Parameter Setting for Multiple Reports

[shelton1979]

I have several reports that I need to generate at the same time using a macro, however I did not want to see 5 input dialog boxes. Is there a way to put the ID number in one time and it be used for all report/queries????

 

[glalsop]

You need to create your own form to do this.

Create a form that has controls for the fields that you want to use as filters for your queries/reports.

Then, using this syntax

[Forms]![YourFormNameHere]![YourControlNameHere]

you can refernce the value of that control in your queries/reports.

Hope that helps

-Gary

 

[Chase998]

Gary,

I'm rather new to Access DBs. Could you give an example of the syntax for the control name box? I'm having the same problem as Shelton, and it's a feature I desperately need to work at the moment.

I'm assuming you mean create a form to enter the parameter needed to run the report? Where does the syntax go? In the VBA?

Thanks!

CHASE

 

[glalsop]

Hi Chase998 -

You are correct in your assumption that you will need to create a form to enter the parameters.

Say that you have a report (or several reports) that you would like to run dynamically based on a date range entered on a form.

First create your form, lets say frmParameters.

On frmParameters we need two text box controls to collect the dates, say txtStartDate and txtEndDate.

Now in the query that serves as the record source for the report we can reference the values in the form. To do this open the query in design view. In the Criteria section of the field that serves as your date field you can say:

Between [Forms]![frmParameters]![txtStartDate] and [Forms]![frmParameters]![txtEndDate]

Every time you run the query (or report) the data will be filtered according to frmParameters (keeping in mind that frmParameters must be open).

Alternatively, you could examine the OpenReport method of the DoCmd object in Access help. This allows you to supply a Where condition to your report based any criteria you like.

Good luck

-Gary

 

[Chase998]

Gary,

Thanks for the instructions! Works well! One question, though. When I used [FORMS]![frmPARAMETER]![FIELD] option in the query, it comes up with a prompt box, but the prompt box's text is the same as the parameter.

Is there a way to change the text for the parameter box when it appears so that the message for entering data is customized?

Thanks again!

CHASE

 

[glalsop]

Hi Chase!

You are getting prompted because Access can't reconcile your form reference. Check two things:

1) Make sure that you are using the name of the field on the FORM, as your [FIELD], not the name of the field on the table.

2) Make sure that frmPARAMETER is open and that [FIELD] has a value in it when you run the report.

Good luck

-Gary