The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

Microsoft Access BeforeUpdate and AfterUpdate Events

Many times when working with data you’ll need to write some code or start a Macro that validates or changes the information being passed around in your database. When working on a form, you’ve got so many options available that it’s difficult to decide when to change data, when to check it, and where to go about doing all of this.

There are two events available to you when working with Controls and Forms: BeforeUpdate and AfterUpdate. The BeforeUpdate event will occur before a new value update, but after you’ve initiated the change (otherwise BeforeUpdate would just run constantly – something has to set it in motion). AfterUpdate is the event that takes place after the data has been updated or changed. But when do you use these events, and does it really matter where your code or macro goes?

When Using Controls

If you need to verify what data is being input into a control, then you should use the BeforeUpdate event. For example, when someone changes a field, you’ll need to check that field to make sure the data should be accepted and placed into the table. You wouldn’t do this on the AfterUpdate event, as it would be too late – the data would already be in the table. You can also compare the value that is going to be saved to the value that was previously in the control if it had a prior value. If, however, you need to change a value on your form depending on what was input into a certain field, you would use the AfterUpdate event to make that change as after the control is updated, you’ll be using the new value.

When Using Forms

Use the BeforeUpdate event for data validation. For instance if you wanted a custom message box to appear when someone tried to save a record, but entered incorrect data, or left data blank, you would set that Macro or Code on the BeforeUpdate event as the record won’t accept all of your changes, then check to see if they were correct. Any kind of change to the data – validation, calculations, or adding data – is best done on the BeforeUpdate event as you can better trap problems and avoid incorrect data being stored in underlying tables.

Use the AfterUpdate event on forms whenever you want to analyze the record that was just created either through looping through the recordset to analyze the most current information, or simply work a calculation or two.

The best advice anyone can give regarding these two events is to test extensively against sample data. By manipulating items right before a record saves or by saving data without testing it out first the possibility that procedure is wrong is greatly increased. Your best bet when building this type of code is to take it step by step. Don’t build an entire procedure only to realize that you’ve gotten the event wrong (in any case, not just BeforeUpdate and AfterUpdate), or go about it in a way that could be damaging to your data.

Test, test, test, and then test some more!


©2014 Blue Moose Technology, LLC

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Microsoft Access BeforeUpdate and AfterUpdate Events