Welcome to David W Houston.com

I recently ran into a problem while designing an Access database.  I created a form for data input that had a relevant yet unrelated subform on it.  It was unrelated because the “link master fields/child fields”  properties were empty and it was not necessary to relate these forms for my purposes.

Here’s the problem:  If a user was entering data in the parent form and decided to click on the subform, regardless of the reason, the data in the parent form would save automatically.  This was troublesome because the users that input the data were not capable of deleting the records they created.  I wanted the data input process to be a deliberate one where the record would save only by deliberately clicking the submit button.  The problem seemed quite tricky at first, but I was eventually able to overcome it with some research and a little experimentation.

So, what do you do?  Well, I tried Googling the answer and I saw some good ideas and got some good direction, but nothing seemed to answer my specific needs.  So after a few hours of experimentation with the direction I received from my Google searches, I was able to come up with this…

1.  Create a module variable.

In your form module, create a module variable.  I created a variable of Boolean data type  and called it  “modAllowSave”.  This variable will be in one of two states — True or False.  When this variable is set to True, you can save the data in the parent form.  When it’s False, you’re prohibited from saving the data.

2.  Add a “BeforeUpdate” event trigger.

The Before Update event is triggered just before changed data is updated in a record or from a control.   I add a simple if/then statement to this event trigger to test the state of the modAllowSave variable.   If the modAllowSave variable is True, then you’re permitted to save the data.  If False, then you’re prohibited.

When using the Before Update event, setting Cancel = True basically says to cancel the impending update.  This is what prevents the Auto Save from saving your record when you click on your subform.  It’s also the code that allows the record to be saved when the submit button is deliberately pressed.

3.  Add an “OnCurrent” event trigger.

I add an On Current event trigger to guard against accidentally saving unwanted information while navigating the form, refreshing the form, or while navigating records in the form.  I know from experience that if a user can screw something up, they will.  The On Current event is like a “catch-all” in case the user does something I didn’t anticipate.  I don’t think that it’s entirely necessary to have it, but I feel better with it in place.

4.  Set the modAllowSave variable to True when you want to save the record.

The only thing left is to add the code that sets the modAllowSave variable to True when you want to save the record.

You can see in my code sample that just before I want to save the record, I set the modAllowSave variable to True.  If you step through the code you’ll see that just before the record saves, it jumps to the Before Update event to test the condition of the modAllowSave variable.  It then saves the record, refreshes the form, and goes to a new record.  I then set modAllowSave back to False.

This code is very simple and I found it to be very effective for my needs.  There may be other methods out there to do this but I found this one offers the most control and flexibility for protecting my input forms from unwanted or accidental data.  Feel free to add comments,  improve on it, or add your own ideas that worked to solve this problem.