Printing Reports with VBA

[mikefitzge]

I made a button that when clicked will print all of the reports that
we will require that day, based on what order for that day is. All of
the reports are printed, but they are not in the same order as my
code. I put in a counter after each print command, thinking that a
slight delay might help keep them in order, but it has not. They come
out in a seemingly random order. Any suggestions? The code is below.


Private Sub cmdPrintReports_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim count As Integer

If vbYes = MsgBox("This will print all of the reports required by
today's RE's. Do you want to continue?", vbQuestion + vbYesNo) Then
Set rsta = currentdb.OpenRecordset("SELECT
tblDailyReport.dtmDateDailyReport, tblDailyReport.strCrew,
tblDailyReport.lngRevision, tblProject.strCCEProjectNumber,
tblDailyReport.lngCurrent FROM tblProject INNER JOIN tblDailyReport ON
tblProject.lngProjectID = tblDailyReport.lngProjectID WHERE
(((tblDailyReport.dtmDateDailyReport) = #" & Me.dtmDateDailyReport &
"#) And ((tblDailyReport.lngCurrent) = 1)) ORDER BY
tblDailyReport.strCrew, tblDailyReport.lngSite")
Do Until rsta.EOF = True
Forms!frmdataparameters!dtmDateReportFrom = rsta!
dtmDateDailyReport
Forms!frmdataparameters!strCCEProjectNumber = rsta!
strCCEProjectNumber
Forms!frmdataparameters!strCrew = rsta!strCrew
stDocName = "rptBituminousDailySchedule"
DoCmd.OpenReport stDocName, acNormal
Do Until count = 30000
count = count + 1
Loop
count = 0
'MsgBox ("Daily Schedule")

If rsta!strCrew = "ASP-SGR-01" Or rsta!strCrew = "ASP-
SGR-02" Or rsta!strCrew = "ASP-SGR-03" Or rsta!strCrew = "ASP-SGR-04"
Or rsta!strCrew = "ASP-SGR-05" Then
stDocName = "rptDailyConditioning"
ElseIf rsta!strCrew = "ASP-STG-01" Or rsta!strCrew = "ASP-
STG-02" Or rsta!strCrew = "ASP-STG-03" Or rsta!strCrew = "ASP-STG-04"
Or rsta!strCrew = "ASP-STG-05" Or rsta!strCrew = "ASP-ALG-01" Or rsta!
strCrew = "ASP-ALG-02" Or rsta!strCrew = "ASP-ALG-03" Or rsta!strCrew
= "ASP-ALG-04" Or rsta!strCrew = "ASP-ALG-05" Then
stDocName = "rptDailyGrinding"
ElseIf rsta!strCrew = "ASP-STP-01" Or rsta!strCrew = "ASP-
STP-02" Or rsta!strCrew = "ASP-STP-03" Or rsta!strCrew = "ASP-STP-04"
Or rsta!strCrew = "ASP-STP-05" Or rsta!strCrew = "ASP-ALP-01" Or rsta!
strCrew = "ASP-ALP-02" Or rsta!strCrew = "ASP-ALP-03" Or rsta!strCrew
= "ASP-ALP-04" Or rsta!strCrew = "ASP-ALP-05" Then
stDocName = "rptDailyPaving"
ElseIf rsta!strCrew = "ASP-AGR-01" Or rsta!strCrew = "ASP-
AGR-02" Or rsta!strCrew = "ASP-AGR-03" Or rsta!strCrew = "ASP-AGR-04"
Or rsta!strCrew = "ASP-AGR-05" Then
stDocName = "rptDailyGrading"
End If
DoCmd.OpenReport stDocName, acNormal
Do Until count = 30000
count = count + 1
Loop
count = 0
'MsgBox ("Cover Sheet")

stDocName = "rptHourLog"
DoCmd.OpenReport stDocName, acNormal
Do Until count = 30000
count = count + 1
Loop
count = 0
'MsgBox ("Hour Log")

stDocName = "rptCommentsBlank"
DoCmd.OpenReport stDocName, acNormal
Do Until count = 30000
count = count + 1
Loop
count = 0
'MsgBox ("Comments")

If rsta!strCrew = "ASP-STG-01" Or rsta!strCrew = "ASP-
STG-02" Or rsta!strCrew = "ASP-STG-03" Or rsta!strCrew = "ASP-STG-04"
Or rsta!strCrew = "ASP-STG-05" Or rsta!strCrew = "ASP-ALG-01" Or rsta!
strCrew = "ASP-ALG-02" Or rsta!strCrew = "ASP-ALG-03" Or rsta!strCrew
= "ASP-ALG-04" Or rsta!strCrew = "ASP-ALG-05" Then
stDocName = "rptTruckLogGrindingBlank"
ElseIf rsta!strCrew = "ASP-STP-01" Or rsta!strCrew = "ASP-
STP-02" Or rsta!strCrew = "ASP-STP-03" Or rsta!strCrew = "ASP-STP-04"
Or rsta!strCrew = "ASP-STP-05" Or rsta!strCrew = "ASP-ALP-01" Or rsta!
strCrew = "ASP-ALP-02" Or rsta!strCrew = "ASP-ALP-03" Or rsta!strCrew
= "ASP-ALP-04" Or rsta!strCrew = "ASP-ALP-05" Then
stDocName = "rptTruckLogPavingBlank"
ElseIf rsta!strCrew = "ASP-AGR-01" Or rsta!strCrew = "ASP-
AGR-02" Or rsta!strCrew = "ASP-AGR-03" Or rsta!strCrew = "ASP-AGR-04"
Or rsta!strCrew = "ASP-AGR-05" Then
stDocName = "rptTruckLogGradingBlank"
End If
DoCmd.OpenReport stDocName, acNormal
Do Until count = 30000
count = count + 1
Loop
count = 0
'MsgBox ("Truck Log")

If rsta!strCrew = "ASP-STP-01" Or rsta!strCrew = "ASP-
STP-02" Or rsta!strCrew = "ASP-STP-03" Or rsta!strCrew = "ASP-STP-04"
Or rsta!strCrew = "ASP-STP-05" Or rsta!strCrew = "ASP-ALP-01" Or rsta!
strCrew = "ASP-ALP-02" Or rsta!strCrew = "ASP-ALP-03" Or rsta!strCrew
= "ASP-ALP-04" Or rsta!strCrew = "ASP-ALP-05" Then
stDocName = "rptSiteCompletionForm"
DoCmd.OpenReport stDocName, acNormal
Do Until count = 30000
count = count + 1
Loop
count = 0
'MsgBox ("Site Completion Form")
End If


rsta.MoveNext
Loop
rsta.Close
Set rsta = Nothing
End If
End Sub

 

[Guest]

Mike:
As you have determined, just running an OpenReport simply spawns off the
report in a separate process and continues executing your next command.
Consider using a "DoEvents" command instead of your looping in the code.
DoEvents doesn't return control back to your program until all events in the
queue are completed.

 

[mikefitzge]

Mike:
As you have determined, just running an OpenReport simply spawns off the
report in a separate process and continues executing your next command.
Consider using a "DoEvents" command instead of your looping in the code.
DoEvents doesn't return control back to your program until all events in the
queue are completed.

Larry,

Thanks for the advice... I'm not sure I'm using DoEvents correctly, I
simply put "DoEvents" everywhere I had the Do Loop, and the reports
still come out of order. Is there something else I need to do to
ensure the code doesn't resume until the report is printed?

Thanks,
Mike