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
      ViewsAllowed Form
      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