Parameter value in Query

  • Thread starter Thread starter Ernie Sersen
  • Start date Start date
E

Ernie Sersen

Does anyone know how to create a drop down list box for a
parameter query prompt when running reports based on
queries? When I run my maintenance location report, the
parameter query box appears stating "input.maint_loc".
As I am adding dozens of maintenance locations every week,
it would be nice to have a drop down list appear in the
parameter query box that is tied to my table of
maintenance locations. Otherwise, I have to open my
maintenance location table and cut/paste the data into the
parameter query box.

Thanks.

Ernie Sersen
 
Does anyone know how to create a drop down list box for a
parameter query prompt when running reports based on
queries? When I run my maintenance location report, the
parameter query box appears stating "input.maint_loc".
As I am adding dozens of maintenance locations every week,
it would be nice to have a drop down list appear in the
parameter query box that is tied to my table of
maintenance locations. Otherwise, I have to open my
maintenance location table and cut/paste the data into the
parameter query box.

Thanks.

Ernie Sersen
Click to expand...

You cannot add a combo box to the parameter [input.maint_loc] prompt,
but you can use a form to pass the parameter to the query.

The query is the record source for a report right?

Create an unbound form.
Add a combo box.

Set the Row Source of the combo box to include the
LocationID field and the LocationName field.
Name the Combo Box 'FindLocation'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the query) [LocationID] field's
criteria line write:
forms!ParamForm!FindLocation

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Location.
Click the command button and then report will run.
When the report closes, it will close the form.
 
Thanks for the help. I tried implementing your solution.
When I went to run the report, an error message "Microsoft
Access cannot find the macro Do.Cmd. The macro doesn't
exist or the macro is new but hasn't been saved". Do I
need to create macro's and then launch them from the form
or have I missed something? I get the same message when
I click on the control button "Cannot find macro Me. etc"

Also, I had to set the column width to 1". Setting it to
0";1" blanked out the entries in my combo box drop down
list. Will this work or again, am I missing something?

Please advise.

Thanks.


-----Original Message-----
Does anyone know how to create a drop down list box for a
parameter query prompt when running reports based on
queries? When I run my maintenance location report, the
parameter query box appears stating "input.maint_loc".
As I am adding dozens of maintenance locations every week,
it would be nice to have a drop down list appear in the
parameter query box that is tied to my table of
maintenance locations. Otherwise, I have to open my
maintenance location table and cut/paste the data into the
parameter query box.

Thanks.

Ernie Sersen
Click to expand...

You cannot add a combo box to the parameter [input.maint_loc] prompt,
but you can use a form to pass the parameter to the query.

The query is the record source for a report right?

Create an unbound form.
Add a combo box.

Set the Row Source of the combo box to include the
LocationID field and the LocationName field.
Name the Combo Box 'FindLocation'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the query) [LocationID] field's
criteria line write:
forms!ParamForm!FindLocation

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Location.
Click the command button and then report will run.
When the report closes, it will close the form.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
-----Original Message-----
Thanks for the help. I tried implementing your solution.
When I went to run the report, an error message "Microsoft
Access cannot find the macro Do.Cmd. The macro doesn't
exist or the macro is new but hasn't been saved". Do I
need to create macro's and then launch them from the form
or have I missed something? I get the same message when
I click on the control button "Cannot find macro Me. etc"

Also, I had to set the column width to 1". Setting it to
0";1" blanked out the entries in my combo box drop down
list. Will this work or again, am I missing something?

Please advise.

Thanks.


-----Original Message-----
Click to expand...
for
a
parameter query prompt when running reports based on
queries? When I run my maintenance location report, the
parameter query box appears stating "input.maint_loc".
As I am adding dozens of maintenance locations every week,
it would be nice to have a drop down list appear in the
parameter query box that is tied to my table of
maintenance locations. Otherwise, I have to open my
maintenance location table and cut/paste the data into the
parameter query box.

Thanks.

Ernie Sersen
Click to expand...

You cannot add a combo box to the parameter [input.maint_loc] prompt,
but you can use a form to pass the parameter to the query.

The query is the record source for a report right?

Create an unbound form.
Add a combo box.

Set the Row Source of the combo box to include the
LocationID field and the LocationName field.
Name the Combo Box 'FindLocation'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the query) [LocationID] field's
criteria line write:
forms!ParamForm!FindLocation

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Location.
Click the command button and then report will run.
When the report closes, it will close the form.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
.

-----Original Message-----
Does anyone know how to create a drop down list box for a
parameter query prompt when running reports based on
queries? When I run my maintenance location report, the
parameter query box appears stating "input.maint_loc".
As I am adding dozens of maintenance locations every week,
it would be nice to have a drop down list appear in the
parameter query box that is tied to my table of
maintenance locations. Otherwise, I have to open my
maintenance location table and cut/paste the data into the
parameter query box.

Thanks.

Ernie Sersen
Click to expand...

You cannot add a combo box to the parameter [input.maint_loc] prompt,
but you can use a form to pass the parameter to the query.

The query is the record source for a report right?

Create an unbound form.
Add a combo box.

Set the Row Source of the combo box to include the
LocationID field and the LocationName field.
Name the Combo Box 'FindLocation'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the query) [LocationID] field's
criteria line write:
forms!ParamForm!FindLocation

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Location.
Click the command button and then report will run.
When the report closes, it will close the form.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Thanks for the help. I tried implementing your solution.
When I went to run the report, an error message "Microsoft
Access cannot find the macro Do.Cmd. The macro doesn't
exist or the macro is new but hasn't been saved". Do I
need to create macro's and then launch them from the form
or have I missed something? I get the same message when
I click on the control button "Cannot find macro Me. etc"

Also, I had to set the column width to 1". Setting it to
0";1" blanked out the entries in my combo box drop down
list. Will this work or again, am I missing something?

Please advise.

Thanks.
-----Original Message-----
Does anyone know how to create a drop down list box for a
parameter query prompt when running reports based on
queries? When I run my maintenance location report, the
parameter query box appears stating "input.maint_loc".
As I am adding dozens of maintenance locations every week,
it would be nice to have a drop down list appear in the
parameter query box that is tied to my table of
maintenance locations. Otherwise, I have to open my
maintenance location table and cut/paste the data into the
parameter query box.

Thanks.

Ernie Sersen
Click to expand...

You cannot add a combo box to the parameter [input.maint_loc] prompt,
but you can use a form to pass the parameter to the query.

The query is the record source for a report right?

Create an unbound form.
Add a combo box.

Set the Row Source of the combo box to include the
LocationID field and the LocationName field.
Name the Combo Box 'FindLocation'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the query) [LocationID] field's
criteria line write:
forms!ParamForm!FindLocation

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Location.
Click the command button and then report will run.
When the report closes, it will close the form.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...

You tried to write the code DoCmd.OpenQuery ... etc. on the event
line, but it belongs in the event code window.
I'll use the Click event of the form's command button to illustrate.
Do the same for the other events as well.

Here's what to do.
Click on the Command Button's On Click Event line.
Write
[Event Procedure]
on that line.
Then click on the button with the 3 dots that appears on that line.
When the event code window opens, the cursor will be flashing between
2 already existing lines of code. Write:
Me.Visible = False
between the 2 already existing lines.

Exit the code window.
Save the form.

Now do the same in the 2 report events (Open and Close) , as indicated
above.
 
Back
Top