Solved 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.

 

[spaLOGICng]

When you have the Master and Child Link Properties referencing the correct fields, the columns on the subform will be automatically populated with the Parent/Master Values.

Just create a query that represents the record source of the subform correctly and add the name of the query to the RecordSource Propeprty of the Subform. Make sure your Primary Key from your main table is in the record source so that it is updateable. You should have only one main table in the record source. it is okay for supporting tables to use their columns as needed. I typically avoid this by using combo boxes for the updateable FK fields. I use queries so that I can control the default sorting without needing any VBA. I never use SELECT statements as a RecordSource. Always a saved Query. This permits easier management of recordsources even while in runtime and reduced redundancy.

You do not need all that VBA. I only use VBA as a last resort.

 

[esturgesjr]

Bingo!! Thank you ALL for your input; many lessons learned (as always) from the gurus, some of which I have leaned on from UA (you know who you are). Here's the final code:

Private Sub cboTrnType_AfterUpdate()

    Const MESSAGE_TEXT = "A training date must be selected."
    Dim strSQL As String
    
    If Not IsNull(Me.TrnDate) Then
        strSQL = "INSERT INTO JR_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"" ORDER BY [Last Name], [First Name];"
                    
        CurrentDb.Execute strSQL
        Me.JR_TrClassesT_subform.Requery
    Else
        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
    End If
    
End Sub

Merry Christmas! Happy Holidays! Stay Safe!

 

[tvanstiphout]

Better: CurrentDb.Execute strSQL, dbFailOnError

Personally, I don't like to worry about string concatenation and escape characters for dates and strings, and I use the StringFormatSQL function from Northwind Developer Edition template.

Also, single quotes are easier to read, in situations like:
AND Status = 'Active' ORDER BY

 

[Ken Sheridan]

Better: CurrentDb.Execute strSQL, dbFailOnError

Tom, I deliberately omitted the dbFailOnError constant in my earlier reply to the OP. This allows additional trainees to be added to a training session by executing the procedure again for that session. The errors which are raised on those rows which attempt to insert a trainee who has already been inserted are thus ignored, and only any new trainees are added. If the dbFailOnError option is included, then the SQL statement should be extended by adding a WHERE clause to exclude those rows where the referencing table already includes a trainee/session which matches the trainee in the referenced table/the current session.

 

[DHookom]

Tom, I deliberately omitted the dbFailOnError constant in my earlier reply to the OP. This allows additional trainees to be added to a training session by executing the procedure again for that session. The errors which are raised on those rows which attempt to insert a trainee who has already been inserted are thus ignored, and only any new trainees are added. If the dbFailOnError option is included, then the SQL statement should be extended by adding a WHERE clause to exclude those rows where the referencing table already includes a trainee/session which matches the trainee in the referenced table/the current session.

Ken, in all my decades of Executing SQL statements, either I wasn't aware of that "feature" or forgot it decades ago.