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
Code:
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