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].Form.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.

 

[Ken Sheridan]

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

It's much the same as when you run the query in the user interface, and it warns you that a certain number of rows won't be inserted for a stated reason, in this case a key violation. In the example I posted for the OP I tried to keep things simple, but I have to confess that it's not great code, and contradicts 'Einstein's razor' that things should be done as simply as possible, but no more so. If another error were to occur other than the key violation on a row which was otherwise valid, that row would be ignored also. It would be better to include the dbFailOnError option, and restrict the rows inserted to only new trainees as I described in my last post.

Incidently, Einstein never couched the 'razor' in the simple terms in which it is usually cited. What be actually said was:

'It can scarcely be denied that the supreme goal of all theory is to make the irreducible basic elements as simple and as few as possible without having to surrender the adequate representation of a single datum of experience.'

Albert Einstein "On the Method of Theoretical Physics" The Herbert Spencer Lecture, delivered at Oxford (10 June 1933)

 

[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].Form.Requery
    Else
        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
    End If
  
End Sub

Merry Christmas! Happy Holidays! Stay Safe!

Ugh, a slight problem. When I run the above code (which is running from the AfterUpdate on the Main Form), the subform is NOT refreshed. I know the records have been written to the table as I see the record change in the Navigation bar, but the form stays empty. If I navigate back one record, then forward the form fills. What am I missing?

 

[The_Doc_Man]

If you want to force a sub-form to refresh after you do something in the parent form,

[name-of-sub-form-control].Form.Requery

The name of the sub-form control might accidentally match the name of the form gets loaded to that control, but ideally it will NOT match. Access can get confused - or you can - when that form and control have the same name.