|
|
|
Microsoft Access - Programming in VBA
|
|
|
|
|
|
|
|
|
|
|
Virtually anyone can create an Access database in very little time and with little effort, by using the Wizards provided.
Many people can create a database by using a wizard then customising the tables, forms etc.
Fewer people can create a database with or without wizards AND use MODULES and Visual Basic for Applications (VBA) and a certain amount of SQL (Structured Query Language) statements.
Utilising VBA and SQL statements together give a database not only a high degree of automation but far more flexibility and power to get the database to carryout complex data retrieval and consolidation of data. Essentially you can get Access to carryout actions more commonly associated with Word and Excel. However if it is more efficient to return to Word or Access or retrieve data from these applications, then Access can handle this as well.
Before we can get on to VBA and SQL there are a few points to note:
Events:
Many actions in Access can be triggered by events such as On Loading a form, or After Updating (after changing) data in a field ..etc..etc. When such an event occurs, a VBA procedure you write, can then take a specific action or set of actions. Such 'Event Procedures' gave 'intelligence' to the database.
Data:
Data for a field or other object need not come directly from a table, but may come from a SQL statement placed in the Record Source of the object or in a VBA procedure. It may also come from a Look Up into a different table, or may even come from a predetermined list and go INTO a table which has a field made ready for that purpose. In short the generation of, input and output of, data is very flexible and good databases need such flexibility. To see where the majority of these events can occur we need to look at the properties of an object such as a field in a form.
Let's look at some form objects and their properties:
|
A simple Text Box |
A Combo Box |
|
(Most of your fields in forms will be text boxes) |
(Lets the user pick from a column or many columns of data) |
|
|
|
|
Notice that there are many properties in these lists (shown are just a few, there are many more further down the lists). Notice also that the lists are different in each object. Each list shown above is showing the ALL properties tab. If we wished to look only at event properties only, we would click the EVENT tab, as shown below: |
|
|
|
|
|
Notice there are many events here but a VBA procedure is attached to the 'On Enter' event and this procedure runs as soon as the user enters this text field in the form. |
Notice there are many events here but a VBA procedure is attached to the 'After Update' event and this procedure runs as soon as the user selects an item in the combo's list in the form. |
|
|
|
|
OK, this is all - well, a bit abstract you might say but how can I use such capability in reality.??? Example of Usage: Imagine you had a Text box into which was entered in case1: a payment or Case2: a Date of Birth. Now for Case1, if we used this box for a payment, then when the amount is entered by the user, we could use an 'After Update' event to calculate a commission from the amount input and place the result into another Text box we have labelled as 'Commission'. In Case2:, when the date of birth is entered we could use an 'After Update' event to convert the date to a number, take that number away from today's date (as a number), divide this by 365.25(average days in a year, accounting for Leap year) and arrive at the age of the person whose date of birth it is and place the result into another Text box we have labelled as 'Age Now'. Making the result box visible or invisible: In either case if payment or Date of Birth are entered, then inside the procedure's code we could have the 'Commission' or 'Age Now' box made visible, or made invisible if data is not entered. Although in the design view the box is there, it is more intelligent that during operation, the 'Age Now' or 'Commission' box is made invisible if no data is available for it. - this saves clutter in the form, giving the user only that information which is pertinent.
How to Create an Event Procedure: Given the above information you might now like to create an Event Procedure, if you do click here <event procedures>.
|
|
