Access VBA Macro Programming

Timer

The Timer event is useful if you want to run a procedure at a fixed time interval, such as

re-querying the form so that the data is not stale due to the actions of other users:

Private Sub Form_Timer()

Me.Requery

End Sub

The Me object used here refers to the current form. This is explained in more detail in

Chapter 15.

You need to set the Timer Interval in the form properties sheet. It is set to 0 by default

(which means it never runs). It is measured in milliseconds and the maximum value is 65536

which equates to about a minute. Do not set this to a very low figure (below 1000) since your

timed procedure will then be constantly running and no one will be able to use the form.

Reports

A report is very similar in some ways to a form in that it is based on a table or query. It

contains controls similar to the form—usually text boxes and labels.

You define a single row in the report using controls and add in sorting, grouping, headers,

and footers. When the report is run, your single row definition then repeats throughout the

report giving the user a means of viewing and printing data. Reports are different from forms

in that they do not usually form a user interface.

Creating a Simple Report

This section will show you how to create a simple report, similar to the form we created

earlier based on the orders, order detail, and products tables in the Northwind database.

First, you need to create a query to drive the report. Click the Create tab in the ribbon and

then click the Query Design icon in the Macros & Code group of the ribbon.

For the query, select the tables for Orders, Order Details, and Products. Access will

automatically make the joins between the key fields in the tables. You may need to edit

the join between Orders and Order Details so it shows a straight join instead of a left join.

Include the fields Order ID, Ship Name, Ship Address, Product Name, and Quantity in the

output of the query. Your Query Design window should now look like Figure 9-7.

Make sure the query runs correctly and returns records. Save the query and close the Query

Design window.

Select the Create tab on the ribbon and click the Report Wizard icon in the Reports group

of the ribbon. I am personally not a great fan of wizards because they can often end up doing

something you do not want to do. However, for me, the Report Wizard is the exception, and I

have found that it can produce very professional looking results quickly with sorting and

grouping all arranged. Once you have the report produced in this way, you can then easily

amend it to what you need.

114 Microsoft Access 2010 VBA Macro Programming