|
|
|
Technology
>>
Programming
>>
Programming MSAccess Applications
|
Programming MSAccess Applications
Improve Functionality with Macros and Visual Basic for Applications
Jun 19, 2009 © Harry P. Schlanger
An industry application written in Access 2007 is presented for inspection to learn how applications
can be made more professional, using macros and VBA programming.
Access 2007 readers knowledgeable in creating applications with wizards and
macros can
easily learn how to make their applications more powerful using 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 as
version Access 2003 or version
Access 2007.
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 (Figure 1 below). 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,
below is the code behind the Student dropdown of the main data entry form.
VBA Transferrable to other Microsoft Applications
In summary, a school application written in Access 2007 by the author, using VBA is presented for
studying the manner in which code enhances the functionality of applications. Coding makes applications
more flexible and professional, ehancing the level of user-friendliness. Snippets of code and the skills
learned are often transferrable across to other VBA applications, such as in Excel, Word, etc.
The copyright of the article Programming MSAccess Applications: Improve Functionality with Macros and
Visual Basic for Applications is owned by Harry P. Schlanger.
Permission to republish in print or online must be granted by the author in writing.
smart-koala.com © 2009 All Rights Reserved
|
|
Custom Search
Links:
Website Construction:
Gum Leaf Designs © 2009
|
