Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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:
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?
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;
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think you need something like this :
DoCmd.OpenReport NameOfYourReport, acViewPreview, , Criteria
DoCmd.SelectObject acReport, NameOfYourReport
DoCmd.PrintOut , , , , NoOfCopies
DoCmd.Close acReport, NameOfYourReport
try this
Dim j As Integer, rptName As String
For j = 0 To CurrentProject.AllReports.
rptName = CurrentProject.AllReports.
DoCmd.OpenReport rptName, acViewPreview
Next
Dim j As Integer, rptName As String
For j = 0 To CurrentProject.AllReports.
rptName = CurrentProject.AllReports.
DoCmd.OpenReport rptName, acViewPreview
Next
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]![frmSearchForClien
Change the query to select all records if [Forms]![frmSearchForClien
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;
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
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
So since I can't have criteria for txtCode this is my record source for the report.
So, how do I loop through the reports to print them one after another?
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;
So, how do I loop through the reports to print them one after another?
@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
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
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"
JeffCoachman
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).
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
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
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"
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.
docmd.OpenReport strReportName, acViewNormal, , "Code= '" & rstCodes!Code & "'"
AcViewNormal is the default (direct to the printer) so you can omit it.
...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
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
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"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial