Selecting report criteria in a form


Q: How can I select report criteria in a form?

A. You can either use Access macros or Access VBA.

The following solution uses Access macros

Presume that we have a Customers table, which among other data contains a CustomerID fileld and a CustomerName field. We have created a CustomerOrders report from this and other tables. We now want to have the ability to select in a form whether to print the report for all customers or for just one customer.

Create one form, frmReportSelector and two macros, mcrShowAllCustomers and mcrShowOneCustomer.

On the form, add two command buttons, one for selecting all customers and one for selecting one customer and connect their Click events to the two macros, respectively.

Also, add a combo box and set its properties as follows

 Property  Property Value
 Bound Column  1
 Columns  2
 Column Widths  0";2"
 Limit To List  Yes
 Name  cboSelector
 Row Source  SELECT CustomerID, CustomerName FROM Customers
 Row Source Type  Table/Query

Set the action parameters of the two macros as follows

   mcrShowAllCustomers  mcrShowOneCustomer
 Action OpenReport  OpenReport
 Report Name CustomerOrders  CustomerOrders
 Where Condition    [Customers]![CustomerID]=
 [Forms]![frmReportSelector]![cboSelector]

This will do the trick