Access07:Heights:Visual Basic

Background
At this stage this probably doesn’t mean much so I will try to illustrate what a module is by creating a simple example.
 * "A module is a collection of Visual Basic for Applications declarations and procedures that are stored together as a unit. There are two types : class modules and standard modules".

If you created the macros in the Heights database, you have already started to add some "stored" instructions. A module takes the concept of a macro much further and allows for very sophisticated instructions to be created for use with your database.

The underlying language that is used by Access is Visual Basic. This is a core programming language that sits behind Microsoft Office, and indeed is being included in other non-Microsoft products as well.

An Access database is made up of many objects. Each object has properties (like the background colour we looked at in macros), and events (like if we click the mouse on the object). When an event happens we can make Access follow some stored instructions. These instructions are called event procedures.

If you don’t fully understand all this techo stuff don’t worry we’ll create a simple class module for a form.
 * Class modules are event procedures that are associated with standard form or report events. These can be accessed via an object’s properties and selecting the event tab.
 * Standard modules are those which we create to perform repetitive or common tasks.

A class Module that changes background of a control
Purpose

What we are going to do is to add some code so that each time a new record is displayed Access will check the value of the Height.

* If the Height is < 160 change the background colour to red. * If the Height is < 170 change the background colour to light blue. * Otherwise the background colour should be white.

This could be used when a numeric value becomes negative in an accounting system, and will show you the steps needed to create a module.

Method

1. Open the People form (frmPeople)

2. Change to Design view

3. In the top left hand corner of the form is a small black box (contains the form properties), right click the box and select Properties.



4. On the Property Sheet select the Event tab. An event is something that happens, so what we are going to instruct the computer to do is when the current record is displayed (On Current) we want some code to check the Heights and depending on its value change the colour, so ..

5. Click on the button with three [...] in the "On Current" row,

6. From the Pop-up select Code Builder, this will display a new window used for Visual Basic Programming, and some of the code has been entered to start you off

Private Sub Form_Current End Sub

7. Enter the code following:

Private Sub Form_Current ' Some simple Visual Basic Code ' Changes the background colour depending ' on the value of Height. ' Author: M H Verhaart (c) 2008-7-22 PeoHeight.BackColor = 16777215   'white If PeoHeight.Value < 160 Then PeoHeight.BackColor = 255      'red ElseIf PeoHeight.Value < 170 Then PeoHeight.BackColor = 16776960 'light blue End If End Sub

8. Save the code (call it modPeople)

9. Close the VisualBasic Window

10. Close the property sheet attached to the form

11. Back to the Home button on the Ribbon bar and change to Form View

12. Use the navigation buttons (at the bottom of the form) to move from record to record. The Height background should change depending on the value as instructed by the code.