One Message box for Mainform and Subform?

[Clarinet]

Is there any way to have a message box pop up AFTER ALL additions or changes are entered on both a main form AND a subform?

I realize that when you move from a main form to a subform Access automatically saves the current record before changes can be made to the subform. However, to the user both forms are one record that is being added/changed and I would like to have the message box reflect this if at all possible.

Record source for the main form is tblMain
Record source for the subform is a junction table (tblNewJunction) that creates a many to many relationship with tblMain and tblDistinctTableNames.

This simple message box code fires when tabbing out of the main form into the subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
    
    strMsg = "Data has changed."
    strMsg = strMsg & vbCrLf & "Do you wish to save your changes?"
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Changes?") = vbYes Then
        'do nothing
        
    Else
    
    DoCmd.RunCommand acCmdUndo
    
    End If

I could change the message to “Do you wish to save your changes and proceed to add table names to the new record?”, but would like to avoid this “intermediate message box” and just have one yes/no message box pop up when the user is finished entering data on both the main form AND the subform.

Any suggestions would be greatly appreciated.

 

[Remou]

The main form will automatically be saved when the user moves to the subform, so if you want to add a message, it will need to be before this happens, otherwise you cannot undo or cancel. The alternative, as far as I recall, is an unbound form.

 

[PHV]

Anyway, with referential integrity properly set, you can't add records in tblNewJunction for a not yet saved tblMain occurence.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Clarinet]

Thank you Remou and PHV for your feedback.

I am aware of the referential integrity constraints in adding records in the Junction table. However, I was hoping there would be a way to hold the main table data in a buffer until additional new data was added to the subform.

Then a message box at the end would give the user a chance to save or cancel the data entry for the entire record on the main form and subform. Of course the save would have to hit the main table first and then the junction table.

 

[Remou]

There is. Use an unbound form, as I mentioned. The subform will have to be unbound, too.

 

[Clarinet]

Thanks again. An unbound form is new territory for me, but I'll do some research and give it a try!

Just one question, is it possible to populate the fields on an unbound form with code when the form opens? Sorry if this seems like a dumb question, but like I said, this is new territory for me!

 

[Remou]

Yes, it is. Pay a good deal of attention to the naming of your controls, it will make life easier.