Reports - Passing Parameters to Source Query

[Robbie Baquiran]

Hi All:

I'm currently writing a script to run a report sourced from a query which
contains one parameter. The script will run a report based off a list of
names retrieved via DAO.

My question is, how would I pass the name value to the parameter of the
query through the report? I've tried
..querydefs("myquery").parameters(0)=daoRS(0) - but triggering the opening of
the report reveals that the parameter was not handed to the query correctly.

Any ideas?

Thanks in Advance!!

 

[Albert D.Kallal]

Don't use a parameter in the query.

All forms, and all reports have a parameter feature built in (it is called
the "where" clause).

Take alook at the folwing reprot screens:

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

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 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 contorls 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

In your case...you only have one paramter...so it should be easy....

 

[Robbie Baquiran]

Thanks Albert - I actually came to that very conclusion, or at least
something similar. I removed the paramter and added the conditions in the
"WhereCondition" of the docmd.openreport method.

Thanks for the input!!

Don't use a parameter in the query.

All forms, and all reports have a parameter feature built in (it is called
the "where" clause).

Take alook at the folwing reprot screens:

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

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 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 contorls 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

In your case...you only have one paramter...so it should be easy....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal