Parameter queries in Reports/Forms

[MikeB]

I don't quite understand how this is supposed to work. I know that if
I put something like this [Tournament number?] in the criteria field
of a query, Access will prompt me for the value to use as the criteria
for the WHERE clause. But that's rather clumsy. If I have lots of
Tournaments, I might not always recall the number of the correct
tournament.

I have read that I can put the value of a field (control) on a form in
there if I put the following code in the criteria field Forms!
[formname]![controlname].

I have seen how the sample in the Northwinds database works. You open
a form that looks at Suppliers If you have a particular Supplier, then
there is a button you can click and all the Products from that
Supplier is shown in a different form.

But that's not what I want to do. I was hoping that I could open a
report and a little form would pop up where I could browse for the
right Tournament and then it would bring up the report for that
Tournament.

The best (and it is far from optimal) is that if I create a form that
lists the Tournaments, and I open that form and select the right
Tournament, I can then open the query for the tournament report with
the right Tournament selected.

However, perhaps because of some misunderstanding on my part, the form
for the Tournaments is always on top of everything. And I cannot open
the query for the report if the form is not open - it then simpoly
prompts me the same way as the example I described first.

Is this how it is supposed to work? Then I can see why people invent
their own swithboards, etc. because then I will have to design a
"master" form where I first off select the Tournament and then run
other reports and forms based on the values in that master form.

Thanks all, I find the advice I get here invaluable, although I find
my learning curve frustratingly slow. I hope I'm not taxing the
collective patience of this group.

 

[John W. Vinson]

But that's not what I want to do. I was hoping that I could open a
report and a little form would pop up where I could browse for the
right Tournament and then it would bring up the report for that
Tournament.

Actually, you can do exactly that.

Create an unbound (or it can even be bound, though that would be unusual) form
named frmCrit. Put a combo box (or other appropriate control) on it to select
the tournament. Base your Report on a query using

=Forms![frmCrit]![cboTournament]

as a criterion.

In the Open event of the report - which fires BEFORE it tries to run its
recordsource query - put code to open frmCrit in Dialog mode:

DoCmd.OpenReport "frmCrit", WindowMode := acDialog

The form will pop open and the report's Open code will halt. This allows the
user to select (or browse for, or whatever) the desired criterion.

Put a command button on frmCrit that does nothing except set the form's
Visible property to No. This will resume the code, opening the report.

To clean up, put code in the Report's Close event to close frmCrit.

 

[MikeB]

Actually, you can do exactly that.

John, would you get the reference if I mentioned "tugging my forelock
to you?"

 

[John W. Vinson]

John, would you get the reference if I mentioned "tugging my forelock
to you?"

I would, and I'd blush and laugh out loud... glad to be of assistance.