Updating a set of child records in a sub-form from a current parent record in the parent form

[WCStarks]

I have a current parent record in a form and a sub-form with child records.

I need to be able to update certain fields in the child record from corresponding fields in the active/current parent record. This may sound like redundancy, but this update will only occur in cases where the related fields should contain the same data. This is not always the case, however.

For example:

Case 1: An Event record for a meeting attendance, the child records would normally have the same beg_date, end_date and hours as in the Event record.
However,
Case 2: For an Event record for a call-out participation, the corresponding child records would not necessarily have the same data in related fields of the parent event record, as the call-out may last over many shifts and days. In this case these fields would be unique to each child record to record each individual's shifts during the event.
So,
I need to be able to execute the update for only the first case.

In case 1, at least the beg_date needs to be in each child record, as it is needed for sorting the child records. Since all the fields need to be present in Case 2, they will, of necessity, need to be in all child records.

[kd2017]

You would simply call an update query to update the child records. To give you an idea here is some GENERIC code that might be executed on a button click from the parent form. The red bold text are placeholders to be replaced by your actual table, field, and control names.

Private Sub cmdUpdateChildren_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qry As String
    
    'build the update query
    qry = "UPDATE [child_table] SET [child_table].[beg_date]=#" & Me.beg_date & "#" & _
         " , [child_table].[end_date]=#" & Me.end_date & "#" & _
         " , [child_table].[hours]=" & Me.hours & _
         " WHERE [child_table].[parent_id]=" & Me.parent_id
    
    'execute the query
    Set db = CurrentDb
    db.Execute qry, dbFailOnError

    'refresh the subform
    Me.subform_control.Requery

ExitHandler:
    'cleanup
    Set db = Nothing
    
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

[WCStarks]

Modified below.

[WCStarks]

Thank you. This looks helpful. I have several questions for clarification.
1) How does this code restrict the process to just the current [Events] record of the parent form and not all [Events] records? I only want to update the child records for the current [Events] record in the parent form.
2) How does the update query, "qry", actually get used in the Execute command?
3) I am unfamiliar with the syntax of the "#" in the query.

[kd2017]

Thank you. This looks helpful. I have several questions for clarification.
1) How does this code restrict the process to just the current [Events] record of the parent form and not all [Events] records? I only want to update the child records for the current [Events] record in the parent form.

Through the WHERE clause.

" WHERE [child_table].[parent_id]=" & Me.parent_id

What this says is update the child_table where the child_table's parent_id is the same as Me.parent_id, again these are generic placeholder names. "Me" referes to the form, ".parent_id" refers to the name of a control on the form that contains the identifier to look for. "child_table.parent_id" is the name of the related field in the child table.
https://www.w3schools.com/sql/sql_update.asp
http://access.mvps.org/access/forms/frm0031.htm

2) How does the update query, "qry", actually get used in the Execute command?

Sorry, I had at typo in the code I posted, i'll go back and edit it. It should look like this

db.Execute qry, dbFailOnError

https://docs.microsoft.com/en-us/off...ute-method-dao

3) I am unfamiliar with the syntax of the "#" in the query.

Dates in access are actually stored as decimals, or doubles. When you are working with date datatypes in access sql statements they need to be surrounded with hashtags so access knows to translate human readible dates like #4/14/2020 9:38 AM# to a computer readible date which in this example actually equals 43935.4013888889. If you just put in the day with no date deliminator like this 4/14/2020 then access would see a mathematical operation of 4 divided by 14 divided by 2020 = 0.00014144, no good.

https://codekabinett.com/rdumps.php?...ype-vba-access
https://www.techrepublic.com/article/10-tips-for-working-with-dates-in-microsoft-access/




Search youtube videos for "Access Update Queries" and you'll find a bunch of tutorials on how to build update queries in the query designer, then if you have a look at the sql code behind the query you've built you'll see a query like I posted above.

[WCStarks]

Thank you for your clarifications.

I guess being in the context of the parent form, the update query is automatically restricted to the current parent record in the Parent form.

I had earlier created an update query in design view, but I couldn't figure out how to restrict it to process for just the current Parent record, so I discarded it.

May I trouble you to show me how this update query would look in a sample Design View context? That would be very instructional for me.

[kd2017]

I guess being in the context of the parent form, the update query is automatically restricted to the current parent record in the Parent form.

It's in the WHERE clause, me.parent_id refers to the parent_id field/object of the CURRENT active record in the form from which it was executed. "Me" is a shortcut that refers to the form where the code was called, in this case a button on the parent form.

See attached for example database, I've included TWO different methods for running this update query. The first method is the one I described earlier. The second method uses a saved query built in the query designer that references the current form objects as you've requested. Let me know if this helps and hopefully doesn't confuse things further.

[WCStarks]

I modified your code as follows. When I run it, it errors out on the db.execute statement with this error: 3061, Too few parameters. Expecting 4. Is this an issue with the Code, or is it something in my infrastructure causing the error, when it tries to update. My system is based on Navigation Control Tabs. In hind sight, I wish it weren't.

Private Sub cmdUpDateChildren_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qry As String
    
    'build the update query
    qry = "UPDATE [Assignments] SET [Assignments].[beg_date]=#" & Me.Beg_Date & "#" & _
         " AND [Assignments].[end_date]=#" & Me.End_Date & "#" & _
         " AND [Assignments].[hours]=" & Me.Hours & _
         " WHERE [Assignments].[Event_id]=" & Me.ID
    
    'execute the query
    Set db = CurrentDb
    db.Execute qry, dbFailOnError

    'refresh the subform
    Me.[Activities Subform].Requery

ExitHandler:
    'cleanup
    Set db = Nothing
    
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

[kd2017]

Can you attach your database for review? Also, see my previous post for an example database that does work.

Also see https://stackoverflow.com/questions/...-1-access-2007

[WCStarks]

Here is a sanitized copy of the database. The surname field contains a string text of the person's Member ID. Select the Event Navigation Tab to get to the form of interest. The update button is to the right in the parent form header. Oops. I had sent the wrong backend db. This should be correct.
SCATeam.zip

[kd2017]

It looks like we both made some mistakes here, but we were close! Mine first: my example sql should have used a comma instead of the AND keyword for setting the different field values. I'll my edit my response above.

In your code you were are referring to the [Assignments] table but that doesn't appear to exist, the subform looks to be based off a query based off of the [Activities] table, also you had [Event_ID] which should have been [Event ID], finally you had the incorrect name for the subform for the requery. This updated code appears to work:

Private Sub cmdUpDateChildren_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qry As String
    
    'build the update query
    qry = "UPDATE [Activities] SET [Activities].[Beg_Date]=#" & Me.Beg_Date & "#" & _
         " , [Activities].[End_Date]=#" & Me.End_Date & "#" & _
         " , [Activities].[Hours]=" & Me.Hours & _
         " WHERE [Activities].[Event ID]=" & Me.ID
         
    'Debug.Print qry
    
    'execute the query
    Set db = CurrentDb
    db.Execute qry, dbFailOnError

    'refresh the subform
    Me.[Activities Ev].Requery

ExitHandler:
    'cleanup
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

For the record, it's best practice not to use spaces in any field names, object names, any names. Also, if you don't use spaces then your code will no longer require square brackets around the names.

[WCStarks]

Thank you. Yes, I see the previously missing comas. I didn't have my second monitor going, and I couldn't see the underlying data elements, but thought I remembered them. I guess not. If I go back and fix the names with spaces, does Access carry over those changes into the associated SQLs? I seem to remember Access doing some auto updates when I changed a name. I am not sure of the contexts of those auto-changes, though.

Anyway, you made it work!!

Not knowing how to do this update, I had created a Before Insert event in the [Activities Ev] sub-form, so that each time I added a new person, it fetched the data from the parent record. The only problem with this is that when entering those attending a meeting, I wouldn't know the actual time the meeting adjourned, so I would estimate the closing time and then often have to go back later and fix the end time and hours to reflect the actual end time and hours. If you look at the sub-form, you will see a checkbox control which is now set to invisible. I would set it True for meetings and False for call-outs. If True, it would auto-fill those fields as I added the new records. Primarily for the case of call-outs, I have a double-click event associated with the parent and child hours to automatically calculate the hours from the related date/times fields.

This query will be a good model for me to use when I need to do something similar in the future. Thanks again.

[kd2017]

... If I go back and fix the names with spaces, does Access carry over those changes into the associated SQLs?...

It will in some places and it wont in others. It should auto-correct references in saved queries but it wont adjust the sql in your vba code like the code we just created. I can't remember off the top of my head if it will auto-correct form object control sources or not, I'm pretty sure it wont auto-correct control sources if they're part of an expression.

[WCStarks]

Hello again kd2017,

I needed to modify your child update routine to add two dialog features.
1) The Update of the Assignments form should only be done on certain events such as meetings where most participants use the same date and hours values as the Event. It would not be appropriate for Call-outs, which make assignments in shifts. So I needed to force a confirmation before proceeding.
2) The update procedure errors out if one of the three Event date and hours fields are empty. So I needed to notify of the need to complete the data.

I initially, used the MsgBox control, but since it centers the dialog box in the center of the monitor screen, I decided to create custom dialog forms for the two needed dialogs. Everything went well this morning. Both dialogs were working just fine. Then I closed the db and a few hours later, reopened it and tried it out again. But, now the dialog box 1) just flashes briefly on the screen and goes away. I have no idea what happened between closing and reopening the db. I tried recreating the dialog form, but it made no difference. I even tried copying the code to a backup db and ran it from there, but it did the same thing.

The module of interest is the Events Navigaton tab and the Update Assignments button in the Event form.

You can test dialog 2) in an existing Event by deleting the value in the Event Hours field and clicking on the button. It works fine.

To test dialog 1) double click on the Hours field to auto-calculate and fill the Hours again. Then click on the update button and watch the form flash on for less than a second.
The DoCmd.OpenForm in red is where the issue is. Debug mode doesn't show what is happening, so I don't know how one can even figure out what is going on.
Maybe you can. If you click on the Dialog_UD_Assign form directly, you will see it opens OK. If you select to continue, it sets form property visible to False. Cancel just closes the form. I have attached a sanitized db to work with.
SCATeam.zip

Private Sub cmdUpDateChildren_Click()
    On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qry As String
    Dim dlForm As String
    
    ' Check to see if both date fields and the Hours in the Event have data
    ' The Update process will error out if any one field is empty
    If IsNull(Me.[Beg_date]) Or IsNull(Me.[End_date]) Or IsNull(Me.[Hours]) Then
        dlForm = "Dialog_Fix_Dates"
        DoCmd.OpenForm dlForm 'Form closes itself after OK is pressed
    Else
        'Get Confirmation
        dlForm = "Dialog_UD_Assign" 'Binary dialog
        DoCmd.OpenForm dlForm  'The form currently will not stay open, but flashes and closes
        If CurrentProject.AllForms(dlForm).IsLoaded Then
            'If the form is still open (invisible), OK was selected
            'If the form is not open, Cancel was selected
            DoCmd.Close acForm, dlForm
            
            'build the update query
            qry = "UPDATE [Activities] SET [Activities].[Beg_Date]=#" & Me.Beg_date & "#" & _
            " , [Activities].[End_Date]=#" & Me.End_date & "#" & _
            " , [Activities].[Hours]=" & Me.Hours & _
            " WHERE [Activities].[Event ID]=" & Me.ID
        
            'execute the query
            Set db = CurrentDb
            db.Execute qry, dbFailOnError

            'refresh the subform
            Me.[Activities Ev].Requery
        End If
    End If

ExitHandler:
    'cleanup
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

[WCStarks]

I just realized that the older sanitized Activities table is lacking a new field that the [Activities ev subform] needs. I wasn't able to sanitize the back-end and don't have time to modify the sanitize query. I am on a search and recovery call-out with the Sheriff's Office. You can fix the Activities table by adding a numeric field named "Travel" configured like the "Hours" field. Sorry for the trouble.

In the comment on the DoCmd.OpenForm dlForm, I said the form closes, but that is not the case. It goes invisible, because it is still open for the following If statement.