Use VBA to Print a Filter By Form Recordset

  • Article

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Use VBA to Print a Filter By Form Recordset

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release. by Susan Sales Harkins

Often, you'll want to perform a quick search through a database without going to the trouble of creating a query. In these cases, Access's Filter By Form feature is especially convenient. However, once you find the records you're looking for, you may find that printing the results isn't as straightforward as you would expect. In this article, we'll review the basics of locating records using the Filter By Form feature. Then, we'll use code to pass the filtered results to a report's record source.

Filter By Form overview

The Filter By Form feature allows you to search for records by entering filter criteria into existing forms. The process is completely automated, and you can use the feature with most any form. You just click a button, enter a search string, and then click a second button. The resulting records Access displays are known as a filtered recordset (or filtered set). When you're ready to remove the filter and return to the initial recordset, you just click another button.

Printing filtered recordsets

At first, printing a filtered recordset seems just as effortless as creating it. You can simply click the Print button and Access prints all the records that match your search string using the current form. If you want to print just the current record instead of all the filtered records, choose File | Print from the menu bar, choose the Selected Record(s) option button, and click OK. Unfortunately, printing the results using an existing report is a different matter--there isn't a built-in way of applying the filter to a report's record source. Access does provide the ability to save the filter as a query, and you can create a new report from the filtered recordset, but these options are often overkill for the task at hand. After all, the main benefit to using Filter By Form is its speed and ease of use, so you want the reporting to reflect those aspects. Here's where our code comes in.

Setting it all up

To demonstrate our printing technique, we'll create a simple form and use the Filter By Form feature to select specific records. We'll assume you have a database of real estate properties and want to be able to filter on any address information. We'll use a command button on the form to run Visual Basic for Applications (VBA) code that sends the results of the filtered recordset to a simple report.

Set up the data

To follow along exactly with our example, create a table named tblSale, using Figure A as a guide for the structure and data to enter. Of course, you can also use an existing table or query you have, as long as there's plenty of searchable data.

Figure A: We'll filter this sample data using address criteria.

Build the report

We'll use the Report Wizard to build a simple report. First, select tblSale in the Database window. Next, choose Report from the New Object button. Double-click on the Report Wizard and then move all of the available fields to the Selected Fields list box. Click Next until you get to the last step of the wizard. Then, enter Property Address Report where you're prompted for a title, and click Finish. Access previews the report with all of the data from tblSale, as shown in Figure B. Close the report, and then click on the Reports tab in the Database window. Right-click on the newly created report, choose Rename from the shortcut menu, and change the name to rptFilter.

Figure B: We'll display the filtered set in this simple report.

Create the filter form

To create the basic form, click on the Tables tab and select tblSale in the Database window. Then, select AutoForm from the New Object button menu. After Access finishes creating the form, as shown in Figure C, save it as frmFilter.

Figure C: We'll use the Filter By Form feature with this form to locate data.

Adding VBA code to the form

Now that you've created the form, add a command button to the form's header. To do so, first switch to Design view. Then, from the View menu, select Form Header | Footer to display those parts of the form. Ensure that the Control Wizards button is deselected on the Toolbox, then add a command button to the header section. Change the button's name to cmdPrint and change the Caption property to Print.

Now we're ready to add the code that will print a filtered set, so click the Code button on the Form Design toolbar. When Access opens the form's module, declare a module-level variable named iFilterType by adding the following statement to the form's General Declaration's area:

Dim iFilterType As Integer

After declaring that variable, select cmdPrint from the module window's Object dropdown list. If necessary, choose Click from the Procedure dropdown list. Then, enter the procedure shown in Listing A at the insertion point.

Listing A: Click event

Dim frm As Form
Set frm = Forms!frmFilter
If iFilterType = acApplyFilter Then
    DoCmd.OpenReport "rptFilter", acViewPreview, , frm.Filter
Else
    DoCmd.OpenReport "rptFilter", acViewPreview
End If

Next, choose Form from the Object dropdown list and select ApplyFilter from the Procedure dropdown list. At the insertion point, enter the statement

iFilterType = ApplyType

Finally, save and close the module.

Putting it all together

All the pieces are ready, so let's see how they fit together. First, switch back to Form view and initiate the Filter By Form feature by clicking the Filter By Form button on the Form View toolbar. Doing so displays a blank version of your form. (Later, Access will display the previous search string, but right now there isn't one to display.)

The next step is to enter the search string. As you can see in Figure D, we entered the name of a particular street, ROCKFORD LANE, to return all the listed properties on that street. For your convenience, you can use the search controls as if they were comboboxes. Click to the right of each control to display a list of field entries. You can choose an item from this list instead of typing it yourself.

Figure D: We're searching for all the properties on Rockford Lane.

Once you've entered a search string, click the Apply Filter button on the Form View toolbar. Our search returned four records. The record number control at the bottom of the screen displays the number of records in the filtered set and also identifies the result as a filtered set, as shown in Figure E.

Figure E: Our search turned up four properties on Rockford Lane.

After filtering your records, simply click your Print button to display those filtered records in Print Preview, as shown in Figure F. Although Access's Print button only prints the filtered set in the form environment, using cmdPrint lets you print the same recordset in a report formatted the way you want.

Figure F: Clicking our Print button populates the report with just the records in our filtered set.

How it works

Let's take a look at how everything works. After we specified the search string using Filter By Form, we clicked the Apply Filter button. Doing so triggered the form's ApplyFilter event. The statement we attached to that event defines the module-level variable iFilterType. Specifically, iFilterType equals the ApplyType value. That value identifies the filter event that triggered the ApplyFilter event. This can happen when the user does any of the following:

  • Chooses Apply Filter | Sort from the Records menu, chooses Apply Filter | Sort from the Filter menu, or clicks the Apply Filter button on the Form View toolbar.
  • Selects Filter By Selection or Filter Excluding Selection from the Records | Filter submenu or clicks Filter By Selection on the Form View toolbar.
  • Removes the filter by choosing Remove Filter | Sort from the Records menu or clicks Remove Filter on the Form View toolbar.
  • Selects Filter By Selection, Filter Excluding Selection, or Remove Filter | Sort from the shortcut menu, or enters a value or expression in the Filter For box on the shortcut menu.
  • Closes the Advanced Filter | Sort window or the Filter By Form window.
  • Chooses Advanced Filter | Sort or Filter By Form from the Filter menu after clicking the Filter By Form button.

Nothing else happens until you click cmdPrint. You can trigger any number of filtering events without printing a thing. When you're ready to print, you simply click the Print button you created. If a filter has been applied to the recordset, the iFilterType equals 1, which is the same as the constant acApplyFilter. Access then passes the form's filter to the OpenReport method's wherecondition argument. This argument is a SQL WHERE clause, without the word WHERE in it. For instance, in our example, frm.Filter equals

((tblSale.strStreet="ROCKFORD LANE"))

If you click cmdPrint when your recordset isn't filtered, Access prints all the records in the underlying recordset.

Notes

After you've established a filter, you can switch between the two recordsets--the complete recordset and the filtered set--by choosing Apply Filter | Sort and Remove Filter | Sort from the Records menu. In addition, we chose to display, instead of print, the report. If you'd rather send the records to your printer automatically, replace the acViewPreview constant in the

DoCmd.OpenReport "rptFilter", acViewPreview, , frm.Filter

statement in Listing A with the acNormal constant (or leave the argument blank).

Conclusion

The Filter By Form feature is a great tool for quickly finding records that meet specific criteria. Unfortunately, Access doesn't provide an equally easy way of applying the filter criteria to an existing report. In this article, we've shown you how to get around this limitation using VBA.

Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.