A previous article introduced writing a first program in VBA, a language available across all Microsoft Office products. In MSAccess, it includes the Access form, the properties window from which code can be written or accessed, and the Visual Basic language syntax and debugging tools.
How VBA Works
When a user clicks on an object in the user interface (UI), such as a button, this event activates program code "behind" the object, which is associated with this click event . In general, the purpose of Visual Basic for Applications is to carry out logic and help to perform a host of interface or database functions such as:
- Open or close a form
- Run a query
- Store data to the database
- Etc.
User objects are available in the MSAccess Interface Development Environment (IDE), such as labels, text boxes, buttons, etc. When the user interacts with any of these controls, it is called an "event"; for example, clicking on a button or hitting "Enter" after typing in data.
Developing the User Interface
Developing a user interface begins with client requirements, as a simple problem will demonstrate: A student receives a mark for a completed task, which needs to be entered into the interface, including the maximum amount possible for that piece of work. Clicking a button will initiate calculating the percentage score and display the result.
A mock-up screen in Figure 1 shows the objects needed with a possible page layout. The steps to follow are explained and correspond to the red numbered boxes. Changes to the object property are explained.
Steps to follow:
- Create an Access Form, which will become the user interface
- Add a Label object to the top of the Form big enough to contain instructions to the user. To achieve this, change the "Caption" property of the Label object.
- Add three additional Labels boxes with text as shown. Adjust some of the other presentation properties such as Font size, colour, etc., of these label boxes.
- Place two small Text boxes and remove the default Text property message so that no message is displayed. The Font and other property characteristics may be set. Rename the Text boxes using Microsoft's Camel notation. For example, txtScore and txtMaximum.
- Place another small Label box for the solution. A label is preferred so the user cannot key anything into it. Set the Font and other property characteristics as desired, but be consistent. Rename this Label box using Camel notation, eg lblPercentage..
- Add two Command boxes (i.e. buttons). Change the default Caption property, one to "Calculate Percentage" and the other to display "Exit". Rename the Command boxes with a logical name, eg cmdCalculate and cmdExit.
For steps 7 and 8, to set-up an event procedure, click on the ellipsis of the command button object's onClick event. Type in the code as shown below, then run the form and test the interface.
Typing in the VBA Program
With the opened code window, one may type in the following simple Visual Basic for Applications program in the onClick event procedure for the Calculate button.
Private Sub cmdCalculate_Click()
Dim Answer, Score, Maximum As Integer
' Input Section - check data integrity
txtScore.SetFocus
If txtScore.Text = "" Then
MsgBox ("Please Enter the Student's Score!")
Exit Sub
End If
txtMaximum.SetFocus
If txtMaximum.Text = "" Then
MsgBox ("Please Enter a MAXIMUM Score!")
Exit Sub
End If
' Calculate the percentage and display to the form
txtScore.SetFocus: Score = txtScore.Text
txtMaximum.SetFocus: Maximum = txtMaximum.Text
Answer = (Score / Maximum) * 100
lblPercentage.Caption = CStr(Answer) & "%"
End Sub
The onClick event of the Exit button should contain the single statement: DoCmd.Close
Running and Debugging the Program
To run the program, one needs to click on the "View Form" icon located at the top left of the Access IDE. Figure 2 shows if a test value of 15 is entered for the score and 20 for maximum score, the message: "Answer is: 75%", is produced.
For more information about VBA programming language, the reader may search the Internet for tutorials, or visit one such site at FunctionX.
 |
 |
 |
