Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Evaluate query results for printing reports (A97/SR2)

    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

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Evaluate query results for printing reports (A97/SR2)

    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

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Evaluate query results for printing reports (A97/SR2)

    Thank you Pat - that worked like a charm!!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> You are a hero.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •