docmd printout access97 Results

Sponsored Links:

I am using docmd.printout to print pathology reports.The code is shown below.

My problem is that sometimes it prints the Main Menu but mostly it does the right thing.

The situation is that from the Main Menu a form with a list of reports waiting to be printed is displayed. Each report has a check box and the user ticks off the reports they want, then clicks the Print button.

The reason for using docmd.printout is that if the report is going to run to more than a specified number of pages it won't be printed. Instead it will be printed later on large paper. (I have cut out this code for the moment.)

The number of copies needed also has to be worked out, and this depends on a few things.

As far as I can tell, the problem occurs with the first report in a batch. The report opens in preview mode, and the number of pages calculated. Then the Main Menu prints out.

For the first report to be printed, there is a message box that is really telling the user to make sure that the right paper is in the printer. While I am writing this, it occurs to me that this might be the problem.
Does clicking "OK" on a message box while in print preview mode move the focus to another screen invisibly?
I have just removed the message box and done a couple more without problems, but the problem has always been intermittent.

Private Sub cmdPrint_Click()
Dim NoPages As Integer
Dim stDocName As String
Dim stcond As String
Dim CopiestoPrint As Integer
CopiestoPrint = DLookup("[NumCopies]", "tblPreferences")
Dim extraCopies As Integer
Dim PageLimit As Long
PageLimit = DLookup("[ReportPageLimit]", "tblPreferences")
Dim blFirst As Boolean
blFirst = True
Dim db As DAO.Database
Set db = CurrentDb()
Dim sql As String
sql = "SELECT tblReports.* , tblCases.CaseStatus FROM tblReports INNER JOIN tblCases _
ON tblCases.RefID = tblReports.RefID where ((Complete = True) _
and isNull(ReportPrinted) and ([Select]=true))"
DoCmd.RunCommand acCmdSaveRecord
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset(sql, dbOpenDynaset)
If Not rst.EOF Then
Do While Not rst.EOF
extraCopies = Nz(DCount("ProviderNo", "tblcopiesto", "[ReferralNo]= " & rst![RefID]), 0)
CopiestoPrint = CopiestoPrint + extraCopies
stcond = "[ReportId] = " & rst!reportID
If rst!Category = "H" Then
stDocName = "rptHistotestreport"
stDocName = "rptMycotestreport"
End If
DoCmd.OpenReport stDocName, acViewPreview, , stcond
If Screen.ActiveReport.Label95.Visible = True Then
CopiestoPrint = CopiestoPrint + 1
End If
If Screen.ActiveReport.Label98.Visible = True Then
CopiestoPrint = CopiestoPrint + 1
End If
NoPages = Screen.ActiveReport.Pages
If NoPages