Full Version: Code produces multiple copies of a report and I want only 1
UtterAccess Forums > Microsoft® Access > Access Forms
This code is behind an on-click event on my form to print reports. I have multiple types of reports that can be produced based on the information in the items that are selected in the list on the form. I have one problem that I just can't solve. I tried adding a print loop counter to solve the problem but it's not working. There's a portion of code that loops through and creates an SQL statement to create the record source for a subreport (works great!). Once that gets created then I want to print the report and only 1 copy. What I get is the number of items that were selected plus 1 as the number of copies.
need the code at this point in my process so that this cover letter comes out in the proper order on the printer.
Please help....I've had another person in my office look at it and he can't figure out what I can do either.
Set frm = Forms!frmPrintingInspectionReports
Set ctl = frm!ReprintDocNumber
For Each varItm In ctl.ItemsSelected
stDocNumber = Forms!frmPrintingInspectionReports!ReprintDocNumber.Column(1, varItm)
mfilter = "DocID = '" & Me.ReprintDocNumber.Column(1, varItm) & "' and RecID = " & Me.ReprintDocNumber.Column(4, varItm)
mfilter2 = "DocID = '" & Me.ReprintDocNumber.Column(1, varItm) & "'"
For Each varItem In ctl.ItemsSelected
stFormID = stFormID & "(tblInspectForms.FormID)=" & Forms!frmPrintingInspectionReports!ReprintDocNumber.Column(2, varItem) & " Or "
Next varItem
If PrintCoverPage = "yes" And Printloop = 1 Then
stFormID = Left(stFormID, Len(stFormID) - 4)
strSQL = "SELECT tblFormsUsed.MainID, tblFormsUsed.NoOfUnits, tblInspectForms.FormID, tblInspectForms.InspectForm" & _
" FROM tblInspectForms INNER JOIN tblFormsUsed ON tblInspectForms.FormID = tblFormsUsed.FormID" & _
" Where ((" & stFormID & "));"
Dim rpt As Report
DoCmd.OpenReport "subrptFormsUsed", acViewDesign
Set rpt = Reports(subrptFormsUsed)
rpt.RecordSource = strSQL
DoCmd.Save acReport, "subrptFormsUsed"
DoCmd.Close acReport, "subrptFormsUsed"
Set rpt = Nothing
DoCmd.OpenReport stDocName, acViewPreview, , mfilter2
'DoCmd.Close acReport, stDocName, acSaveNo
Printloop = Printloop + 1
End If
If ctl.Column(2, varItm) = 1 Then 'Crane System
If PrintCoverPage = "no" Then
MsgBox "printing individual cover page 1"
DoCmd.OpenReport stDocName59, acViewPreview, , mfilter
DoCmd.Close acReport, stDocName59, acSaveNo
End If

DoCmd.OpenReport stDocName3, acViewPreview, , mfilter
DoCmd.PrintOut , , , , stCopies, 1
DoCmd.Close acReport, stDocName3, acSaveNo
'DoCmd.OpenReport stDocName4, acViewPreview, , mfilter
'DoCmd.PrintOut , , , , stCopies, 1
'DoCmd.Close acReport, stDocName4, acSaveNo
....multiple if statements....
next varitm
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.