Passing a Where cause to a report

[H. Martins]

When and while in a form I start a report, the report opens, and it's
query reads the content of a listbox so that query's where clause
works as expected. It works OK, no problem.

In order to use the same report with other forms, I need it to work
the other way around: to pass the where clause to the report.

The problem is that the report has it's own query and I don't know how
to combine both, ie, how to pass a where parameter to the report's
query.

Could I have some help, please?

Thanks
H. Martins

 

[Jeff Boyce]

You could make the query more generic, removing the WHERE clause that is
specific to that one report.

Then, in the form, you could use the

DoCmd.OpenReport

syntax to specify a WHERE clause.

If you then give the user a way to select from among the many reports they
could run (a combobox?), you can alter both the name of the report and the
WHERE clause in your code to open the report you want (same basic underlying
query) with the specific WHERE needed for the specific report chosen.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Albert D. Kallal]

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions or
[enter parms] are placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

after the above, I could now open the reprot such as:

docmd.OpenReprot "reprotName",acViewPreview,,strWhere

However, we could also have MANY MORE conditons on the form...so, we could
continue the above code like:


' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. We add an " and " part to the
strWhere. This means we can add as "many" more conditions you want. Lets say
we have a check box to only include Special Customers. We can add to our
very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

Now, for each combo and control we add to the nice report screen DOES force
you to write code. However, this code is not a lot more messy then adding
those prompts/expresisons to the query builder. And, this way each query is
nice and clean, and free of a bunch of HIGHLY un-maintainable forms!
expressions in the sql.

So, this approach means you can re-use the same query for different reports,
and have no worries about some form that is supposed to be open. So, a tiny
bit more code eliminates the messy query problem..

For me, this is very worth while trade.