Microsoft Access VBA Macro Programming

If a query you are going to use in VBA is complicated, encompassing several tables, it is a

good idea to work it out first using the Query Design window. This allows you to see if your

query will work and provides more meaningful error messages than if you were running it

inside of VBA.

You will find that more complicated queries are not updatable. If, for example, you have

two tables with a many-to-many relationship, you cannot change the data in the resultant

query, either via the Query Result window or through VBA.

You can use several different types of query, as described in the following sections.

Select Query

The Select query is the simplest kind of query and is the same as the one constructed earlier

in this chapter. In its simplest form, it appears as:

Select * from MyTable

The star (*) means all fields, so this query returns all the data in the table MyTable.

As you saw earlier, you can join to other tables using straight joins (inner joins) or

left/right joins (outer joins) dependent on how you wish to see your data. You can also add in

criteria and sorting orders.

Because this query actually returns records, it cannot be executed directly from within

VBA, but must be opened as a recordset and manipulated using VBA. To do this, you must

enter the VBE window (pressALT+F11) and insert a new module by using Insert | Module

from the VBE menu.

Enter the following code:

Sub TestQuery()

Dim ReSet As Recordset

Set ReSet = CurrentDb.OpenRecordset("MyQuery")

Do Until ReSet.EOF

MsgBox ReSet!Company

ReSet.MoveNext

Loop

End Sub

The code assumes that you saved your query as instructed in the earlier section of this

chapter as MyQuery.

This code creates a recordset object called ReSet and sets it to point at the query that you

created earlier. It then uses a Do Until... Loop to iterate through the records and displays

the Company field as a message box. Note that the exclamation mark (!) is used in the

statement to display the company name instead of a dot or period (.).

154 Microsoft Access 2010 VBA Macro Programming