Print a series of reports based on a single parameter query

  • Thread starter Thread starter Bill R via AccessMonster.com
  • Start date Start date
B

Bill R via AccessMonster.com

I'm using the following code:

Private Sub cmdRunRpts_Click()
Dim strSQL As String
Dim rsIPTs As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim recDt As Date
Dim i As Integer

strSQL = "SELECT IPT FROM tblIPTs WHERE sectid>1 ORDER BY IPT"
Set db = CurrentDb
Set rsIPTs = db.OpenRecordset(strSQL)
Set qdf = db.QueryDefs("qryNewIPTRpt")
qdf.Parameters(0).Value = CDate(Forms!frmDatePopUp.Form.Controls("txtDate"))
For i = 1 To rsIPTs.RecordCount
qdf.Parameters(1).Value = rsIPTs.Fields("IPT")
DoCmd.OpenReport "rptDailyIPTMtg", acViewNormal
If Not rsIPTs.EOF Then rsIPTs.MoveNext
Next i

End Sub

To run a series of reports from a command button on a popup form that has a
txtbox in which the user is to type a date. The date of each report,
Parameters(0), should remain the same. The "IPT" value will change for each
iteration of the report. As currently written, the code executes properly,
but the OpenReport command causes the parameter dialog to open again and ask
for the value that I hoped had been provided in the Parameters(0) line.
I would prefer not to concatenate the SQL string for the underlying query as
it has 4 lines of criteria in the criteria grid and about 15 fields. It just
doesn't seem like the best solution. The query works fine running the report
manually and it would be great if I could set these 2 paramaters in code.

Thanks,

Bill
 
Bill:

A couple of suggestions. One is to directly reference parameter 0 in the
query. In the criteria section for that parameter, just use the control
reference. For example:

Forms!frmDatePopup!txtDate

This way the query can just directly reference the form textbox rather than
having to specify a parameter.

Two, why not use the fourth parameter of the OpenReport method to specify
parameter 1. For example,

Dim sWhere as String
..
..
..

Do Until rsIPTs.EOF
sWhere = "MyIPTFieldName=" & rsIPTs("IPT") 'This is for numeric
value, put in singles quotes if it is a text value
Docmd.OpenReport "rptDailyIPTMtg", acViewNormal, , sWhere
rsIPTs.MoveNext
Loop

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm using the following code:

Private Sub cmdRunRpts_Click()
Dim strSQL As String
Dim rsIPTs As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim recDt As Date
Dim i As Integer

strSQL = "SELECT IPT FROM tblIPTs WHERE sectid>1 ORDER BY IPT"
Set db = CurrentDb
Set rsIPTs = db.OpenRecordset(strSQL)
Set qdf = db.QueryDefs("qryNewIPTRpt")
qdf.Parameters(0).Value = CDate(Forms!frmDatePopUp.Form.Controls("txtDate"))
For i = 1 To rsIPTs.RecordCount
qdf.Parameters(1).Value = rsIPTs.Fields("IPT")
DoCmd.OpenReport "rptDailyIPTMtg", acViewNormal
If Not rsIPTs.EOF Then rsIPTs.MoveNext
Next i

End Sub

To run a series of reports from a command button on a popup form that has a
txtbox in which the user is to type a date. The date of each report,
Parameters(0), should remain the same. The "IPT" value will change for each
iteration of the report. As currently written, the code executes properly,
but the OpenReport command causes the parameter dialog to open again and ask
for the value that I hoped had been provided in the Parameters(0) line.
I would prefer not to concatenate the SQL string for the underlying query as
it has 4 lines of criteria in the criteria grid and about 15 fields. It just
doesn't seem like the best solution. The query works fine running the report
manually and it would be great if I could set these 2 paramaters in code.

Thanks,

Bill
 
David,

Thanks for your suggestions. The sleuth in me wanted to solve the qdf
parameters problem, but that will have to wait for another day. I am
implementing your solution, but I have one last question. My client wants the
reports to print automatically, especially the "each IPT" version where there
will be more than 10 reports. What is the method for automated printing,
assuming there is one?

Thanks,

Bill
 
Bill:

The acViewNormal parameter of the OpenReport method should automatically
print the report. So you should be good to go. The acViewPreview parameter
would, as its name implies, open the report in print preview mode.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


David,

Thanks for your suggestions. The sleuth in me wanted to solve the qdf
parameters problem, but that will have to wait for another day. I am
implementing your solution, but I have one last question. My client wants
the
reports to print automatically, especially the "each IPT" version where
there
will be more than 10 reports. What is the method for automated printing,
assuming there is one?

Thanks,

Bill
 
Mission accomplished. Thanks for your help.

David said:
Bill:

The acViewNormal parameter of the OpenReport method should automatically
print the report. So you should be good to go. The acViewPreview parameter
would, as its name implies, open the report in print preview mode.

David,

Thanks for your suggestions. The sleuth in me wanted to solve the qdf
parameters problem, but that will have to wait for another day. I am
implementing your solution, but I have one last question. My client wants
the
reports to print automatically, especially the "each IPT" version where
there
will be more than 10 reports. What is the method for automated printing,
assuming there is one?

Thanks,

Bill

David said:
Bill:
Click to expand...
[quoted text clipped - 18 lines]
rsIPTs.MoveNext
Loop
Click to expand...
Click to expand...
 
Back
Top