How to Use Form Filter on Report ~ LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, January 31, 2012

How to Use Form Filter on Report

Introduction.

Normally we use a Query or processed data table as Report Source for printing selected data items, like Customer Invoices or Customer Ledger Statement, etc.

How do we take a printout of the current record on the Form?

Let us try this simple method to start with, before using the Form Filter on Report. We need some sample data, a Form, and a Report to try this out.

Sample Trial Run.

Import the Order details and Products Table from the Northwind sample database.
Use the Report Wizard and design a Report using the Order Details Table as Record Source (sample report view is given below) and save the Report with the name Rpt_OrderDetails.
Use the Form Wizard and design multiple items (continuous) Form using the Order Details Table (sample data view image is given below) and save it with the name Frm_OrderDetails.
Open the Form Frm_OrderDetails in the design view.
Select the Command Button Tool from the Toolbox and draw a Command Button on the Header Section of the Form.
While the Command Button is still in the selected state display the Property Sheet (Design - - >Tools - - > Property Sheet or use F4) and select All Tab on the Property Sheet.
Change the Name Property Value to cmdRpt and change the Caption Property Value to Run Report (see the Form image above).
Select the Event Tab of the Property Sheet.
Select [Event Procedure] from the drop-down list of the On Click event property and click on the Build (. . .) button at the right edge of the property to open the VBA window with the empty Subroutine stub of the Command Button Click Event Procedure.
Copy and paste the middle line of Code between the empty Sub-Routine Stub of the Command Button Click event procedure:
```
Private Sub cmdRpt_Click()

   DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , "[order id] = " & Me![order id]

End Sub
```
Check the middle line of the above code. The last parameter to the DoCmd.OpenReport command is a Filter condition to select the current record's Order ID number to filter all records with the same Order ID number. We have inserted one extra comma between the printing option and the filter condition to skip the choice of using the name of a Query as Report Source Data.
The filter condition "[Order Id] = " & Me![Order Id] states that take all the records with the current record Order ID Number as source data for the Report.
Save the form Frm_OrderDetails and open it in normal view.
Click on any record with the same Order ID in more than one record or any record you like.
Click on the Run Report Command Button to open the Rpt_OrderDetails with the selected record.
Close the Report and you may try it again after selecting some other record on the Form.

The above example uses only a single record or several records with the same Order ID to print the Report.

Several Items Selection.

Next, we will do it with more flexibility after setting the Filter Property Value of the Form. This can be done after selecting one or more Order IDs for filtering the data on the Form before we open the Report for printing the data on the Form. We must set a reference to the Filter Property of the Form replacing the criteria setting we used earlier: "[order id] = " & Me![order id], on the DoCmd.OpenReport command.

With this change, the user can filter data based on any column value on the Form (like Order Id or Quantity or UnitPrice, etc.) and use the result set to print the Report.

Make a copy of the form Frm_OrderDetails and paste it with the name Frm_Orderdetails2.
Open the form Frm_Orderdetails2 in the design view.
Click on the Command Button to select and display its property sheet (F4).
Select the Event Tab and select the On Click property.
Click on the Build (. . .) button to open the VBA window.

Copy the following code and paste replacing the existing lines of code on the Form Module:

Private Sub cmdRpt_Click()
    If Me.Filter = "" Then
        MsgBox "Apply a filter to the Form first."
    Else
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , Me.Filter
    End If
End Sub

Check the last parameter setting in the DoCmd.OpenReport statement. We are asking the Report to use whatever criteria setting is available in the current Form’s (Me) Filter Property Value (like ([Order Details].[Order ID] In (30,31,32))) to pick records for the Report. Save the Form and open it in its normal view.
Click on the Command Button to open the Report.
You will be greeted with a message asking to 'Apply a filter to the Form first'. When you click on the Command Button the program checks whether the Form's Filter Property is set with a Filter condition or not, like the text we have used in the first example above, or like the sample shown in Step-7 above.
Note: Remember, once you apply a filter on the form the text of the last filter condition remains saved on the Filter Property. The Filter action is supported by another property value setting: FilterOn = True/False. When you Toggle the filter action on the Form the FilterOn property value changes but the Filter property value criteria text is not removed. You will be greeted with the above message only when the Filter Property Value is empty.
Click on the Order ID column on any record.
Click on the Filter Toolbar button (see the image below)to display the Filter selection control.
A list of the selected field values (Order ID numbers) is displayed and shows all the values are check-marked, indicating that all the values are in the selected state.
Click on the Select All Option to de-select all the items.
Now, put check marks on the Order ID Numbers 30, 31 & 32, and Click OK to close the Filter Control.
The form now shows only records of Order ID numbers 30, 31 & 32.
Click on the Run Report Command Button on the Header of the Form to open the Report in Print Preview with the records filtered on the Form.
Close the Report.

Try it with a different Field.
Place the cursor on the Quantity Field on any record on the Form.
Click on the Filter Toolbar Button to display the Filter Control.
Select the items with the Quantity values 100,200 & 300 and click the OK button to close the control and filter the records you have selected.
Open the Report by clicking on the Run Report Command Button and check the report contents.
Close the Report.
Click on the Toggle Filter Toolbar button. The Filter action is reversed and all the records are back on the Form (or the FilterOn Property Value is set as False now).
Now, Click on the Run Report Command Button to preview the Report.

The Report shows the last filtered records only, rather than all the records from the Form. When we toggle the filter Microsoft Access sets the FilterOn Property Value to False nullifying the effect of the filter action on the form, without removing the filter condition string inserted in the Filter Property, because the User may click the Toggle Filter button again to bring back data filtered by the last set filter condition. Our program keeps using it because the Filter Property value is not empty and we are not checking the status of the FilterOn Property setting.

But, we can solve this issue with a few changes in our program as follows:

Private Sub cmdRpt_Click()
    If Me.FilterOn Then
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , Me.Filter
    Else
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview
    End If
End Sub

Change the program as shown above and try the effect of the Filter and Toggle Filter action on the Form as well as on the Report.

Technorati Tags: Reports, Microsoft

LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, January 31, 2012