Print a Report based on a drop-down menu of choices.

[JR]

I have a report that prints Project Info by Division. I want to create a
macro that when the report is opened, the user can choose the Division they
want to print info for from a drop-down menu.

Essentially I want to imitate the "Like [Enter Division Name]" command you
would enter in a query, except I want a list of choices.

Thanks,
Jodi

 

[fredg]

I have a report that prints Project Info by Division. I want to create a
macro that when the report is opened, the user can choose the Division they
want to print info for from a drop-down menu.

Essentially I want to imitate the "Like [Enter Division Name]" command you
would enter in a query, except I want a list of choices.

Thanks,
Jodi

One way you can do this using a form with a combo box on it and a
query as the report's record source.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Division field.
Name the Combo Box 'FindDivision'.
Set it's Bound column to 1.
Set the Column Width property to 1"

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

Me.Visible = False

Name this form 'ParamForm'.

Open the query that is the report's record source.
On the query's [Division] field criteria line write:
forms!ParamForm!FindDivision

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 Division wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[JR]

Fred,

Ok, this part didn't work:

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

Me.Visible = False

Here is the error message I got:

<The expression On Click you entered as the event property setting produced
the following error: The object doesn’t contain the Automation object “Me.â€.>

JR

One way you can do this using a form with a combo box on it and a
query as the report's record source.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Division field.
Name the Combo Box 'FindDivision'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Name this form 'ParamForm'.

Open the query that is the report's record source.
On the query's [Division] field criteria line write:
forms!ParamForm!FindDivision

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 Division wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[JR]

Ok, ignore my prior reply. I had put the code in the wrong spot.

Everything is running just fine, except that no records are being pulled.
I'm assuming the error is in my query somewhere??

JR

 

[JR]

Fred,
Never mind ... I figured it out. I just had to change the Bound column to
"2".

Thanks for your help - you gave me the opportunity to learn something new!

JR

 

[bmullin via AccessMonster.com]

Hi,

I've been following this post. Thanks for the information, so far it's
worked great. I'm doing a simalar thing so that individual users, working on
address data entry, can print their own lable report with the addresses they
they, themselves have entered.

One question, some times the user is Bob, sometimes it is Sally, but
sometimes I want them to be able to print all the labels. Sometimes the user
field is empty. How do I add an option so that all the labels come up.

In the original example, the eqivalent would be all divisions, even the
division value has been left null.

Thanks for any input you could provide.

I have a report that prints Project Info by Division. I want to create a
macro that when the report is opened, the user can choose the Division they

[quoted text clipped - 5 lines]

Thanks,
Jodi

One way you can do this using a form with a combo box on it and a
query as the report's record source.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Division field.
Name the Combo Box 'FindDivision'.
Set it's Bound column to 1.
Set the Column Width property to 1"

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

Me.Visible = False

Name this form 'ParamForm'.

Open the query that is the report's record source.
On the query's [Division] field criteria line write:
forms!ParamForm!FindDivision

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 Division wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[fredg]

Hi,

I've been following this post. Thanks for the information, so far it's
worked great. I'm doing a simalar thing so that individual users, working on
address data entry, can print their own lable report with the addresses they
they, themselves have entered.

One question, some times the user is Bob, sometimes it is Sally, but
sometimes I want them to be able to print all the labels. Sometimes the user
field is empty. How do I add an option so that all the labels come up.

In the original example, the eqivalent would be all divisions, even the
division value has been left null.

Thanks for any input you could provide.

I have a report that prints Project Info by Division. I want to create a
macro that when the report is opened, the user can choose the Division they

[quoted text clipped - 5 lines]

Thanks,
Jodi

One way you can do this using a form with a combo box on it and a
query as the report's record source.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Division field.
Name the Combo Box 'FindDivision'.
Set it's Bound column to 1.
Set the Column Width property to 1"

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

Me.Visible = False

Name this form 'ParamForm'.

Open the query that is the report's record source.
On the query's [Division] field criteria line write:
forms!ParamForm!FindDivision

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 Division wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

As criteria in the query, write, on 2 rows as indicated below:

forms!ParamForm!FindDivision
forms!ParamForm!FindDivision Is Null

The above will create an OR criteria

Either select a Division to show just that division, or leave the
control blank to show all records.

Is that what you are looking for?

 

[bmullin via AccessMonster.com]

Hi,

It seemed to show all of the records regardless of whether I picked an option
or left it blank.

Brian

Hi,

[quoted text clipped - 50 lines]
As criteria in the query, write, on 2 rows as indicated below:

forms!ParamForm!FindDivision
forms!ParamForm!FindDivision Is Null

The above will create an OR criteria

Either select a Division to show just that division, or leave the
control blank to show all records.

Is that what you are looking for?

 

[bmullin via AccessMonster.com]

Ahhh... I got it to work I'm not sure what I did wrong the first time.

Thanks very much.

Hi,

[quoted text clipped - 50 lines]
As criteria in the query, write, on 2 rows as indicated below:

forms!ParamForm!FindDivision
forms!ParamForm!FindDivision Is Null

The above will create an OR criteria

Either select a Division to show just that division, or leave the
control blank to show all records.

Is that what you are looking for?