Parsing parameters to a query as the recordsource of a Report
Lars Reimer Jensen
I'm trying to do an automatic printout of a report based on a query with
parameters. Actually, the query consists of three queries in cascade(nested)
and it愀 the first one, that needs the parameters, so I've just passed it
through the other queries. The report (and the queries) is working as
supposed, as long as you enter the parameters by hand. The obvious solution
would be an SQL-string as the recordsource of the report, but this is too
complex since it's based on nested queries.
I've tried several approaches without any luck. The Frontend is Access 2002
and the backend is Access 2000
This is the scenario:
A normal module:
Option Compare Database
Option Explicit
Public mrstReport As DAO.Recordset
Public Sub TestReport()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
'DoCmd.SetWarnings False
Set db = CurrentDb
Set qd = db.QueryDefs("qrySumKorrbudgetForbrugGraddage")
qd.Parameters("[Angiv Startdato (inklusiv)]") = #1/1/2002#
qd.Parameters("[Angiv Slutdato (inklusiv)]") = #1/4/2002#
Set mrstReport = qd.OpenRecordset
DoCmd.OpenReport "rptTest", acViewNormal
mrstReport.Close
Set mrstReport = Nothing
End Sub
The report "rptTest"
Private Sub Report_Open(Cancel%)
Me.RecordSource = mrstReport.Name
End Sub
========End of code
The above is based on this example:
http://www.mvps.org/access/reports/rpt0014.htm
Any ideas on another approach would be greatly welcome.
Thanks in advance and best regards,
Lars Jensen
Fletcher Arnold
news:3f1ce4b8$0$76155$edfa...@dread11.news.tele.dk...
> Hello,
>
> I'm trying to do an automatic printout of a report based on a query with
> parameters. Actually, the query consists of three queries in
cascade(nested)
Couple of ideas:
I am not sure whether this is as complicated as using an SQL string for the
report's recordsource, but it can be an elegant solution. Use the
WhereCondition when you open the report. Instead of using queries with
parameters, base your report on the all records but use a form to collect
the information about which records to print. For example, here is some
code that opens a report based on 2 dates
' *********************************
If Not IsNull(Me.txtDate1) Then
If Not IsNull(Me.txtDate2) Then
' Min and Max Date
strWhere = "PstDate BETWEEN #" & Format((Me.txtDate1), "mm/dd/yyyy")
& "#"
strWhere = strWhere & " AND #" & Format((Me.txtDate2), "mm/dd/yyyy")
& "#"
Else
' Min Date only
strWhere = "PstDate>#" & Format((Me.txtDate1 - 1), "mm/dd/yyyy") &
"#"
End If
Else
If Not IsNull(Me.txtDate2) Then
' Max Date Only
strWhere = "PstDate<#" & Format((Me.txtDate2 + 1), "mm/dd/yyyy") &
"#"
Else
' No Dates
strWhere = ""
End If
End If
DoCmd.Close
DoCmd.OpenReport "rptPostEntries", acViewPreview, , strWhere
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow
' *********************************
If you don't like that, it may be useful to know that new to Access 2002 is
the OpenArgs parameter of Docmd.OpenReport. You could use this to pass a
query name when the report is opened.
HTH
Fletcher
Lars Reimer Jensen
Your solution is interesting, but I think it would be too complex to alter
the queries to obtain all data. Since it sums up some data for a period of
time like a week, a month, a year, it would be too much data to sum up
several years if you're only interested in a single week.
Yes, I'm quite familiar with the OpenArgs parameter, and I use it a lot.
I've even tried it in this case, but it doesn't seem to do the job.
But thanks anyway. I really appreciate your reply.
Best regards,
Lars Jensen
"Fletcher Arnold" <fle...@home.com> skrev i en meddelelse
news:bfiqfd$51g$1...@titan.btinternet.com...
> "Lars Reimer Jensen" <l...@esenet.dk> wrote in message
> news:3f1ce4b8$0$76155$edfa...@dread11.news.tele.dk...
>
>
Richard Bernstein
query or use of a wherecondition -- criteria need to be supplied, generally
done with text boxes on a form.
Within the current structure the easiest solution is just change the
parameters in the query to the names of textboxes on a form, e.g.
Forms!frmParams!txtStart
Forms!frmParams!txtEnd
Then change the parameters code as indicated below, and read
http://www.mvps.org/access/queries/qry0013.htm
Richard Bernstein
"Lars Reimer Jensen" <l...@esenet.dk> wrote in
news:3f1ce4b8$0$76155$edfa...@dread11.news.tele.dk:
<lost of snip>
> The report (and the
> queries) is working as supposed, as long as you enter the parameters
> by hand. The obvious solution would be an SQL-string as the
> recordsource of the report, but this is too complex since it's based
> on nested queries.
> Set db = CurrentDb
> Set qd = db.QueryDefs("qrySumKorrbudgetForbrugGraddage")
> 'qd.Parameters("[Angiv Startdato (inklusiv)]") = #1/1/2002#
> 'qd.Parameters("[Angiv Slutdato (inklusiv)]") = #1/4/2002#
dim prm as parameter
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
TR
encounter performance issues.
Perhaps you can dump the results of the query into a temp table, and base
the report on that. So your Sub TestReport ends with a make table query (or
starts by clearing the temp table and ends with appending) then opens the
report. The report is based on the temp table that now contains only the
data of interest.
If this is a multiuser app its fairly easy to modify the procedure and its
queries to put the data into a table named <temptablename>_<CurrentUser>,
then set the reports recordsource in its open event.
"Lars Reimer Jensen" <l...@esenet.dk> wrote in message
news:3f1ce4b8$0$76155$edfa...@dread11.news.tele.dk...
Lars Reimer Jensen
TR, I think I'll stick to your solution. It seems to be an elegant solution
and yet easy to implement.
Haven't even thought of that.
Thanks again.
Best regards,
Lars Jensen.
"TR" <trNO_SP...@mindREMOVEspring.com> skrev i en meddelelse
news:bfkm2k$3ol$1...@slb5.atl.mindspring.net...