Show All

Create a form to enter report criteria

1. Create an unbound form that prompts for report criteria.

   How?

   1. In the Database window, click Forms under Objects.
   2. Click the New button on the Database window toolbar.
   3. In the New Form dialog box, click Design View, and click OK.
   4. 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
      AllowFormView	Yes
      AllowDatasheetView	No
      AllowPivotTableView	No
      AllowPivotChartView	No
      ScrollBars	Neither
      RecordSelectors	No
      NavigationButtons	No
      BorderStyle	Dialog

   5. Click the Text Box tool on the toolbox to add a text box to the form for each criteria you want to enter.
   6. 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.

   7. 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.

2. In the Database window, click Macros , and then click the New button on the Database window toolbar.
3. Create a macro that opens the Sales Dialog form.

   How?

   1. 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	Sales Dialog
      View	Form
      Data Mode	Edit
      Window Mode	Dialog

   2. Add a second action, CancelEvent, that cancels previewing or printing the report when the Cancel button on the form is clicked.

   3. If the Condition column is not displayed, click Conditions .

   4. Type the following expression in the Condition column:

      Not IsLoaded("Sales Dialog")

      Note  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 from Northwind into a utility module in your database.)

4. Create a macro that closes the form.

   How?

   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	Sales Dialog
   Save	No

5. Create a macro for the OK button.

   How?

   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

6. Create a macro for the Cancel button.

   How?

   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	Sales Dialog
   Save	No

7. Save and close the macro group. Give the macro group a name— for example, the same name that you gave the unbound form.
8. Add OK and Cancel command buttons to the form.

   How?

   1. Reopen the Sales Dialog form in Design view.
   2. Make sure Control Wizards in the toolbox isn't selected and create an OK command button.
   3. Set its properties as follows.

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

   4. 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

   5. Save and close the form.
9. Enter the criteria in the underlying query or stored procedure for the report.

   How?

   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.

10. In a Microsoft Access project, set the InputParameters property in the main report.

    How?

    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]

11. Attach the macros to the main report.

    How?

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

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