Microsoft

Quickly save the current record in Access using code

You don't need a lot of code to save data in Access. If you're using code to save data, you need only one statement!

There are a number of ways to save data via an Access form. The easiest way is to let a bound form take care of it. However, sometimes even a bound form loses data, unexpectedly. For instance, if a user forgets to press Enter after changing or entering a value and code takes over, VBA won't consider that unsaved value in any processing. In addition, some developers like to bypass the bound behaviors and control the save process using code. Either way, the Dirty property is usually the most efficient way to save the current record.

The Dirty property applies to both the Form and Report objects. When a user changes a record but hasn't saved it, the Dirty property returns True -- the object is dirty.  When the record hasn't been changed, Dirty returns False. This Boolean structure makes it easy to save a record quickly -- or not -- as needed. Simply drop the following statement into code:

If Me.Dirty Then Me.Dirty = False

Setting the object's Dirty property to False forces Access to save the current record. If the object isn't dirty, the statement does nothing.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
hedwyg
hedwyg

... because neither kicks off the before_update event so that proper validation occurs, regardless of whether the user clicked my save button or hit one of the navigation buttons. Still looking for a good solution.

burch_r
burch_r

Although the code provided in the article may work, to me there is a feel of "wagging the dog by the tail." Rather than depending on Access to force a save based on setting a property, the dirty record could be saved with a single line of code, with more clarity and elegance. If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord Once the record is saved, the Dirty property will be set to FALSE, which is the way the application is intended to function.

Editor's Picks