save changes to form and subform (1 Viewer)

ReginaF

Registered User.
Local time
Today, 03:09
Joined
Sep 23, 2019
Messages
26
Hello Everybody!

I am back with a new "problem". So I am now working on form with a subform. I added a saveBtn to my main form, and I would like to only save changes made to the form or the subform if I click the saveBtn and then click yes to the appearing messagebox. I have successfully implemented this with only one main form. If just click save (and Me.Dirty = True) then a msgbox will appear and if I click yes it will save changes, if no, then it will discard all changes made. Now when I tried to update my code to do the same if I make changes to the main form or the subform. However no matter what I try it will only ask to save changes if I only edit the main form. If I edit both or only the subform, then when I click on the saveBtn it automatically saves all, without asking if I really want to save changes.
My code it the following:

If Me.Dirty = True Then
If MsgBox(" Mented a változtatásokat mainben?", vbYesNo) = vbYes Then
Me.Dirty = False
Else
Me.Undo
End If
ElseIf Me.Dirty And Me.frm_Sub_Szövet.Form.Dirty = True Then
If MsgBox(" Mented a változtatásokat mindenhol?", vbYesNo) = vbYes Then
Me.Dirty = False
Me.frm_Sub_Szövet.Form.Dirty = False
'with this we force access to save changes
Else
Me.Undo
Me.frm_Sub_Szövet.Form.Undo
End If
ElseIf Me.frm_Sub_Szövet.Form.Dirty = True Then
If MsgBox(" Mented a változtatásokat subban?", vbYesNo) = vbYes Then
Me.frm_Sub_Szövet.Form.Dirty = False
'with this we force access to save changes
Else
Me.frm_Sub_Szövet.Form.Undo
End If
End If

It would be really important for me to only save changes if I press okay and if no is pressed, then all changes made should be discarded. I would really appreciate any kind of help.
Thank you in advance!

Regina
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
Hi Regina. If you really want to control when a record should be saved to the table, then your best bet is to use unbound forms.
 

ReginaF

Registered User.
Local time
Today, 03:09
Joined
Sep 23, 2019
Messages
26
Thank you for your hint. But is there no other way? I am really unfamiliar with unbounf forms and how I can save records with them.
I had success with simple forms (using my code above), I only can not implement it to forms containing subforms :(
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
Thank you for your hint. But is there no other way? I am really unfamiliar with unbounf forms and how I can save records with them.
I had success with simple forms (using my code above), I only can not implement it to forms containing subforms :(
I'm afraid any other way would be just as problematic. The good thing about using bound forms is that Access automatically saves the record for you. So, when the user leaves the subform after dirtying it, Access automatically saves the data. But since you don't want that, then you'll have to stop Access from doing it. And the only reliable way to do that, since you want to consider the data on the main form too, is to use unbound forms.


One other approach is to bind the subform to a temp table. When the user clicks the "Save" button on the main form, you then save or move the data from the temp table to the appropriate child table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:09
Joined
Feb 19, 2002
Messages
42,981
Understanding form level events is the key to controlling when a record is saved. Unbound forms are not necessary.

Your save button should not have any validation code. All it should do is to force the record to save if it is dirty. The KEY event is the form's BeforeUpdate event. That is where you put your question as well as the validation code. If you don't want to save a record because the user said no or because some validation rule failed, you must cancel the event using --

Cancel = Trie

You will need an error trap in the button click event because Access will raise an error there if the save is cancelled in the BeforeUpdate event.

The subform and mainform are handled separately. The mainform record MUST be created first before Access can create the subform record. That's just the way relational databases work. If you want to control this so that the main record is never created if no subform records are created, then you're going to have to go with unbound forms (a royal PITA) or use forms bound to holding tables. When the user is finished creating the reocords, he presses the "save" button which validates the subform records first, then the mainform record and if all are satisfactory, the code copies the main form record and appends it the the permanent table and then does the same thing for the subform records. Then the data is deleted from the working tables. To be perfectly honest, I've only actually needed to create this type of process ONCE is 25+ years of working with Access. The problem is even worse, if you need to allow changes to existing records.

A simpler solution is to use a "complete" flag on the parent record. And only set that programatically after validating both the parent and child records. At least that lets you work with only one set of tables but all your queries except the one for the maintenance form must exclude any parent record where the complete flag is false.

Why is it important to control BOTH the parent and child record saves together? What bad thing happens if one is changed without the other?
 

Users who are viewing this thread

Top Bottom