If you basing a query on a query, there not much you can do here.
On the other hand, while you "can" set the sql of a report in the on-open
event such as:
me.RecordSource = "name of query or sql goes here"
For what advantage would the above be in place of simply setting the nameof
the query the report is based on in the property sheet?
As general rule, if you have to change the "base" table for a report, then
that speaks of a design issue. If you looking to "change" the parameters,
then I included a re-post of mine on that issue after my signature.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
========
use the "where" clause, and make the reports sql *without* any
parameters..and you not need to change the params..
So, you can build a un-bound form (a un-bound form is a form that is NOT
attached to a table - these forms are typically designed for user interface
face stuff like prompts, print buttons etc).
The following screen shots are all un-bound forms, and they simply prompt
the user for information.
http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html
The above should give you some ideas
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. And, NO FORMS conditions are
placed in the query.
To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.
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
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. You 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
For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.
Further, it 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.
For a date range, we could put two calendar controls on the screen. The code
could be:
dim strWhere as string
dim strStartDate as string
dim strEndDate as string
strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"
strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate
docmd.openReport "InvoiceReport",acViewPreview,,strWhere
