|
|
|
Technology
>>
Programming
>>
Programming a User Interface in MSAccess 2007
|
Programming a User Interface in MSAccess 2007
Using Event-Driven VBA Procedures
Aug 9, 2009 © Harry P. Schlanger
The user interface may contain many object types (text boxes, labels, etc.). Interaction is achieved
when objects respond to user events, which then activate procedures.
The Visual Basic for Applications (VBA) programming language is 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
A previous article introduced the
VBA programming language
in the MSAccess development environment, including
the properties window from which code can be written or accessed, and how to run the code.
User objects are available in the MSAccess Interface Development Environment (IDE), such as labels, text
boxes, buttons, etc. When the user clicks on any of these controls, it is called an "event"; for example, clicking on a button or
hitting "Enter" after typing in data.
In general, the purpose of Visual Basic for Applications code may be used to carry out logic or 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.
Developing the User Interface
When a user clicks on an object on the user interface (UI), such as a button, this activates a
program "behind" the object, which is associated with this click event.
Developing a user interface begins with client requirements, as the following 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 below 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. The finished
application written in Access 2007 may be
downloaded.
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 setup 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.
A value of 15 could be entered for the score and 20 for the maximum score. Clicking on the Calculate button
will produce the message: "Answer is: 75%", as shown in the figure below.
For more information about VBA programming language, the reader may search the Internet for tutorials, or
visit one such site at
FunctionX.
The copyright of the article Programming a User Interface in MSAccess 2007: Using Event-Driven VBA
Procedures 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
|
