Create a form to enter report criteria

  1. Create an unbound form (unbound form or report: A form or report that isn't connected to a record source such as a table, query, or SQL statement. (The form's or report's RecordSource property is blank.)) that prompts for report criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).

ShowHow?

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Forms Button image 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 (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), 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
  1. Click the Text Box tool Button image on the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.) to add a text box to the form for each criteria you want to enter.
  2. 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 (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) of the criteria. For example, for a date criteria, select a format such as Medium Date.
  1. 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.

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Macros Button image, and then click the New button on the Database window toolbar.
  2. Create a macro that opens the Sales Dialog form.

ShowHow?

  1. Begin by clicking Macro Names Button image 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 (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.). Then set the action arguments (action argument: Additional information required by some macro actions. For example, the object affected by the action or special conditions under which the action is carried out.) as follows.
Argument Setting
Form Name Sales Dialog
View Form
Data Mode Edit
Window Mode Dialog
  1. Add a second action, CancelEvent, that cancels previewing or printing the report when the Cancel button on the form is clicked.
  2. If the Condition column is not displayed, click Conditions Button image.
  3. Type the following expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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 (Form view: A view that displays a form to show or accept data. Form view is the primary means of adding and modifying data in tables. You can also change the design of a form in this view.) or Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.). 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.)

  1. Create a macro that closes the form.

ShowHow?

  1. Create a macro for the OK button.

ShowHow?

  1. Create a macro for the Cancel button.

ShowHow?

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

ShowHow?

  1. Reopen the Sales Dialog form in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Make sure Control Wizards Button image in the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.) 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
  1. 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
  1. Save and close the form.
  1. Enter the criteria in the underlying query or stored procedure for the report.

ShowHow?

  1. Open the underlying query or stored procedure for the report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Enter the criteria for the data. In the expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.), use the Forms object, the name of the form, and the names of the controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) in the criteria.

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

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

  1. In a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.), set the InputParameters property in the main report.

ShowHow?

  1. Open the report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  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 (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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]

  1. Attach the macros to the main report.

ShowHow?

  1. Open the report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  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
 
 
Applies to:
Access 2003