Use a form to enter report criteria

Create an unbound form that prompts for report criteria

1. Create an unbound form.
   
   
2. In Design view, set the following form properties.

   Property	Setting
   Caption	Name you want to appear in the title bar of the form
   DefaultView	Single Form
   ViewsAllowed	Form
   ScrollBars	Neither
   RecordSelectors	No
   NavigationButtons	No
   BorderStyle	Dialog

3. Add an unbound text box for each criteria you want to enter.
   
   
4. Set the properties for the text boxes as follows.

   Property	Setting
   Name	Name that describes the type of criteria; for example, BeginningDate.
   Format	Format that reflects the data type of the criteria. For example, for a date criteria, select a format such as Medium Date.

5. Save the form and give it a name, such as Sales Dialog.

   You'll add OK and Cancel command buttons to the form after you create macros for them.

Create the macros for the form in a macro group

1. In the Database window, click Macros , and then click the New button on the Database window toolbar.

   For this procedure, you'll create four macros in a macro group.

2. Create a macro that opens the unbound form. Begin by clicking Macro Names to display the Macro Name column. Type a macro name, such as Open Dialog, in the Macro Name column, and then click the OpenForm action. Then set the action arguments as follows.

   Argument	Setting
   Form Name	Name of the unbound form; for example, Sales Dialog
   View	Form
   Data Mode	Edit
   Window Mode	Dialog

   
   

   Add a second action, CancelEvent, that cancels previewing or printing the report when the Cancel button on the form is clicked. Then click Conditions to display the Condition column, and type the following expression in the Condition column:

   Not IsLoaded("Sales Dialog")

   IsLoaded is a function defined in the Utility Functions module in the Northwind sample database. It's used to check whether a form is open in Form view or Datasheet view. You must define the IsLoaded function in your database before you can use it. (You can copy and paste this function into a utility module in your database.) For information on defining a function, click . For information about opening Northwind, click .

3. Create a macro that closes the form. Give the macro a name, such as Close Dialog. Click the Close action. Then set its action arguments as follows.

   Argument	Setting
   Object Type	Form
   Object Name	Name of the unbound form
   Save	No

4. Create a macro for the OK button. This macro hides the form. Give the macro a name, such as OK, and click the SetValue action. Then set its action arguments as follows.

   Argument	Setting
   Item	[Visible]
   Expression	No

5. Create a macro for the Cancel button. This macro closes the form. Give the macro a name, such as Cancel, and click the Close action. Then set its action arguments as follows.

   Argument	Setting
   Object Type	Form
   Object Name	Name of the unbound form
   Save	No

6. Save and close the macro group. Give the macro group a name — for example, the same name that you gave the unbound form.

Add OK and Cancel command buttons to the form

1. Reopen the unbound form in Design view.
   
   
2. Create an OK command button, and set its properties as follows. (Make sure Control Wizards in the toolbox isn't pressed in.)

   Property	Setting
   Name	OK
   Caption	OK
   Default	Yes
   OnClick	Name of the macro; for example, Sales Dialog.OK

3. Create a Cancel command button, and set its properties as follows.

   Property	Setting
   Name	Cancel
   Caption	Cancel
   OnClick	Name of the macro; for example, Sales Dialog.Cancel

4. Save and close the form.

Enter the criteria in the underlying query or stored procedure for the report

1. Open the underlying query or stored procedure for the report in Design view.
   
   
2. Enter the criteria for the data. In the expression, use the Forms object, the name of the form, and the names of the controls in the criteria.
   • For example, in a Microsoft Access database (.mdb), for a form called Sales Dialog, you would use the following expression to refer to controls named Beginning Date and Ending Date in the query:

     Between [Forms]![Sales Dialog]![Beginning Date] And [Forms]![Sales Dialog]![Ending Date]

   • In a Microsoft Access project (.adp), you must first explicitly name the parameters in the stored procedure; for example:

     @Beginning_Date datetime, @Ending_Date datetime

     and then use those parameters in the WHERE clause; for example:

     WHERE Sales.ShippedDate Between @Beginning_Date And @Ending_Date

     In an Access project, you set the reference to the controls on the form in the InputParameters property in the report, as shown in the next procedure.

Set the InputParameters property in the main report (Access project only)

1. Open the report in Design view.
   
   
2. Set the report's InputParameters property to a string that specifies the parameters that are passed to the stored procedure that the report is bound to. As in the following example, the string must be an expression that includes the parameters you specified in the stored procedure and the reference to the controls on the dialog box:

   @Beginning_date datetime = [Forms]![Sales Dialog]![Beginning Date], @Ending_date datetime = [Forms]![Sales Dialog]![Ending Date]

Attach the macros to the main report

1. Open the report in Design view.
   
   
2. Set the following report properties.

   Property	Setting
   OnOpen	Name of the macro that opens the unbound form; for example, Sales Dialog.Open Dialog
   OnClose	Name of the macro that closes the unbound form; for example, Sales Dialog.Close Dialog