Microsoft Access allows you to quickly create forms for viewing and editing your data. One issue you may have run into with forms is that it’s easy to accidentally edit the data on a form with a slip of the fingers. Here’s the scenario:
You open an Access form and are looking at the data. Someone comes into your office and distracts you, and you accidentally lean on your keyboard without noticing. Leaning on the keyboard causes data to be changed - the keys you accidentally pressed on the keyboard get entered into the form. Once your guest leaves, you return your attention to the screen, but don’t notice that you have accidentally changed your data. As soon as you move to a different record or close the form, your changes are automatically be saved. Oops!
There are several ways to deal with this situation, and I’m going to discuss a couple of them. Your first line of defense is awareness. Did you know that there is a visual indicator on a form that tells you if the data has been changed? Here’s an example where I accidentally leaned on the “j” key.
Notice the pencil in the left margin? That means the record on the screen has been changed, and the changes have not yet been saved. Here’s your chance to undo the damage - press the “Esc” key on your keyboard. When you do, any unsaved changes on the record will be discarded, and the pencil will change to a black arrow, like this:
There are a couple of problems with this approach. First, it’s possible to disable that indicator. A form has a property called “Record Selectors”. When this property is set to “No”, the margin on the left disappears, and along with it, the helpful icon. The second problem with this approach is that is requires diligence. It’s easy to miss the pencil icon. If you don’t notice it, the changes will automatically be saved as soon as you navigate to another record.
Which brings me to my second idea for dealing with this issue. Using a little bit of code, you can disable the editing feature of the form until the user clicks an “Edit” button. Here are the steps you’ll need to take.
In Design mode, change the “Allow Edits” property of the form to “No”:
Add a command button to the form. Set its “Caption” property to “Edit” and its “Name” property to “cmdEdit”.
Note: if the “Command Button Wizard” appears, click “Cancel”.
Add another command button to the form. Set its “Caption” property to “Save”; its “Enabled” property to “No”; and its “Name” property to “cmdSave”.
Add another command button to the form. Set its “Caption” property to “Cancel”; its “Enabled” property to “No”; and its “Name” property to “cmdCancel”.
Locate the “Click” event for the “cmdEdit” button, and add the following code (note: if you’re not sure how to locate the “Click” event, see the hint at the end of this article):
Me.AllowEdits = True
cmdSave.Enabled = True
cmdCancel.Enabled = True
cmdEdit.Enabled = False Locate the “Click” event for the “cmdSave” button, and add the following code:
DoCmd.RunCommand acCmdSaveRecord
Me.AllowEdits = False
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdEdit.Enabled = True Locate the “Click” event for the “cmdCancel” button, and add the following code:
DoCmd.RunCommand acCmdUndo
Me.AllowEdits = False
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdEdit.Enabled = True
That’s it! My completed code for the form looks like this:
Save your changes, and then test your form. You’ll find that you can no longer edit a record without clicking the “Edit” button. Once you’ve clicked the “Edit” button, you can make changes to the data, and then click the “Save” button or the “Cancel” button.
There are many approaches to protecting the data on your form, including the two we looked at here: being vigilant about noticing the visual cues, and implementing some simple code to “lock” the form. Of course, your database should also be backed up on a regular basis so that you can restore lost data when necessary. But using the technique covered here, you probably won’t need to resort to that.
Thanks for reading, and please leave comments to let me know if this article is helpful or needs improvement. All feedback is welcome!
Note: If you aren’t sure how to find the “Click” event for one of your command buttons, here’s how to do that:
With the form in Design mode, right-click on the button.
From the menu, choose “Build Event”.
If you are prompted to choose a Builder, choose “Code Builder”.
Click OK.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2017 North Port Solutions, LLC. All right reserved.
Images: Martin, Kimberlee. 19 January 2017.