Automating Microsoft Access with VBA

The existing billing report shows one way of limiting the records that populate the report. The report's query grabs values from the setup form. It's a simple technique and requires no VBA code other than the short procedure needed to launch the report from the form.

VBA has an alternative to this technique. As mentioned earlier, you can use a couple of the OpenReport method's arguments to specify a particular query or even a WHERE clause. If you choose this route, you need to remove the form references from the report's underlying query and then replace the report opening command button's Click event with the following procedure:

Private Sub cmdOpenReport_Click() Dim strCriteria As String Dim frm As Form Set frm = Forms!BillingReportSetup On Error GoTo HandleErr strCriteria = "Clients.ClientID = " & frm!cboClient.Value & _ " AND Timeslips.DateWorked Between #" & frm!txtStartDate & _ "# AND #" & frm!txtEndDate & "#" Debug.Print strCriteria DoCmd.OpenReport "BillingReport", acViewPreview, , strCriteria ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description Resume ExitHere End Sub

As you can see, this method appears a bit more complex because you must build the WHERE string. There's no right or wrong to the solution you choose. With time, you'll find that each method has its pros and cons and choose according to the requirements.

Applying a Filter and Sort Order

At this point, you've already seen a lot of expressions and literal strings used to limit the records that make it to TimeTrack's billing report. But there's one filtering property you haven't seen yet. Both forms and reports have properties that enable you to apply filters to the underlying recordset:

• Filter A string expression that identifies the actual filter as a WHERE clause without the WHERE keyword.

• FilterOn Enables the filter specified in the Filter property.

• OrderBy Determines how the records are sorted, but the report's native sort order(s) take precedence over this property.

• OrderByOn Enables the sort specified in the OrderBy property.

Setting the Filter and FilterOn properties through VBA is just one way to automate this process and allow enough flexibility so that you can use the same report to display different recordsets.

To set a filter string, use the following syntax:

Me.Filter = filterstring

where filterstring represents the actual filtering criteria by which you want to filter the report. For instance, if you wanted to filter the billing report by a particular client, you might use the following statement

Me.Filter = "ClientID = 1"

You would need to follow that statement with the FilterOn property in the form

Me.FilterOn = True

Setting the FilterOn property to False would disable the current filter.

The sorting properties are similar to the filtering properties in that you specify a SQL ORDER BY clause, without using the ORDER BY keywords, and then you enable the sort using the following syntax

Me.OrderBy = "ClientID, ProjectID DESC" Me.OrderByOn = True