HELP WITH VBA ON SUBFORM

[Gasman]

Perhaps the ID is incorrect?

"AND AFD_ID NOT IN (SELECT JR_ID FROM JR_TrClassesT)

Should that JR_ID be AFD_ID ?, or vice versa?

 

[esturgesjr]

Perhaps the ID is incorrect?

"AND AFD_ID NOT IN (SELECT JR_ID FROM JR_TrClassesT)

Should that JR_ID be AFD_ID ?, or vice versa?

The ID (AFD_ID as JR_ID) should be obtained from Personnel, not JR_TrClassesT (see Post #20).

 

[Ken Sheridan]

I am attempting to grab the 12 Juniors from the Personnel table (no duplicates here, 1 record for each member including the 12 Junior members) and create new records for a specific training class in JR_TrClassesT (many records per Junior member; they train one night a week all year round).

Did you try the amendments posted by MajP? I think you just need to change the assignment of the concatenated string expression to the strSQL variable in Post #13 as follows:

        strSQL = "INSERT INTO TrClassesT(JR_ID,TrnDate,TrnType) " & _
            "SELECT AFD_ID, #" & Format(Me.TrnDate,"yyyy-mm-dd") & "#,""" & Me.TrnType & """ " & _
            "FROM Personnel WHERE Rank = ""Junior FireFighter"" AND Status = ""Active"""

This assumes TrnType is of text data type. Otherwise your code looks fine to me and will do what you want.