How cycle through reports and print them

I have a report that has a query as it's record source.  The SQL of the query looks like:

SELECT tblClientSignAudit.ClientName, tblClientSignAudit.ClientAddress, tblClientSignAuditDetail.Location, tblClientSignAuditDetail.SiteImageFolder, tblTypeLU.Code, [SizeWidth] & "x" & [SizeHeight] & "x" & [SizeDepth] AS [Size], tblCompositeLU.Description AS Composition, tblConditionLU.Description AS Condition, tblClientSignAuditDetail.Electric, tblClientSignAuditDetail.Message, tblClientSignAuditDetail.Qty, tblClientSignAuditDetail.ClientAuditID, tblClientSignAuditDetail.TagNumber, tblTypeLU.Description, tblAuditTypesLU.Description, tblClientSignAudit.LogoFile
FROM (tblClientSignAudit LEFT JOIN tblAuditTypesLU ON tblClientSignAudit.SheetDescription = tblAuditTypesLU.ID) INNER JOIN (((tblClientSignAuditDetail LEFT JOIN tblTypeLU ON tblClientSignAuditDetail.Type = tblTypeLU.ID) LEFT JOIN tblCompositeLU ON tblClientSignAuditDetail.Composite = tblCompositeLU.ID) LEFT JOIN tblConditionLU ON tblClientSignAuditDetail.Condition = tblConditionLU.ID) ON tblClientSignAudit.ID = tblClientSignAuditDetail.ClientAuditID
WHERE (((tblTypeLU.Code)=[Forms]![frmSearchForClientAuditForReport]![txtCode]) AND ((tblClientSignAuditDetail.ClientAuditID)=[Forms]![frmSearchForClientAuditForReport]![cboSearch]))
ORDER BY tblTypeLU.Code;

Open in new window


This is working fine.  But now I want the user to be able to print all of the reports, one after another, without selected the "txtCode" from a combo-box.  Just cycle through them and print them.  In some cases there may not be records with a particular code.  In that case, just skip over that type and go on to the next.

How can I do this with VBA code?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I think you need something like this :
 DoCmd.OpenReport NameOfYourReport, acViewPreview, , Criteria
                    DoCmd.SelectObject acReport, NameOfYourReport
                    DoCmd.PrintOut , , , , NoOfCopies
                    DoCmd.Close acReport, NameOfYourReport

Open in new window

SteveL13Author Commented:
I don't see the looping part of the code.  ???
Rey Obrero (Capricorn1)Commented:
try this

Dim j As Integer, rptName As String

For j = 0 To CurrentProject.AllReports.Count - 1
    rptName = CurrentProject.AllReports.Item(j).Name
   
    DoCmd.OpenReport rptName, acViewPreview
   
Next

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
Modify the report to do a page break on txtCode if it doesn't do one already.

Change the query to select all records if [Forms]![frmSearchForClientAuditForReport]![txtCode] is empty.
SELECT tblClientSignAudit.ClientName, tblClientSignAudit.ClientAddress, tblClientSignAuditDetail.Location, tblClientSignAuditDetail.SiteImageFolder, tblTypeLU.Code, [SizeWidth] & "x" & [SizeHeight] & "x" & [SizeDepth] AS [Size], tblCompositeLU.Description AS Composition, tblConditionLU.Description AS Condition, tblClientSignAuditDetail.Electric, tblClientSignAuditDetail.Message, tblClientSignAuditDetail.Qty, tblClientSignAuditDetail.ClientAuditID, tblClientSignAuditDetail.TagNumber, tblTypeLU.Description, tblAuditTypesLU.Description, tblClientSignAudit.LogoFile
FROM (tblClientSignAudit LEFT JOIN tblAuditTypesLU ON tblClientSignAudit.SheetDescription = tblAuditTypesLU.ID) INNER JOIN (((tblClientSignAuditDetail LEFT JOIN tblTypeLU ON tblClientSignAuditDetail.Type = tblTypeLU.ID) LEFT JOIN tblCompositeLU ON tblClientSignAuditDetail.Composite = tblCompositeLU.ID) LEFT JOIN tblConditionLU ON tblClientSignAuditDetail.Condition = tblConditionLU.ID) ON tblClientSignAudit.ID = tblClientSignAuditDetail.ClientAuditID
WHERE (tblTypeLU.Code = [Forms]![frmSearchForClientAuditForReport]![txtCode] OR [Forms]![frmSearchForClientAuditForReport]![txtCode] Is Null) AND tblClientSignAuditDetail.ClientAuditID=[Forms]![frmSearchForClientAuditForReport]![cboSearch]
ORDER BY tblTypeLU.Code;

Open in new window

SteveL13Author Commented:
Pat,

The page numbering has to start with "1" for each report.  Therefore the code has to somehow loop through all codes and run each report one after another.  Am I making any sense at all?

--Steve
SteveL13Author Commented:
So since I can't have criteria for txtCode this is my record source for the report.

SELECT tblClientSignAudit.ClientName, tblClientSignAudit.ClientAddress, tblClientSignAuditDetail.Location, tblClientSignAuditDetail.SiteImageFolder, tblTypeLU.Code, [SizeWidth] & "x" & [SizeHeight] & "x" & [SizeDepth] AS [Size], tblCompositeLU.Description AS Composition, tblConditionLU.Description AS Condition, tblClientSignAuditDetail.Electric, tblClientSignAuditDetail.Message, tblClientSignAuditDetail.Qty, tblClientSignAuditDetail.ClientAuditID, tblClientSignAuditDetail.TagNumber, tblTypeLU.Description, tblAuditTypesLU.Description, tblClientSignAudit.LogoFile
FROM (tblClientSignAudit LEFT JOIN tblAuditTypesLU ON tblClientSignAudit.SheetDescription = tblAuditTypesLU.ID) INNER JOIN (((tblClientSignAuditDetail LEFT JOIN tblTypeLU ON tblClientSignAuditDetail.Type = tblTypeLU.ID) LEFT JOIN tblCompositeLU ON tblClientSignAuditDetail.Composite = tblCompositeLU.ID) LEFT JOIN tblConditionLU ON tblClientSignAuditDetail.Condition = tblConditionLU.ID) ON tblClientSignAudit.ID = tblClientSignAuditDetail.ClientAuditID
WHERE (((tblClientSignAuditDetail.ClientAuditID)=[Forms]![frmSearchForClientAuditForReport]![cboSearch]))
ORDER BY tblTypeLU.Code;

Open in new window


So, how do I loop through the reports to print them one after another?
Rey Obrero (Capricorn1)Commented:
@steve

seen my post at http:#a40976157 ?

to send the reports to the printer,
change
 DoCmd.OpenReport rptName, acViewPreview

with

 DoCmd.OpenReport rptName, acViewNormal
or simply

 DoCmd.OpenReport rptName
Jeffrey CoachmanMIS LiasonCommented:
print all of the reports, one after another, without selected the "txtCode" from a combo-box.
Loop the txtCode values
I will presume that they are stored in a table (ex.: txtCodes)
I will also presume that the codes are all numeric

Dim strReportName As String
Dim rstCodes As DAO.Recordset
strReportName = "YourReportName"
Set rstCodes = CurrentDb.OpenRecordset("SELECT Code FROM tblCodes")
rstCodes.MoveFirst
Do Until rstCodes.EOF
    'Test
    'MsgBox rstCodes!Code
    DoCmd.OpenReport strReportName, , , "Code=" & rstCodes!Code, acHidden
    DoCmd.Close acReport, strReportName
    rstCodes.MoveNext
Loop
MsgBox "Done"

Open in new window


JeffCoachman
PatHartmanCommented:
As long as you don't need page x of y, you can initialize page in the Format event of the header or footer (whichever holds the page number).
Jeffrey CoachmanMIS LiasonCommented:
As far I can tell..., the SQL is mostly irrelevant here.

From what I can tell, you need to loop the codes and print a report for each Code.
Correct?
This is what my code does.

If this is not what you need then you will have to be a bit clearer as to what you have, and the exact results you are expecting...

My guess is that after a fuller explanation, ...any of us can get you sorted...

JeffCoachman
SteveL13Author Commented:
Jeffrey:  This is very close.  But codes are text.  How would this change:

Dim strReportName As String
Dim rstCodes As DAO.Recordset
strReportName = "YourReportName"
Set rstCodes = CurrentDb.OpenRecordset("SELECT Code FROM tblCodes")
rstCodes.MoveFirst
Do Until rstCodes.EOF
    'Test
    'MsgBox rstCodes!Code
    DoCmd.OpenReport strReportName, , , "Code=" & rstCodes!Code, acHidden
    DoCmd.Close acReport, strReportName
    rstCodes.MoveNext
Loop
MsgBox "Done"

Open in new window

PatHartmanCommented:
I'm not sure why acHidden is being used and since the report is going directly to print, there is no need to close it.

docmd.OpenReport strReportName, acViewNormal, , "Code= '" & rstCodes!Code & "'"

AcViewNormal is the default (direct to the printer) so you can omit it.
Jeffrey CoachmanMIS LiasonCommented:
...just old lazy syntax Pat....
Just threw it together quickly as an example...
The code should work as posted, ...I just wanted to see if this is the loop logic the OP wanted...
...Then the code could be refined as needed.

Jeff
SteveL13Author Commented:
Here is what I have so far.  But it is only printing the 1st report.

Dim strReportName As String
Dim rstCodes As DAO.Recordset
    strReportName = "Report2-Original"
    Set rstCodes = CurrentDb.OpenRecordset("SELECT Code FROM tblTypeLU")
    rstCodes.MoveFirst
    Do Until rstCodes.EOF
       
        'To go directly to printer...
        DoCmd.OpenReport strReportName, acViewNormal, , "Code= '" & rstCodes!Code & "'"

        DoCmd.Close acReport, strReportName
        rstCodes.MoveNext
    Loop
    
    MsgBox "Done"

Open in new window

Rey Obrero (Capricorn1)Commented:
@steve

did you ever look at the codes i posted?
Jeffrey CoachmanMIS LiasonCommented:
Yes,
Steve, please first reply to Rey
PatHartmanCommented:
Steve,
Put a stop in the code and step through the loop.  Examine the  contents of rstCodes!Code

Remove the line that closes the report.  It isn't necessary and may be causing a problem since at the time it runs, the report isn't open.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.