Use Macro to run queries in multiple option groups

  • Thread starter Thread starter Crystal via AccessMonster.com
  • Start date Start date
C

Crystal via AccessMonster.com

I have 4 separate option groups. The first is a list of reports the user can
run. The second allows the user to choose a detailed or summary version of
their report. The third lists options as to how to sort the data and the
fourth gives advanced sorting options. They are all radio buttons. The
user can only select one choice per group. I can create a query for each
possible combination, but I'm not sure how to run them accordingly. Is there
a way I can do this from a macro so that when the user clicks the view report
button, the appropriate query will run based on the selections and then the
correct report will display for the user to view or print? Any help is
greatly appreciated. Also, if anyone knows of a better way to do this, I'm
up for suggestions :)

Thanks,
Crystal
 
Crystal,

ou would not create a query for each possible combination.

As I understand it, the first two option groups just relate to which
report to use, right? If I was doing this in a macro, I would put an
OpenReport action for each possible report, and use the Condition column
in the macro design (if you can't see this column, select it from the
View menu) to control which report is printed. The Condition would be
entered something like this...

[FirstOptionGroup]=2 And [SecondOptionGroup]=1
.... in which case the Report Name argument of the OpenReport action
would be the equivalent of...
YourSecondReport SummaryVersion

As for the second part, though, I am not sure. If the report has any
Sorting & Grouping defined in the report design, then this takes
priority over any sort order defined in the report's underlying query.
For this reason, it is generally advised to never try to control the
sort order in a report via the query. Therefore you need to manipulate
the Sorting & Grouping of the report, based on your user's choices,
which I have never done via a macro, and I imagine is a bit tricky, in
fact probably not possible. It may be possible using a VBA procedure,
I'm not sure. The grinch method would be to create a separate version
of the report to represent each sorting option, and include this in the
macro condition process by expanding on the idea I already mentioned.
There is another approach, which may take some time to fine-tune if the
choices you are offering your user are complex. You can use an
expression in the Sorting and Grouping dialog of the report design. To
give a very simple example, if your option group gave two options, to
sort by either FieldA or FieldB, then you can write this in the Sorting
& Grouping...
=Choose([Forms]![YourForm]![ThirdOptionGroup],[FieldA],[FieldB])
 
i have a query that i want to use the selected option in this query based on a prompt to filter a report based on this selection, what can i do? i think it would be best to use a macro but what are the steps i would take to do this??
 
Back
Top