Passing a parameter for a query to multiple queries

[Guest]

I have created 4 cross-tab queries that are similar to each other. I use the
data from these 4 cross-tab queries to populate a report with subreports
(each subreport represents data from each of the cross-tab queries). What I
need to do now..if possible...is to provide users with a way to enter
criteria for the 4 cross-tab queries at one time. So, therefore, a user
would type in that they want to see dates from say 12/11/04 to 1/1/05. I
would like to apply this criteria to each of the cross-tab queries
automatically. I would then like it to run the cross-tab queries and update
the report. I would also like for this prompt to happen when they open the
report (from a switchboard).

Does anyone have any ideas about collecting the criteria and then applying
it to multiple queries?

Any help would be appreciated. I am fairly new at doing this kind of stuff
in Access.

Thanks

 

[Nikos Yannacopoulos]

Dear Newbie,

In my experience, the most user-friendly way for inputting parameters is
through an unbound form. The additional benefit is that the parameters are
then available to as many objects as you want for reading, and that's not
limited to queries (your primary concern right now), they can also be read
by reports, code, macros etc as long as the form remains open. For instance,
you could use a couple of text boxes in your report header to print the date
range requested by the user.

The logic of this approach would be: the button on the switchboard does not
open the report directly; instead, it opens a form (called frmParameters in
my example). The form has two unbound text boxes (called txtDateFrom and
txtDateTo in my example), and a command button whcih opens the report (and
then closes the parameter entry form). The queries are modified to "read"
the parameters from the form.
So, when the user clicks on the report button on the switchboard he gets a
form, fills in the date range and clicks the button (called cmdRunReport in
my example) to run the report. Simple, intuitive, effective.

To make the queries get the parameters from the form, the criterion in them
should be something like:

= Forms!frmParameters!txtDateFrom And <= Forms!frmParameters!txtDateTo

or something along these lines, depending on what you want.
For any other kind of query this would be enough; crosstabs have a
peculiarity, though, they require that parameters be explicitly declared as
such in order to recognize them. To do that, while in query design, go Query

Parameters and type in the two expressions:

Forms!frmParameters!txtDateFrom
Forms!frmParameters!txtDateTo
specifying type Date/Time.
The queries are now ready.

The code behind the command button to open the report would look something
like:

Private Sub cmdRunReport_Click()
DoCmd.OpenReport "ReportName", acPreview '[etc]
DoCmd.Close acForm, Me.Name
End Sub

Additionally, you could add some validity checking on the dates before the
report is run, along the lines of:

Private Sub cmdRunReport_Click()

If IsNull(Me.txtDateFrom) Or IsNull(Me.txtDateTo) Then
ptp = "You need to enter both dates."
GoTo Abort
End If

If Me.txtDateFrom > Me.txtDateTo Then
ptp = "End date earlier than Start date!"
GoTo Abort
End If

DoCmd.OpenReport "ReportName", acPreview '[etc]
DoCmd.Close acForm, Me.Name
Exit Sub

Abort:
ttl = "Attention!"
MsgBox ptp, vbOKOnly, ttl

End Sub

Finally, to add to the "professional look" of your application, you might
want to use ActiveX Calendar or Date Time picker controls instead of the
text boxes on the form; just remember that (a) in either case, you will need
a couple of lines of simple code to make them default e.g. the current date,
and (b) in the case of the DT picker, in particular, you will need to make
sure you "read" just the date part, dropping the time, so your query
criterion would become:

= Int(Forms!frmParameters!txtDateFrom) And <=

Int(Forms!frmParameters!txtDateTo)

HTH,
Nikos