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:
Blue Claw Database Design is your premier source of Microsoft Access programming and database support. Both small business and department-level databases. Desktop installation as well as cloud/internet based solutions.
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
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
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:
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
The image below shows the contents of one of the Z_ tables:
Note that the table contains a single dummy record. We have found that this works best compared to having no records in the
The image below shows that the Record Source for the stabling form is set to the dummy table Z_Stabling.
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:
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:
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
in Access databases.