Microsoft Access Tips for xBase developers

Provided by Allen Browne, allenbrowne.com

Event Driven Programming

Remember dBase III? You wrote program scripts that called other programs, but at any instant you could point to a line of your code and say "This it where my program is up to."

Access doesn't work like that. In fact, Windows programs don't work like that. You construct your Access code around events: whenever this button is pressed, this code will run; whenever a new record is added, this code will run etc. But if none of those events have happened, none of your code is running and in most cases you don't know what will happen next.

Picture a form with textboxes for Name, Address, Suburb, Postcode, and State. A user enters a Name, and then presses Tab to move to the Address field. Just before allowing the cursor to leave the Name field, Access fires the BeforeUpdate event, and any code attached to that event is run. You could use this to search for duplicate names and disallow the current entry if found. In this way, each field can be validated before moving to the next. If changes to the current control need to be reflected in other fields, use the AfterUpdate event. For example the AfterUpdate event of the Postalcode control could be used to automatically fill in the State.

Since you have no control over the sequence of events, you cannot use a control's BeforeUpdate property to ensure that some entry was made. For example, if a user types nothing in the Name field, its BeforeUpdate event never occurs. However, the form itself has a BeforeUpdate property, which is fired just before all the changes to the current record are written to the table (when the user moves to another record or closes the form). Use the form's BeforeUpdate property for record-level validation, including checking for nulls.

Forms also have a Delete event, which allows you to trap the deletion of any record and take action such as cancelling the event, or writing an audit trail. Once you understand that the entire program as an unpredictable series of events, you become comfortable with allowing Access to run your code whenever it needs to.

On one occasion, I had a control for a construction fee which was directly dependent on 33 other controls on screen. By writing a single function to update the fee, and attaching it to the AfterUpdate property of all 33 controls, I was assured that the construction fee would always be up to date.

It takes some time to become familiar with all the events, and the sequence in which they occur. After the initial hump, they really do make life easier.