Evaluate query results for printing reports (A97/SR2)

Topic

New Reply

I have a procedure that does a batch print of a list of workorder numbers that are entered by the user from a form. It is a Do Loop that processes each workorder number, one at a time, prints it and then deletes the workorder number and processes the next workorder number until the list of numbers is empty. For each workorder number processed, there is the possibility of eight different reports being generated, depending on the results of eight queries. Typically there will be only one to five of the eight possible reports being generated for a given workorder number.

Each report has a query for the control source. Am currently using the ‘On No Data’ event of each report with a Cancel = True.

Within the Do Loop I would like to make an (If-Then-Else) or a (Case) statement that will evaluate the result of each of the 8 queries. If the query is opened and there are no values returned, then proceed to the next query. If the query is opened and there is a value returned, then have it open the report and print. Can someone help on this?

Reason for this is to: (1) avoid the Error 2501 and (2) prevent all the flashing on the screen of the many reports being sent to the printer (10 workorders X eight reports = 80 flashes on the screen for possibly eight reports)

‘************************************************************
‘* This is the the “Batch Print” event procedure for printing
‘* many logentries one at a time.
‘************************************************************

Private Sub Command3_Click()

MsgBox “Make sure there is logbook paper in the printer and the the printer is ” & _
“””ON””.”, vbExclamation + vbOKOnly + vbDefaultButton1, “Is The Printer Ready?”

DoCmd.SetWarnings False

‘ first – clean out the WOnumber table
DoCmd.OpenQuery “qryDelWOnumber”

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(“tblBatchWO”)
Do
If rs.RecordCount = 0 Then
Exit Do
Else
DoCmd.SetWarnings False
‘take the first wo number and append it to the
‘tblWOnumber table -> also append it to tblWOnumberPrint
‘because I had to remove the ‘key’ from tblWOnumber – (it
‘messed up woopen) – two keys reqd to delete both records
‘with qryDel1fromBatchAndWO
DoCmd.OpenQuery “qryApnd1toWOnum”
DoCmd.OpenQuery “qryApnd1toWOnumPrnt”

‘now print logbook entries
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll100”
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll200”
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll300”
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll400”
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll500”
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll600”
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll700”
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport “rptLogbookAll800”
If Err = 2501 Then Err.Clear

‘now delete the w.o. number just printed
‘on batch and on tblWOnumberPrint
‘and then delete tblWOnumber
DoCmd.OpenQuery “qryDel1fromBatchAndWO”
DoCmd.OpenQuery “qryDelWOnumber”
‘now requery the subform
DoCmd.Requery “frmPrintWOsubform”

End If
Loop While rs.RecordCount > 0

Set db = Nothing
Set rs = Nothing

DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “MainMenuForm”
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Reply | Quote

Replies

What you could do is open the query (recordsource of the report) in a recordset and test for any records, in that way you don’t need to check for 2501 and get the flicker.

Set rsa = dbs.OpenRecordset(“qry_rptLogbookAll100”)
If Not rsa.Eof then
DoCmd.OpenReport “rptLogbookAll100”
End If
Set rsa = Nothing

Don’t forget to change the name of the query to yours.

HTH
Pat

Reply | Quote

Thank you Pat – that worked like a charm!!! You are a hero.

Reply | Quote