Main Form Unable to Detect Subform Changes - Issues with Recognizing Subform Dirty Status in Access VBA

Question

Firstly, apologies for my vague question title - I am not sure how best to describe this issue!

In my Access database, I have a Patient Information form (the main form), and within this, there is a subform "Referral Information Subform". On the main form I have a "Save Changes" button and a separate "Exit" button for this form.

If the user updates/amends a record and clicks the "Exit" button without first clicking the "Save Changes" button, I want there to be an error which says "You have unsaved changes, are you sure you want to exit?" The reason for this, is so that a user doesn't change a record without meaning to. They are alerted that they have made a change and have not manually saved the change.

I know Access is designed to automatically save changes to forms, but I just want to avoid any accidental changes being made.

I have this code:

Private Sub Command273_Click()

If Not ChangesSaved() Then 

    Dim response As Integer 

    response = MsgBox("You have unsaved changes. Are you sure you want to exit?", vbYesNo + vbExclamation, "Exit Confirmation") 

    If response = vbYes Then 

        ' Undo changes and exit 

        Me.Undo 

    Else 

        ' User chose "No", do nothing and keep the form open 

        Exit Sub 

    End If 

End If 

' Close the form 

DoCmd.Close acForm, Me.Name 

DoCmd.OpenForm "Home Page" 

End Sub

Function ChangesSaved() As Boolean

' Check if the form is dirty (changes have been made) 

If Me.Dirty Or Me![Referral Information Subform].Form.Dirty Then 

    ' Form is dirty, changes have not been saved 

    ChangesSaved = False 

Else 

    ' Form is not dirty, no changes have been made or changes have been saved 

    ChangesSaved = True 

End If 

End Function

This coding works for the main Patient Information form, but I cannot get it to work for the Referral Information Subform. If I make changes in the Referral Information Subform, and press the exit button without pressing the save button, the changes just automatically save.

Does anyone have any ideas how I can amend my code to ensure that in my Referral Information Subform, if a change is made and the user tries to exit without pressing the save button, the error message appears?

Thanks!

Answer 1

The issue of course is that the one main form, and the one record?

You can easy check if the form is "dirty" and prompt as such.

However, the sub form is more difficult, since there are MANY records, and the me.dirty for the sub form ONLY applies to the current row the user is on. In other words, like all forms, be it the main form, or the sub form? They are all dealing with ONE record at a time.

So, if the user is editing a record in the sub form, and moves on to the next record, then the previous record is saved, and hence the sub form is not going to show dirty anymore.

So, all forms work with "one" record, and one record at a time. As a result, there not really a practical way to prompt the user to save both parent record, and the "many" child records that may well have been updated.

Worse yet, if the user edits 2 or 3 sub form records, then they are all saved, and now you edit the main record, and answer NO to edit changes. Thus, you not saving those edits to the main form, but the child rows will have been saved.

As a result, you can't really have a single save prompt, and NOT save all edits in one shot due to the sub form. So, this so called "design" pattern does not work with Access and bound forms.

Some ideas?

Well, in place of a nag save prompt, you disable all controls, and provide a edit button. That way, accidental changes can't be made. And, you can when the user hits edit (and enable all controls - include sub form), then you can log that fact in a log table. Now, the user might not do any edits AFTER hitting the edit button, but at least users can't edit by accident, and hitting the edit button can log that fact.

Another idea? Well, you adopt a accounting type of "posting" process. so, as a user edits the records, then you have a single field in the "main" form that is a posting flag. User can thus edit records, and thus you build up a posting list of records to "post". For example, here is a classic accounting posting page in which a single check or donation example is distributed to multiple accounts. However, we don't want to save the data UNTILL such time the donation amount matches the amounts for multiple accounts. due to the above sub form issue, we could not test/check/prevent a save of the form + sub form in which the balance of amounts in the sub form MUST match the 1 single donation amount in the parent form.

So, we let the user start a "batch". The user enters donation amounts, and then in sub form enters the distriputed amounts that must total up to the one parent reocrd. However, they are fee to edit, free to add new donations (and sub form distributled amounts). But, at end of day, they then hit a post button, and it checks if the totals in the one parent record matches the total in the child distroubtion amounts. If the totals don't match, then the one records "ok balance matched" flag is not set.

In effect, that one "flag" in the main record MUST be set to true for the information to be valid.

Hence, the Access screen looks like this:

So, in above, the user entered $50. And I did not want to save the form UNLESS the total on the right side matches. But, with the main form + sub form, then I can't control the "whole thing" in one save operation. So, I let the user edit all day long. But, at end of day, the post (close) batch will run some VBA code that checks the main total record (one donation), and then checks the child records. If the totals don't match, then I don't set nor allow the posted date field to be updated. In effect, the result is the SAME as if I had the ability in access to save a master + child set of data in one operation. Since we don't, then adding the concept of a posting batch solves this issue.

As noted, in your case? Just add a edit button to the form, and then on for load, you can set allow edits = false, and the user's can't make accidental changes. so, don't try to build a save prompt, since it can't work for a master/child (subform) setup. But, what you can do is not allow edits when the form loads, and hence accidental changes can't occur until they hit some "enable" button.

So, say like this:

So, the form editing is disabled until you hit the edit button, and it turns green.

So, don't try to prevent a save, but prevent a edit in the first place, and that way you eliminate accidental edits.

Answer 2

Hi all,

Thank you very much for your help with this - I really appreciate it.

Ken, I will give your first suggestion a go, and if I have any difficulties with that then I will use the alternative in your ChangedRecordDemo file. Thank you very much for your time and effort in sharing these resources, they are extremely helpful!!

Thanks again.

Answer 3

As has been explained by George, exiting a subform automatically saves the current record if it has been inserted or edited since moving to the record.  Consequently, to use code in a parent form's module to determine if a subform's record should be 'saved' would require a more circumspect approach.

In the subform's AfterUpdate event procedure you could assign a value of TRUE to one public Boolean variable declared in a standard module (NB: not the form's or subform's class module), and assign the value of the record's primary key to another public variable declared in the same way.  In the parent form's module you could examine the value of the variable, in the Exit button's Click event procedure.  If the value is TRUE then you would abort the event procedure and call the MsgBox function to ask the user whether they wish to keep the last record in the subform or delete it.  On the basis of the function's return value you would either assign a value of FALSE to the Boolean variable if the user elects to keep the subform record, or execute a DELETE statement to delete it, using the primary key value in the other public variable to restrict the DELETE statement to the correct record. 

In the Click event procedure of the Save button you would assign a value of FALSE to the public Boolean variable, and save the parent form's Current record with:

    Me.Dirty = False

An alternative approach would be that illustrated in my ChangedRecordDemo file, which you can find in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes a 'Form to Confirm Changes to Data' option.  This uses a RecordWillChange function to determine whether any actual changes have been made to a record, rather than its having merely been updated, which can be the case even if no actual changes to the data have occurred.  On the basis of the function's return value in the form's BeforeUpdate event procedure the user is then prompted whether to save the record or not.  In the demo the code is in the parent form's module.  In your case it would be in the subform's module.  You could include the same code in the parent form's module to determine if any data in the parent form's current record has changed.  This would remove the need for a Save button in the parent form.

Answer 4

Exiting the Subform causes it to save the current record. That's basic Access bound forms behavior. Simply clicking on your Exit button on the main form saves whatever is in the subform.

You could try to use the On Exit event of the subform control to check if the subform in it is dirty and cancel the exit action before exiting the subform for the main form.

Answer 5

Dear abin23,

Greetings! Thank you for posting in Microsoft Community.

It seems like the issue is with the ChangesSaved() function not recognizing the dirty status of the subform. You may try modifying the function to explicitly check the dirty status of the subform using the Form.Dirty property.

Also, make sure that the subform's Dirty property is set to Yes in the form's properties. This will allow the subform to recognize changes made to its records.

In the meanwhile, we will also keep this thread open. Welcome community members and MVPs who have similar experience to share insights and suggestions here.

Thanks for your time and cooperation. I hope that you are keeping safe and well!

Sincerely,

Tina | Microsoft Community Moderator