Access VBA Macro Programming

show the property type and if it is read-only. Clicking a method displays the syntax for

parameters and which ones are optional and mandatory. The window at the bottom of the

Object Browser displays the full syntax of the method, with optional properties shown in

square brackets ([ ]).

Communicating with the Tables

One of the main uses of VBA in Access is to communicate with tables and to manipulate the

data within them. To do this, you must use theRecordsetobject, which is part of the DAO

object model (and can also be part of ADO).

The following example is based on the employees table of the Northwind sample database.

You can load this database by loading Access and then clicking Sample in the center navigation

pane (Available Templates). This will display an icon to load the Northwind database.

Insert the following code into a module within the Northwind database:

Sub ViewData()

Dim RecSet As Recordset

Set RecSet = CurrentDb.OpenRecordset("Employees")

Do Until RecSet.EOF

MsgBox RecSet![Last Name]

MsgBox RecSet![First Name]

RecSet.MoveNext

Loop

RecSet.Close

Set RecSet = Nothing

End Sub

This code first creates aRecordsetobject called RecSet by using theDimstatement. The

Recordsetobject is then set to point to the table Employees by using theOpenRecordset

method of theCurrentDbobject. You could also use a query name instead of a table name,

or you could use an SQL select statement here.

One of the problems with using an SQL statement is that you need to make sure it works,

otherwise you will get an unexpected error message.

Using a Do Until ... Loop statement with the condition set to EOF (End Of File), the

recordset is iterated through and message boxes will display the Last Name and First Name

of each employee.

AMoveNextstatement moves to the next record so the EOF marker is eventually reached.

This is a very important statement because if it is not included, the code remains on the same

record and goes nowhere. This is very easy to miss when you are concentrating on the code

to read the fields, leaving you running a procedure that can never end.

186 Microsoft Access 2010 VBA Macro Programming