Generating Multiple reports by looping though a query

[TomYC]

Sorry for the long subject line--
I've not been able to locate a thread that addresses this, though it must exist!
I have a form (f_rpt) that I use to define a query, (q_UnitHasData_UnitOnly from which reports are generated (date range, department, etc.). currently I am generating individual department reports by clicking on the Unit that appears in a listbox on my form (lstUnitHasData), and then I have code to name & save as Snapshot, for emailing, etc.
Now I want to automate the entire process of generating and emailing, but for now I'd be satisfied with:
loop through each Unit in query q_UnitHasData_UnitOnly (SELECT DISTINCT t_wr_main.unit FROM etc.), then output the snapshot file, capturing the Unit name such that it can be part of the snapshot file name (e.g 2South_042709.snp).
I'm sure this can be a loop through recordset in a module, but I'm not certain how to begin!
Tnx,
Tom

 

[kjv1611]

See if this gets you started:

Private Sub ClickNRun()
  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("q_UnitHasData_UnitOnly")

  Do While Not rs.EOF
[GREEN]    'Spit out the snapshot for rs.Fields("ReportName")
    'Of course, substitute the correct field for what specifies the report... and use the correct code for generating the report..[/GREEN]

    rs.MoveNext
  Loop

[GREEN]  'Close out objects you created and used earlier[/GREEN]
  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing
End Sub

Let us know whether that gets you started, and what/if any questions you have beyond that...

Then later, you could use a different method which loops through the items in your list box, or whatever, builds your query via SQL code in VBA, and spits out the reports that way... if that ends up being any different..

--

"If to err is human, then I must be some kind of human!" -Me

 

[TomYC]

Thanks for getting me started, kjv1611--
I probably still have a long way to go...
but here is some of my code so far.
What isn't obvious here is that the report I want to generate:
stDocName = "r_detail_unit_SendAuto"
is a report based upon a query which currently gets one crucial piece of information ("Unit") from my form, specifically a listbox. This is also something I want to change, as currently I need to have my pointer on one line in that listbox, to tell the query "which Unit?" -- thus at present I have stUnit picking up Unit from a listbox lstUnitHasData. I need to change this here!!

Private Sub cmdLoopReportsSend_Click()
On Error GoTo Err_cmdLoopReportsSend_Click

'script from Tek-Tips1557165

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("q_UnitHasData_UnitOnly")
Do While Not rs.EOF
'Spit out the snapshot for rs.Fields("ReportName")
'Of course, substitute the correct field for what specifies the report... and use the correct code for generating the report..
Dim stDocName As String

stDocName = "r_detail_unit_SendAuto"

Dim stPrintFileName As String
Dim stDate As String
Dim stUnit As String
stUnit = rs.Fields("Unit")
'Is that correct?

stDate = Format(Forms![f_rpt]![cbFromDate], "mmddyy")
stPrintFileName = "Patient Safety Rounds_" & stUnit & "_" & stDate

DoCmd.OutputTo acReport, stDocName, "Snapshot Format", "S:\Walkround\Report\" & stPrintFileName & ".snp", False
'back to the loop and end:
rs.MoveNext
Loop

'Close out objects you created and used earlier
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit_cmdLoopReportsSend_Click:
Exit Sub

Err_cmdLoopReportsSend_Click:
MsgBox Err.Description
Resume Exit_cmdLoopReportsSend_Click

End Sub

Right now when I try to run this code, I get an error message saying that Jet cannot find that q_UnitHasData_UnitOnly query, even though it is there!
Tnx,T

 

[kjv1611]

Right now when I try to run this code, I get an error message saying that Jet cannot find that q_UnitHasData_UnitOnly query, even though it is there!

Make sure you double, triple, even quadruple check your query name to what you have in the code, to be absolutely sure the name is correct.

Otherwise, where in the code are you getting the error?

--

"If to err is human, then I must be some kind of human!" -Me

 

[TomYC]

You're right, one must double check query names in code!
Here is what I have now, one letter changed:
Private Sub cmdLoopReport_Click()
On Error GoTo Err_cmdLoopReport_Click

Dim stDocName As String

'script from Tek-Tips1557165

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qr_UnitHasData_UnitOnly")
MsgBox "What now?"
Do While Not rs.EOF
'Spit out the snapshot for rs.Fields("ReportName")
'Of course, substitute the correct field for what specifies the report... and use the correct code for generating the report..


stDocName = "r_detail_unit_SendAuto"

Dim stPrintFileName As String
Dim stDate As String
Dim stUnit As String
stUnit = rs.Fields("r_detail_unit_SendAuto")

'Is that correct?

stDate = Format(Forms![f_rpt]![cbFromDate], "mmddyy")
stPrintFileName = "Patient Safety Rounds_" & stUnit & "_" & stDate

DoCmd.OutputTo acReport, stDocName, "Snapshot Format", "S:\Walkround\Report\" & stPrintFileName & ".snp", False
'back to the loop and end:
rs.MoveNext
Loop

'Close out objects you created and used earlier
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit_cmdLoopReport_Click:
Exit Sub

Err_cmdLoopReport_Click:
MsgBox Err.Description
Resume Exit_cmdLoopReport_Click

End Sub

The line: stUnit = rs.Fields("r_detail_unit_SendAuto")
is just a guess, and it must be incorrect, as I'm getting an error message 'Insufficient parameters; was expecting two' --and as I wrote earlier, the query that is associated with the report (qr_detail_SendAuto, for the r_detail_unit_SendAuto report)currently has an item in a listbox as part of this query, and that must somehow now refer to the same field via the rs.Fields(). In other words, as the loop steps through each element in the single field query (qr_UnitHasData_UnitOnly), that element (Unit) must be used both in the query that governs the report (qr_detail_SendAuto) and as a string (stUnit) that gives the report, in part, its name on output. This is more difficult to describe than to think!

 

[kjv1611]

Yeah, I know the feeling!

--

"If to err is human, then I must be some kind of human!" -Me

 

[TomYC]

I think I still haven't found this answer:
how can a field in a recordset function as both an element in a query, and as a text string in a file name, as I have indicated above??

 

[PHV]

Replace this:
Set rs = db.OpenRecordset("qr_UnitHasData_UnitOnly")
with this:
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set qdf = db.QueryDefs("qr_UnitHasData_UnitOnly")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886