totn Access

MS Access 2003: Use the OpenReport VBA command to return only those records containing a keyword

This MSAccess tutorial explains how to use the OpenReport VBA command to return only those records containing a keyword in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I would like to create a report that will return only those records that contain a certain word from the PartsReplaced field. This word, say "monitor", will be selected by the user when running the report. I would like to set up a table with the words that the user can use.

How do I do this?

Answer:We've created an Access sample that you can download.

Download Access 2000 version

Microsoft Access

In this example, we've created a table in Access called Filter_Values that has one field called Value. This field is populated with the keywords used to filter the report results.

Microsoft Access

Next, we've created a Form with a combo box called cboKeyword that displays the values from the Filter_Value table. In this example, the user must select one of the keywords and then click on the "Run Report" button.

The VBA code behind the "Run Report" button is:

Private Sub cmdRunReport_Click()

   'User must select a value from the combo box
   If IsNull(cboKeyword) = True Then
      MsgBox "You must select a keyword."

   'Open report called rptEntries where the PartsReplaced field
   ' contains the value in the cboKeyword combo box
   Else
      DoCmd.OpenReport "rptEntries", acViewPreview, , "PartsReplaced like '*" & cboKeyword & "*'"
   End If

End Sub

What this code does is filter the results for the rptEntries report so that only those records where the PartsReplaced field contains the value selected in the cboKeyword combo box. This is achieved with the following line of code:

DoCmd.OpenReport "rptEntries", acViewPreview, , "PartsReplaced like '*" & cboKeyword & "*'"

The LIKE condition in the last parameter of the OpenReport command filters the report results so that only records containing the keyword in the PartsReplaced field are displayed.

In this example, we've selected "monitor" in the combo box, so the report will only display records where the PartsReplaced field contains the word "monitor", as shown below:

Microsoft Access