On Change Event in Access


On Change Event

The On Change event can be used to speed up slow opening MS Access forms which can annoy the user.Program activation can sometimes take more than a minute when using a tabbed interface.  We have developed a simple method that often speeds up program activation and form opening by a factor of 10.  The method involves the on change event with a simple technique – use local dummy tables as the record source for forms until the forms are viewed by the user. Below is the setup of our tabbed interface:

There are more forms off to the right side.  The total count of all the form that get opened when the program starts up is about 20, counting the subforms.  This number of forms doesn’t slow the database down much when all the tables are local to the front end.

However, when the database is split into a front end/back end arrangement the time for the program to start up increases dramatically.  For this database the total time exceeded one minute.  This slow startup was not satisfactory for the user.

Our solution was simple – create dummy tables for containing no records for most of the forms associated with each tab.  Below is a list of some of the tables in the database:

On Change Form Event Example

The Z_ tables are the dummy tables and you can see the real linked tables for most of them towards the top of the list – these begin with M_.

The image below shows the contents of one of the Z_ tables:

Access Change Event Tabbed Interface

Note that the table contains a single dummy record.  We have found that this works best compared to having no records in the table.

The image below shows that the Record Source for the stabling form is set to the dummy table Z_Stabling.

VBA Change Event Example

Next we need to show you how and when to switch to the real table.  We change the record source to the real table when the user clicks on the Stabling tab, activating the On Change event:

On Change VBA Code

Note that we have defined an Event Procedure associated with the On Change event of the TabCtl0 object.

Below is the code for the On Change event procedure:

Access Database Form Event Change

So, when the user clicks on the Stabling tab the code checks to see if the record source is set to the dummy record source and if so then changes the record source for the form to the real table ‘L_Stabling’.

The result of using this technique speeded up the database activation from over a minute down to 8 seconds.

Go here to see more about Select Case statements.

We have many more examples of using tab controls in Access databases.

Form Triggers
Table Triggers
Form Flags
Table Triggers
Record Flags
Record Events

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016


Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016