Programming MS Access Applications
Improve Functionality with Macros and Visual Basic for Applications
Apr 17, 2008
Access 2007 readers knowledgeable in creating applications with wizards and macros can easily learn how to make their applications more professional using the power of VBA programming.
Unlike programming tutorials, the starting point here is to present a full-fledged application that was created for a real organization – a school, by the author. The application is offered for analysis and "reverse-engineering" to inspect the code and learn how code can make the application perform in superior ways that wizards cannot offer.
This new coding knowledge is transferable to any other Microsoft VBA application, such as Word and Excel, etc. Readers without at least a rudimentary knowledge of Visual Basic should first consult a good Access VBA programming book before proceeding.
Building the School Application
The school application is a simple interim reporting system for teachers and can be downloaded (Ref IT001). On opening of the database file, the security feature has a button “Options…” that should be clicked, then the “Enable Contents” checkbox should be checked (see Figure 1). The Application was built up from scratch in the following manner:
- The school’s reporting requirements were analysed
- Tables were created, then linked with referential integrity
- Tables were populated with test data
- Forms and reports were created using the available wizards
- Forms and reports were coded using Visual Basic for Applications and tested
- Tables were emptied and populated with the school’s real data
The database has a pre-loaded list of students, subjects, and teachers. It opens with a main data entry form (Figure 2) , ready for teachers to enter the students’ performance. A Help button explains the main functionality of the application. Another button "Report Preview ~ Students for this Subject" opens the interim report, which can then be printed.
Initiating the Data Entry Form
To initiate data into the data grid, the user needs to click on the subject dropdown and make a selection, say, “games programming”. This loads the data for this subject into the form and students may then be viewed by navigating records. A list of students is also loaded in the student dropdown and allows quick access to records.
Inspecting Macros and VBA Code
To find the code behind each form (which includes macros written in code as DoCmd), one should follow these steps:
- Right click on the form and select Design View (the property sheet opens up with a list of form events)
- Click an Event Procedure ellipsis to open up the code window for that Event
- At the top of this code page is a comments section that describes the code. There are also a lot of explanatory comments throughout the page. Study how the subroutines and functions work. For example, Figure 3 shows the code behind the Student dropdown of the main data entry form.
An industry application written in Access 2007 by the author using VBA is presented for studying the manner in which code enhances the functionality of applications, making them more professional and user-friendly. Snippets of code and the skills learned are often transferrable across to other VBA applications, such as in Excel, Word, etc.
 |
 |
 |
 |
