• visual basic for applications...

If you are writing an Access database of any complexity you will need knowledge of Microsoft's VBA programming language. Unlike VB or VB.NET, VBA cannot run independently of the software applications it underlies (Microsoft's Office suite). It is integrated with these applications and so does not need to be separately compiled.

Access tables include rules that can help prevent the wrong type of data being entered into tables, for example a valid date in a date field, which must be within a particular range, and you do not need to write VBA for these kinds of constraints as they can be part of the table design. Similarly, when data is added, changed or deleted from tables, the relationships that you can set up, can include automatic cascading of changes to related tables in the database. Wizards can help you to design linked forms. You can go quite a long way in designing your database withour using any VBA, but unless your database is simple, with only a few tables and relationships, you will eventually need to learn how to code events using VBA if you are to successfully design your database.

Each object in the database has Properties. In Access 2003, a form, for example, has over 100 Properties, which includes over 50 events. Some events, such as Before Insert and After Insert can be fundamental to maintaining data integrity. Before Insert helps you to manage what happens when the user attempts to save a new record. After Update tells Access what additional tasks need to be done once the new record has been saved. Every control that you might put on your form also has many events associated with it. These include Before Update and After Update, and various mouse events, as well as the OnClick event. Any button that you add to your form, will typically make use of the OnClick or perhaps OnDblClick events.

The example we shall use here is typical of the kind of additional VBA coding you need to ensure data changes follow business rules. Lets assume there is a table of members, and if they are current members, then they can be included in any mailshots. However if their membership lapses, or if they die, they should not be included in any future mailshots. Mailshots use a database query that includes a boolean data field called 'OKToContact?'. If this is set to 'True' then that member can be included in any mailshot. Where membership lapses, we want the database to automatically change OkToContact to False, just in case the user forgets to update the field. This is even more important if a date of death in added, or if the checkbox 'Deceased?' is checked, Ok To Contact? is also set to False. We also want to change the Notes to say "Do not contact".

Here is the design of the form

The rules that govern amendments to these data fields include: - i) Ok To Contact must be False if there is a Date Of Death ii) Ok To Contact must be False if there is a Membership End Date after the current date. iii) Ok To Contact must be False if Deceased is True iv) Deceased must be True if there is a Date of Death v) When a Date of Death is added, if Membership End date is empty, the two dates should coincide. vi) Always update the Notes field.

These rules should apply to the AfterUpdate event for the various text fields, and the two Options buttons, checking that the associated fields are correctly updated.

To achieve this, the form should be opened in design mode, and each control's Before Update event should be coded. Let's start with the Greg(orian) Date of Death control. On the form this is a text box (Text191). After a date is added, the Deceased option button (Check212) must be set to True. OkToContact (Check143) must be set to False. If the MembershipEndDate field (Text210) is empty, give it the same value. Finally add information to the Notes field (Text2010)

To get to Text191's AfterUpdate Event, open the form in design mode, and right click the Text191 text box, choosing Properties and then open the Event tab. After Update is one of the available events. The small button [...] to the right allows you to add the code, as follows: -

Private Sub Text191_AfterUpdate() 'after updating Date of Death
Dim NewDateValue As Date
NewDateValue = Me.Text191.Value
If NewDateValue <> "" Then
    Me.Check212.Value = True
    Me.Check143.Value = False
    If Me.Text210.Value = "" Then
         Me.Text210.Value = NewDateValue
    End If
    Me.Text2010.Value = Me.Text2010.Value + " *Deceased* - Do Not Contact!!"
End If
End Sub

The Sub routine is Private (this form only)...The date of death is passed to a variable within the Sub procedure called NewDateValue. If it is NOT an empty date, then make sure the two checkboxes are set to true and false respectively. If the Membership End Date text box is empty, put the date of death in there too. Then add the text "*Deceased*-Do Not Contact!!" to whatever is already in the Notes field.

Similar coding can apply to the other controls, although some extra work is needed for rule ii) above, as it needs the current date. This can be obtained from the computer clock. The membership end date must be compared with the computer clock. If the computer clock is after the membership end date, then OK to Contact should be set to False. We could do this for the current record, as it is opened, and whenever certain fields are updated. However to keep the database accurate, the code needs to run whenever the form is opened, or possibly whenever the database is opened. You may decide to only run the code whenever the form is updated, so that it only runs when you are changing (any) data for (any) record. Your decision will depend on how crucial it is for OKToContact to be set to False based upon the passing of the membership end date. You could stop any membership end date from being added to the database if the date is a future date i.e. >Now() . In that case there would be no need to run the code at all. Lets assume that future dates are allowed. If the current record is being amended in any way, then OKToContact is set to False if it is currently set to True, but there is a membership end date, which is in the past.

This is an After Update event for the form, rather than for a particular control within the form. We will use SQL to get all the records from the table, and then update the OKToContact field accordingly.
Private Sub Form_AfterUpdate()
Dim CurrentDate As Date
CurrentDate = Now() 'based on computer clock
Dim strSQL As String
Dim rst As Recordset
strSQL = "Select MembershipEndDate From Members"
'open the recordset
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Dim MembEndDate As Date
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While Not rst.EOF
    MembEndDate = rst!MembershipEndDate
        If MembEndDate < CurrentDate Then
            With rst
                .Edit
                !OKToContact.Value = False
                .Update
            End With
        End If
    rst.MoveNext
    Loop
End If

The code runs whenever any record is updated using the form. The Current Date is obtained from the computer clock. Using SQL a recordset variable stores all the membership end dates dynamically (can be updated). The recordset is opened and as the loop opens each record, provided we are not at EOF (End Of File) then if the membership end date is prior to the current date, OKToContact is set to False, before the record is saved using the Update keyword.

This concludes this example page, using VBA event procedures to manage an Access database.