Forcing Save of Sub Form

pdlecesne

New Member
Joined
Feb 10, 2016
Messages
14
I have created manual navigation buttons for a Maintenance form that has Contract Data in the Main Form with Participant Data in a subform that is linked to the Main form by a unique identifier. I want the user to be stopped if they have changed any record from automatically saving. I have attached the following to the on click event:



Private Sub btngonextmaint_Click()
If Me.Dirty = True Then
MsgBox "Must Save Changes before changing Records"
ElseIf Me!frmProviderMaint.Form.Dirty = True Then
MsgBox "Must Save Changes before changing Records"
Exit Sub
Else: On Error GoTo ErrorHandler
DoCmd.GoToRecord , , acNext
Exit Sub
End If
ErrorHandler:
MsgBox "You have reached the Last Record"


End Sub

This seems to work to stop advancing on the main form but is not working on the subform. What am I missing?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Subform is a different form so you need a different test for it - more or less the same but with some small changes in what objects you are referring to:

If Me.MySubformControlName.Form.Dirty = True Then
'//Subform is dirty ....
 
Upvote 0
Thank you for your response. My subform's name is frmProviderMaint I changed the '!' to a '.' but it is still not working I can breeze right on to the next record.
 
Upvote 0
It's possible that the subform is saved well before the main form is saved. You might need to put that code in the subform.
 
Upvote 0
My apologies but I only work on this during the week while at work. I have now added the code to the following areas of the subform, named "FrmProviderMaint" : Lost Focus; On Close; On Deactivate; On Selection Change; On Data Change; On Data Set Change and still no luck. This is quite frustrating :(
 
Upvote 0
I am posting because I have figured out the answer and in case someone else had a similar problem. The following had to be placed on the Before update function of the subform.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True Then
If MsgBox("Must Save Changes before changing Records, Save Changes?", vbYesNo) <> vbYes Then
Cancel = True
Me.Undo
Me.AllowEdits = False
Else: DoCmd.Save
Me.AllowEdits = False
End If
Me.AllowEdits = False
End If
End Sub

I then had to add a few things to the code of my navigation buttons to ensure that the record remains uneditable until the edit button is selected.

Private Sub btngonextmaint_Click()
If Me.Dirty = True Then
If MsgBox("Must Save Changes before changing Records, Save Changes?", vbYesNo) <> vbYes Then
Cancel = True
Me.Undo
Me.AllowEdits = False
Exit Sub
Else
DoCmd.Save
Me.AllowEdits = False
End If
Else: On Error GoTo ErrorHandler
Me.AllowEdits = False
DoCmd.GoToRecord , , acNext
Me.AllowEdits = False
Exit Sub
End If
ErrorHandler:
MsgBox "You have reached the Last Record"
Me.AllowEdits = False
End Sub

I do hope this helps someone.
 
Upvote 0
If you click off of a subform onto a main form, the subform data is saved automatically. At least, this is the behaviour with 2007 version and if the subform is a datasheet. Try it. Put a button on the form whose click event tests if the subform is dirty. You will need to know the correct syntax for referencing a subform.
Or, if it's a datasheet, while editing a subform record you will see the edit icon in the far left. When you leave the subform, it's gone. Either way, if you examine the table field that's being edited, you'll see that the data is changed as soon as the focus shifts to the main form. So I'm saying that in these circumstances, checking if a subform is dirty is pointless.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top