Solved HELP WITH VBA ON SUBFORM (1 Viewer)

[esturgesjr]

Greetings, Access gurus! Hoping you can help me sort out what may be a simple issue (and maybe not).

I run a database for my volunteer fire department which is cloud-based. I also run an Access database locally to help me keep track of training that we can't seem to get our vendor to run in their environment. Therefore, I import raw data and run my own queries/reports to track training and certification. Last year we started a Junior program to get kids 14-18 interested in firefighting. OK, enough background. On to my issue.

I have a form (frmJUNIOR_UPDATE) with a subform (JR_TrClassesT) linked by Master (TrnDate, TrnType) to Child (TrnDate, TrnType) ... one to many. I have VBA code in the AfterUpdate of the TrnType field on the main form to populate the JR_ID on the subform, which then has a DLookup to fill the Name field (12 records in total ... or however many Juniors are in our Personnel table).

Here's the VBA:

Private Sub TrnType_AfterUpdate()
    Dim strSQL As String, frm As Form, subfrm As Form
    strSQL = "SELECT Personnel.AFD_ID AS JR_ID FROM Personnel WHERE (((Personnel.Rank) = 'Junior Firefighter') And ((Personnel.Status) = 'Active')) ORDER BY Personnel.[Last Name], Personnel.[First Name];"
    
    Set frm = Me
    Set subfrm = frm.[JR_TrClassesT subform].Form
    
    subfrm.RecordSource = strSQL
    
    subfrm.Requery
    
    Set subfrm = Nothing
    Set frm = Nothing
End Sub

Everything seems to go smoothly except for the fact that after the code runs (see screenshot attached), the checkboxes are not updateable and I can't figure out why. I tried setting a default value of "0", but that didn't work either.

Hoping someone can pinpoint bad code, bad form or whatever.

Many thanks in advance!

 

[Gasman]

Update one of the controls BEFORE running that code.
Message seems self explanatory? Show the field TrnCnt in the table.

 

[RonPaii]

Greetings, Access gurus! Hoping you can help me sort out what may be a simple issue (and maybe not).

I run a database for my volunteer fire department which is cloud-based. I also run an Access database locally to help me keep track of training that we can't seem to get our vendor to run in their environment. Therefore, I import raw data and run my own queries/reports to track training and certification. Last year we started a Junior program to get kids 14-18 interested in firefighting. OK, enough background. On to my issue.

I have a form (frmJUNIOR_UPDATE) with a subform (JR_TrClassesT) linked by Master (TrnDate, TrnType) to Child (TrnDate, TrnType) ... one to many. I have VBA code in the AfterUpdate of the TrnType field on the main form to populate the JR_ID on the subform, which then has a DLookup to fill the Name field (12 records in total ... or however many Juniors are in our Personnel table).

Here's the VBA:

Private Sub TrnType_AfterUpdate()
    Dim strSQL As String, frm As Form, subfrm As Form
    strSQL = "SELECT Personnel.AFD_ID AS JR_ID FROM Personnel WHERE (((Personnel.Rank) = 'Junior Firefighter') And ((Personnel.Status) = 'Active')) ORDER BY Personnel.[Last Name], Personnel.[First Name];"
   
    Set frm = Me
    Set subfrm = frm.[JR_TrClassesT subform].Form
   
    subfrm.RecordSource = strSQL
   
    subfrm.Requery
   
    Set subfrm = Nothing
    Set frm = Nothing
End Sub

Everything seems to go smoothly except for the fact that after the code runs (see screenshot attached), the checkboxes are not updateable and I can't figure out why. I tried setting a default value of "0", but that didn't work either.

Hoping someone can pinpoint bad code, bad form or whatever.

Many thanks in advance!

The query in strSQL only returns 1 column, the screen shot shows 3. Where are those other columns coming from? Also why are you setting the source this way, strSQL is not dynamic, you should be able to setup the sub-form source in the sub-form, using the links to filter it. After update could be as simple as frm.[JR_TrClassesT Subform].Requery.

 

[esturgesjr]

Thanks for the replies. This is a form that I was filling manually. I manually enter the ID in column 1 from a sign-in sheet supplied by the Training Coordinator, I have a DLookup in column 2 that pulls Last Name and First Initial from the Personnel table (to make sure each check box in column 3 is checked if the Junior for that record attended training, or unchecked if they didn't. When I click New Record button and the blank form comes up, everything works fine with manual entry. The problem developed when I tried to automate everything. Should I edit the SQL to include the LastName, Initial and TrnCnt with a value of 0? Then I could edit the TrnCnt according to the sign-in sheet.

 

[Gasman]

No, but do include TrnCnt if that control is going to be bound.
The dlookup should still get populated.

 

[Ken Sheridan]

The SQL statement which you are assigning to the subform's RecordSource property does not include the TrnCnt column to which the check box is bound, hence the error. I'm puzzled by your methodology, however. In a situation like this I would normally expect the subform to be bound to a table or query, and an INSERT INTO statement to be executed to insert rows into that table. The following example is the code for a button in the header of a CourseAttendances subform which builds and executes an SQL statement which inserts all students who are registered for the course currently selected in the parent form into the subform's table for the session currently selected in another subform in the parent form:

Private Sub cmdAllStudents_Click()

    Dim strSQL As String
    
    strSQL = "INSERT INTO CourseAttendances" & _
        "(StudentID,CourseID,CourseDate) " & _
        "SELECT StudentID, " & Parent.[CourseID] & ", #" & _
        Format(Parent.[sfcCourseSessions].[Form].[CourseDate], "yyyy-mm-dd hh:nn:ss") & _
        "# FROM CourseRegistrations WHERE CourseID = " & Parent.[CourseID]
    
    CurrentDb.Execute strSQL
    
    Me.Requery

End Sub

 

[esturgesjr]

Thank you, Ken. The subform is a datasheet view of a table (JR_TrClassesT) so I guess the answer to whether it's bound or not is "yes". The JR_ID would come from Personnel table (AFD_ID) where the Rank = 'Junior Firefighter" and Status = 'Active' and Order by LastName, FirstName; the TrnDate and TrnType from the two fields on the parent form and TrnCnt is a field in JR_TrClassesT, which should be unchecked (I would then check the appropriate records from the sign-in sheet that is sent to me. I have VERY little SQL expertise; I build most of my queries in design view. I do think INSERT INTO is the way to go, as you suggested; I just need help in putting the language together.

 

[arnelgp]

modify your code like this:

Private Sub TrnType_AfterUpdate()
    Dim strSQL As String, frm As Form, subfrm As Form
    
    'insert records to JR_TrClassesT table
    strSQL = "INSERT INTO JR_TrClassesT (JR_ID, TrnCnt) " & _
            "SELECT Personnel.AFD_ID, 0 FROM Personnel " & _
            "WHERE (((Personnel.Rank) = 'Junior Firefighter') And ((Personnel.Status) = 'Active')) " & _
            "AND AFD_ID NOT IN (SELECT JR_ID FROM JR_TrClassesT) ORDER BY Personnel.[Last Name], Personnel.[First Name];"
    
    CurrentDb.Execute strSQL
    
    'join with Personnel table
    strSQL = "SELECT JR_ID, TrnCnt FROM JR_TrClassesT INNER JOIN " & _
            "((SELECT Personnel.AFD_ID FROM Personnel " & _
            "WHERE (((Personnel.Rank) = 'Junior Firefighter') And ((Personnel.Status) = 'Active')) " & _
            "ORDER BY Personnel.[Last Name], Personnel.[First Name]) AS T) ON JR_TrClassesT.JR_ID = T.AFD_ID;"
    
    Set frm = Me
    Set subfrm = frm.[JR_TrClassesT subform].Form
    
    subfrm.RecordSource = strSQL
   
    subfrm.Requery
    
    Set subfrm = Nothing
    Set frm = Nothing
End Sub

 

[Ken Sheridan]

I do think INSERT INTO is the way to go, as you suggested; I just need help in putting the language together.

To illustrate how to do it I've attached a simple little database, whose model is:

The way it works is that you first select a session from the drop down list of the combo box at the top of the form, or you can type in the date for a new session not yet in the list. This causes the form's NotInList event procedure to execute and insert a new row into the Sessions table.

Having selected or entered a session in the combo box you then click the button. This inserts new rows, one for each Junior Firefighter in the Personnel table, into the SessionAttendances table and then requeries the subform to show the new rows. The button's code is:

Private Sub cmdInsertTrainees_Click()

    Const MESSAGE_TEXT = "A session must be selected."
    Dim strSQL As String
   
    If Not IsNull(Me.cboSessions) Then
        strSQL = "INSERT INTO SessionAttendances(SessionID,PersonnelID) " & _
            "SELECT " & Me.cboSessions & ",PersonnelID " & _
            "FROM Personnel WHERE Rank = ""Junior FireFighter"""
           
        CurrentDb.Execute strSQL
        Me.sfcAttendances.Requery
    Else
        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
    End If
   
End Sub

Note that in the event procedure's code the CurrentDb.Execute strSQL line does not include the usual dbFailOnError option. This is to allow more trainees to be added to an existing session by selecting the session and clicking the button again. If new trainees have been added to the Personnel table since the form was originally used to insert trainees the key violation errors which occur when the INSERT INTO statement tries to insert them again will be ignored, so only the new trainees will be added.

Note also that the SQL statement does not include the Attended column. As a Boolean (Yes/No) data type this column's values can only be TRUE or FALSE, and will default to the latter. When the rows appear in the subform, therefore, all you need to do is tick the checkboxes for those trainees who attended the session.

Hopefully this will give you a model to follow when you amend your database. If you have any problems get back to us.

 

[esturgesjr]

modify your code like this:

Private Sub TrnType_AfterUpdate()
    Dim strSQL As String, frm As Form, subfrm As Form
 
    'insert records to JR_TrClassesT table
    strSQL = "INSERT INTO JR_TrClassesT (JR_ID, TrnCnt) " & _
            "SELECT Personnel.AFD_ID, 0 FROM Personnel " & _
            "WHERE (((Personnel.Rank) = 'Junior Firefighter') And ((Personnel.Status) = 'Active')) " & _
            "AND AFD_ID NOT IN (SELECT JR_ID FROM JR_TrClassesT) ORDER BY Personnel.[Last Name], Personnel.[First Name];"
 
    CurrentDb.Execute strSQL
 
    'join with Personnel table
    strSQL = "SELECT JR_ID, TrnCnt FROM JR_TrClassesT INNER JOIN " & _
            "((SELECT Personnel.AFD_ID FROM Personnel " & _
            "WHERE (((Personnel.Rank) = 'Junior Firefighter') And ((Personnel.Status) = 'Active')) " & _
            "ORDER BY Personnel.[Last Name], Personnel.[First Name]) AS T) ON JR_TrClassesT.JR_ID = T.AFD_ID;"
 
    Set frm = Me
    Set subfrm = frm.[JR_TrClassesT subform].Form
 
    subfrm.RecordSource = strSQL
 
    subfrm.Requery
 
    Set subfrm = Nothing
    Set frm = Nothing
End Sub

Thanks, arnelgp! I appreciate the work you put into this; however, your code returned 609 records instead of the 12 I'm looking for (it returned all records from the table JR_TrClassesT except for 28 records attached to a Junior who resigned earlier this year). I only want records from Personnel who have Rank of "Junior Firefighter" and Status of "Active".

 

[esturgesjr]

To illustrate how to do it I've attached a simple little database, whose model is:

View attachment 122522

The way it works is that you first select a session from the drop down list of the combo box at the top of the form, or you can type in the date for a new session not yet in the list. This causes the form's NotInList event procedure to execute and insert a new row into the Sessions table.

Having selected or entered a session in the combo box you then click the button. This inserts new rows, one for each Junior Firefighter in the Personnel table, into the SessionAttendances table and then requeries the subform to show the new rows. The button's code is:

Private Sub cmdInsertTrainees_Click()

    Const MESSAGE_TEXT = "A session must be selected."
    Dim strSQL As String
  
    If Not IsNull(Me.cboSessions) Then
        strSQL = "INSERT INTO SessionAttendances(SessionID,PersonnelID) " & _
            "SELECT " & Me.cboSessions & ",PersonnelID " & _
            "FROM Personnel WHERE Rank = ""Junior FireFighter"""
          
        CurrentDb.Execute strSQL
        Me.sfcAttendances.Requery
    Else
        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
    End If
  
End Sub

Note that in the event procedure's code the CurrentDb.Execute strSQL line does not include the usual dbFailOnError option. This is to allow more trainees to be added to an existing session by selecting the session and clicking the button again. If new trainees have been added to the Personnel table since the form was originally used to insert trainees the key violation errors which occur when the INSERT INTO statement tries to insert them again will be ignored, so only the new trainees will be added.

Note also that the SQL statement does not include the Attended column. As a Boolean (Yes/No) data type this column's values can only be TRUE or FALSE, and will default to the latter. When the rows appear in the subform, therefore, all you need to do is tick the checkboxes for those trainees who attended the session.

Hopefully this will give you a model to follow when you amend your database. If you have any problems get back to us.

Thanks, Ken! I think this will "fit the bill". I'll dive in after I've had my morning coffee (yeah, it's 9:41 am my time, but I'm retired ... ha ha). I'll report back when I have an update.

 

[esturgesjr]

Thanks, Ken! I think this will "fit the bill". I'll dive in after I've had my morning coffee (yeah, it's 9:41 am my time, but I'm retired ... ha ha). I'll report back when I have an update.

 

[esturgesjr]

Ken, moving right along, then I stumbled when I modified code to include my needs (see screenshot)! Compile error; expected end of statement.

 

[MajP]

A couple things to make it easier.
1.Debug

dim strSql as string

strSql = ....
debug.print StrSql

look at the debug and ensure it makes sense.

2. When working with string literals in sql use single quotes. Much easier to read and no doubling up

...Where Rank = 'Junior Firefighter' and Status = 'Active'"

3. To insert the literal date you will have to use correct delimiter. Untested but I think it will be

... JR_ID, #" & format(me.TrnDate,"mm/dd/yyyy") & "#, "

giving you something like

... JR_ID,#2/5/2025#,

4. If trnType is a text field then that needs delimiters too. Use single quotes

... JR_ID, #" & format(me.TrnDate,"mm/dd/yyyy") & "#,'" & me.TrnType & "'"

 

[Ken Sheridan]

The second # sign delimiter around the date is missing. It should be:

JR_ID, #" & format(me.TrnDate,"mm/dd/yyyy") & "#,'" & me.TrnType & "'"

PS: For consistency with the rest of the string expression you might want to use a pair of contiguous double quote characters, "", to represent the literal quote characters rather than the single quote character,', around the TrnType value, or vice versa. I normally use contiguous pairs of double quotes, being conscious of potential problems with string expressions like my own name in it's original non-anglicized form, Cináed O'Siridean.

 

[Gasman]

I always put the space on the next continuation line, not the previous line.
Easier to see, without having to scroll all the way to the right.
I also make great use of debug.print before trying to use any concatenated sql strings.

 

[MajP]

also if you do a lot of this I recommend a function like the CSql.

Then you code is

... JR_ID, "& Csql (me.TrnDate) & ", " &  csql(me.TrnType) &.

It will figure out if it is a string or text or numeric.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[arnelgp]

Thanks, arnelgp! I appreciate the work you put into this; however, your code returned 609 records instead of the 12 I'm looking for

then you already have Duplicate JR_ID on JR_TrClassesT table.
what the code is doing is adding (if it does not already exists on JR_TrClassesT) the 12 records you are looking for.
next it uses an INNER JOIN (meaning only those 12 JR_ID should be included).
but you said more than 12 are being retrieved, so there must be duplicate JR_IDs on your table.

 

[MarkK]

Two observations I have...
• use a parameter query. It completely absolves you of having to delimit or format any parameters.
• more importantly, decouple the UI event handling from the action it triggers.
Consider...

Private Const SQL_INSERT = _
    "INSERT INTO CourseAttendances " & _
        "( StudentID, CourseID, CourseDate ) " & _
    "SELECT StudentID, prmCourseID, prmCourseDate " & _
    "FROM CourseRegistrations " & _
    "WHERE CourseID = prmCourseID"

Private Sub cmdAllStudents_Click()
    If InsertRow(Parent.CourseID, Parent.[sfcCourseSessions].[Form].[CourseDate]) Then Me.Requery
End Sub

Public Function InsertRow(CourseID As Long, CourseDate As Date) As Integer
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters("prmCourseID") = CourseID
        .Parameters("prmCourseDate") = CourseDate
        .Execute dbFailOnError
        InsertRow = .RecordsAffected
        .Close
    End With
End Function

So the parameters are expressed in the code, but note that InsertRow() is now Public.
My observation is that its super valuable to start to distinguish...
1) code that handles UI events, and
2) code that performs critical data domain functions
...and if you bury your critical functionality inside your UI handlers, you are, as a programmer, hiding the pearls in the dirt. Handling the click event is easy. Managing the data is hard.
• So make the hard parts independent, well named, and clear about what they depend on to function correctly.
• And then, call them from somewhere in your UI.
As a for instance, the button click could just as easily be on the parent, like...

Private Sub cmdAllStudents_Click()
    If Me.sfm.Form.InsertRow(Me.CourseID, Me.[sfcCourseSessions].[Form].[CourseDate]) Then Me.sfm.Requery
End Sub

How you trigger a method, and what that method does, are separate concerns.

 

[esturgesjr]

then you already have Duplicate JR_ID on JR_TrClassesT table.
what the code is doing is adding (if it does not already exists on JR_TrClassesT) the 12 records you are looking for.
next it uses an INNER JOIN (meaning only those 12 JR_ID should be included).
but you said more than 12 are being retrieved, so there must be duplicate JR_IDs on your table.

Hmmm, either I didn't explain the situation properly or you misunderstood the post. 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). Sorry if I wasn't clear enough.