Access DB - After Insert

Question

Greeting all,

I am attempting to setup a trigger to run when I insert a record into Table A (Agents) that will create the supporting record in Table B (AgentInfo). I think I have a good idea on how to get the record inserted, but I have ran into an issue. Table A has their employees name listed at Lastname, Firstname and Table B is Firstname Lastname.

I already have a module in the DB that will create the record for me BUT I have to remember to run it every time I add a new employee. It looks something like this:

Sub addAgentInfo()
    Dim mydb As DAO.Database, rs As DAO.Recordset, rs2 As DAO.Recordset
    Set mydb = CurrentDb
    Set rs = mydb.OpenRecordset("Select * from Agents where isnull(status) = true")
    Do While rs.EOF = False
        Set rs2 = mydb.OpenRecordset("select * from agentinfo where Agent_Access_ID = " & rs!Agent_Access_ID)
        If rs2.EOF Then
            rs2.AddNew
            rs2!Agent_Access_ID = rs!Agent_Access_ID
            rs2!FirstLast = Split(rs!Agent_Name, ", ")(1) & " " & Split(rs!Agent_Name, ", ")(0)
            rs2!pattern = "5x8"
            rs2!lunchduration = 30
            rs2!starttime = "8:00 AM"
            rs2!endtime = "4:30 PM"
            rs2!scheduleinfo = ""
            rs2!istempshift = False
            rs2!notes = ""
            rs2!loa = False
            rs2!fmla = False
            rs2!PendingLOA = False
            rs2!PendingFMLA = False
            rs2!LOAFMLAStartDate = Null
            rs2!LOAFMLAEndDate = Null
            rs2.Update
        End If
        rs.MoveNext
    Loop
End Sub

Is there a way to do the Split() function in the "Create Record" action in the After Insert trigger OR just run the function I already have made?

Answer 1

I ended up doing this in the After Insert: