Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Open a Parameter Query or Report from a Selection Form

Access Howto:

How to Open a Parameter Query or Report from a Selection Form

by  Quehay  Posted    (Edited  )
First you set up the query that you want to view with the following criterion for the field(s) that you want users to be able to select (substituting the actual names of your form and combo box):

[tt][Forms]![frm_QryView]![cboFieldChoice][/tt]

The combo box has its RowSource set to the field of the table from which you want to select. Use the wizard to create the box and only choose to show the field with the parameter entry.

**Understand that you'll visually refer to the [tt]LastName[/tt] etc., field in the combo (it will be in column #2) but Access will refer to the key of that table (in column #1 with the width set by the wizard to "0";", so that it's not visible in the dropdown list). For this reason make sure that the field in the query is the key/id field of that table, not the [tt]LastName[/tt], etc. field.

The next step is to create a dedicated parameter form. Just a small form with its popup property set to true and your combo on it. Place a button on the form that has the following in the MyButton_Click() event:

DoCmd.OpenQuery "myParaQuery"
DoCmd.Close "myParaForm"

To deal with user failure to select a value you could have the [tt] button.enabled [/tt] property set to False and the reset to True by the combo_Click() event. To allow the option of return of all values if the user selects none go back to the query and in the "OR" parameter line below the first parameter entry just put the exact parameter prompt with "Is Null" after it, e.g., [Enter the Last Name] Is Null.

You can have the button open a report based on the query just as easily. Create the report based on the query and change the DoCmd line to DoCmd.OpenReport "myParaReport".
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top