Use ComboBox in Form to run report from macro

[Guest]

I want a form to be a user-friendly interface to select criteria and run
reports.
I would like to have a combo box allow for a drop down menu that contains
the criteria for the report. I'm asuming the easiest it to make the
selection a filter for the query driving the report. But I don't know how to
build the macro with the right steps to accomplish this. The only macros
I've done in the past have been to simply open reports where the query asks
to type in the criteria. Now that I want the person to be able to select
from a "drop down menu", I'm in over my head. Can anyone help? Thank you!
Kathy

 

[Guest]

I'll pass along the advice that I received from Australia:

The solution is to use a form, and set the Locked property of the bound
controls to Yes, so the user cannot edit them.
Allen Browne - Microsoft MVP. Perth, Western Australia.

 

[Steve Schapel]

Kathy,

A "drop down menu" is called a Combobox.

There are two approaches here. When you make a macro using the
OpenReport action, you will see an item in the arguments section at the
bottom of the screen, called 'Where condition'. Here you can enter
something the equivalent of this...
[YourKeyField]=[Forms]![NameOfYourForm]![NameOfCombobox]

The other approach, as you suggested, is to put something the equivalent
of this...
[Forms]![NameOfYourForm]![NameOfCombobox]
.... in the Criteria of the applicable field in the query that the report
is based on.

 

[Guest]

Thanks. I did the second and put it in the query feeding the report. I then
noticed that in the macro that is set to run the report I had to "OpenQuery"
before I could "OpenReport". I assume that's normal. Normal or not, it
seems to be doing what I need it to, though. Thanks for your help! And it
was fast, too!

Kathy,

A "drop down menu" is called a Combobox.

There are two approaches here. When you make a macro using the
OpenReport action, you will see an item in the arguments section at the
bottom of the screen, called 'Where condition'. Here you can enter
something the equivalent of this...
[YourKeyField]=[Forms]![NameOfYourForm]![NameOfCombobox]

The other approach, as you suggested, is to put something the equivalent
of this...
[Forms]![NameOfYourForm]![NameOfCombobox]
.... in the Criteria of the applicable field in the query that the report
is based on.

--
Steve Schapel, Microsoft Access MVP

I want a form to be a user-friendly interface to select criteria and run
reports.
I would like to have a combo box allow for a drop down menu that contains
the criteria for the report. I'm asuming the easiest it to make the
selection a filter for the query driving the report. But I don't know how to
build the macro with the right steps to accomplish this. The only macros
I've done in the past have been to simply open reports where the query asks
to type in the criteria. Now that I want the person to be able to select
from a "drop down menu", I'm in over my head. Can anyone help? Thank you!
Kathy

 

[Steve Schapel]

Kathy,

No, that is not normal. There is no need for an OpenQuery action in
your macro. Not only that, but if you do have an OpenQuery action, it
will have no impact at all on the OpenReport action. What is/was the
problem?

 

[Guest]

You're right. I took that out and it is still working great. I wonder what
I'd done early on that didn't work and made me think I had to put that in.

Much cleaner macro now! Thank you!

 

[Guest]

Kathy,

Congrats on getting your report to work! I'm trying to do the same thing.
Where is your macro to OpenReport - on the form or the report. What is
[YourKeyField]? Is it the record selected in the combo box?

thanks,
ynj

Thanks. I did the second and put it in the query feeding the report. I then
noticed that in the macro that is set to run the report I had to "OpenQuery"
before I could "OpenReport". I assume that's normal. Normal or not, it
seems to be doing what I need it to, though. Thanks for your help! And it
was fast, too!

Kathy,

A "drop down menu" is called a Combobox.

There are two approaches here. When you make a macro using the
OpenReport action, you will see an item in the arguments section at the
bottom of the screen, called 'Where condition'. Here you can enter
something the equivalent of this...
[YourKeyField]=[Forms]![NameOfYourForm]![NameOfCombobox]

The other approach, as you suggested, is to put something the equivalent
of this...
[Forms]![NameOfYourForm]![NameOfCombobox]
.... in the Criteria of the applicable field in the query that the report
is based on.

--
Steve Schapel, Microsoft Access MVP

I want a form to be a user-friendly interface to select criteria and run
reports.
I would like to have a combo box allow for a drop down menu that contains
the criteria for the report. I'm asuming the easiest it to make the
selection a filter for the query driving the report. But I don't know how to
build the macro with the right steps to accomplish this. The only macros
I've done in the past have been to simply open reports where the query asks
to type in the criteria. Now that I want the person to be able to select
from a "drop down menu", I'm in over my head. Can anyone help? Thank you!
Kathy

 

[Guest]

Hello Steve,
Additional question on us of OpenReport Actions & Forms... (Subform issue)

I have a working form setup with the OpenReport Action, using the "Where
condition".
I created a "Master form" and wanted to set this form up as a sub on that
master.
But the macro doesn't work on the subform.
Do I need to create a new macro for this "new" form?

Thanks in advance!!
--
Vivian

Kathy,

A "drop down menu" is called a Combobox.

There are two approaches here. When you make a macro using the
OpenReport action, you will see an item in the arguments section at the
bottom of the screen, called 'Where condition'. Here you can enter
something the equivalent of this...
[YourKeyField]=[Forms]![NameOfYourForm]![NameOfCombobox]

The other approach, as you suggested, is to put something the equivalent
of this...
[Forms]![NameOfYourForm]![NameOfCombobox]
.... in the Criteria of the applicable field in the query that the report
is based on.

--
Steve Schapel, Microsoft Access MVP

I want a form to be a user-friendly interface to select criteria and run
reports.
I would like to have a combo box allow for a drop down menu that contains
the criteria for the report. I'm asuming the easiest it to make the
selection a filter for the query driving the report. But I don't know how to
build the macro with the right steps to accomplish this. The only macros
I've done in the past have been to simply open reports where the query asks
to type in the criteria. Now that I want the person to be able to select
from a "drop down menu", I'm in over my head. Can anyone help? Thank you!
Kathy

 

[Guest]

Steve,

How would this work if the form had more than one combo box requiring the
user to select multiple criteria?

Thanks in advance!

Kathy,

A "drop down menu" is called a Combobox.

There are two approaches here. When you make a macro using the
OpenReport action, you will see an item in the arguments section at the
bottom of the screen, called 'Where condition'. Here you can enter
something the equivalent of this...
[YourKeyField]=[Forms]![NameOfYourForm]![NameOfCombobox]

The other approach, as you suggested, is to put something the equivalent
of this...
[Forms]![NameOfYourForm]![NameOfCombobox]
.... in the Criteria of the applicable field in the query that the report
is based on.

--
Steve Schapel, Microsoft Access MVP

I want a form to be a user-friendly interface to select criteria and run
reports.
I would like to have a combo box allow for a drop down menu that contains
the criteria for the report. I'm asuming the easiest it to make the
selection a filter for the query driving the report. But I don't know how to
build the macro with the right steps to accomplish this. The only macros
I've done in the past have been to simply open reports where the query asks
to type in the criteria. Now that I want the person to be able to select
from a "drop down menu", I'm in over my head. Can anyone help? Thank you!
Kathy