Access Check for No Records

I using the following code to sent Excel worksheets to the printer and would like to add code to test if recordSet is empty or null and print a message "No worksheets to print" Thanks


Dim myrs As DAO.Recordset
Dim dbs As Database
Dim strSQL As String, strFile As String
Dim wsh As Object, appExcel As Object, myWorkbook As Object
Set dbs = CurrentDb


On Error GoTo Err_Nex_Click


strSQL = "SELECT * from qryEmployeePrint"
Set myrs = dbs.OpenRecordset(strSQL)



If MsgBox("Do you want to send all reports to your default Printer? ", vbYesNo, "OMHA") = vbYes Then

DoCmd.OpenForm "frmPleaseWaitPrinting"

Do While Not myrs.EOF

 
strFile = Replace(Mid(myrs.Fields("Location"), InStr(myrs.Fields("Location"), "#") + 1), "#", "")


    'strFile = myRS.Fields("Location")
   
 
   
    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open(strFile)
    For Each wsh In myWorkbook.Worksheets
        If wsh.Visible = xlSheetVisible Then
            wsh.PrintOut
        End If
    Next wsh
myWorkbook.Close

    Set appExcel = Nothing
    Set myWorkbook = Nothing

    myrs.MoveNext
Loop

End If

DoCmd.Close acForm, "frmPleaseWaitPrinting"

Exit_Nex_Click:

DoCmd.Close acForm, "frmPleaseWaitPrinting"

  Exit Sub


Err_Nex_Click:
MsgBox Err.Description, vbExclamation, Err.Number
 

Resume Exit_Nex_Click
shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
After
Set myrs = dbs.OpenRecordset(strSQL)

check
if myrs.EOF then ' no records
0
shieldscoAuthor Commented:
Nothing happens

[Private Sub NavigationButton29_Click()
Dim myRS As DAO.Recordset
Dim dbs As Database
Dim strSQL As String, strFile As String
Dim wsh As Object, appExcel As Object, myWorkbook As Object
Set dbs = CurrentDb


On Error GoTo Err_Nex_Click


strSQL = "SELECT * from qryEmployeePrint"
Set myRS = dbs.OpenRecordset(strSQL)
If myRS.EOF Then ' no records
MsgBox "No Records"

If MsgBox("Do you want to send all reports to your default Printer? ", vbYesNo, "OMHA") = vbYes Then

DoCmd.OpenForm "frmPleaseWaitPrinting"
Do While Not myRS.EOF

 
strFile = Replace(Mid(myRS.Fields("Location"), InStr(myRS.Fields("Location"), "#") + 1), "#", "")


    'strFile = myRS.Fields("Location")
   
   
   
   
   
    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open(strFile)
    For Each wsh In myWorkbook.Worksheets
        If wsh.Visible = xlSheetVisible Then
            wsh.PrintOut
        End If
    Next wsh
myWorkbook.Close

    Set appExcel = Nothing
    Set myWorkbook = Nothing

    myRS.MoveNext
Loop

End If

DoCmd.Close acForm, "frmPleaseWaitPrinting"

Exit_Nex_Click:

DoCmd.Close acForm, "frmPleaseWaitPrinting"

  Exit Sub


Err_Nex_Click:
MsgBox Err.Description, vbExclamation, Err.Number
 

Resume Exit_Nex_Click

End If

]
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
think that you should handle your codes like these:

option 1:

Private Sub Test()
    On Error GoTo Err_Nex_Click
    
    Dim myRS As DAO.Recordset
    Dim dbs As Database
    Set dbs = CurrentDb
    strSQL = "SELECT * from Patient_Progress_Notes"
    Set myRS = dbs.OpenRecordset(strSQL)
    
    If myRS.EOF Then ' no records
        MsgBox "No Records"
        
        myRS.Close
        Set myRS = Nothing
        dbs.Close
        Set dbs = Nothing
    Else
        If MsgBox("Do you want to send all reports to your default Printer? ", vbYesNo, "OMHA") = vbYes Then
            DoCmd.OpenForm "frmPleaseWaitPrinting"
            Do While Not myRS.EOF
                strFile = Replace(Mid(myRS.Fields("Location"), InStr(myRS.Fields("Location"), "#") + 1), "#", "")
                
                'strFile = myRS.Fields("Location")
                   
                Set appExcel = CreateObject("Excel.Application")
                Set myWorkbook = appExcel.Workbooks.Open(strFile)
                For Each wsh In myWorkbook.Worksheets
                    If wsh.Visible = xlSheetVisible Then
                        wsh.PrintOut
                    End If
                Next wsh
                myWorkbook.Close
            
                Set appExcel = Nothing
                Set myWorkbook = Nothing
            
                myRS.MoveNext
            Loop
            
            DoCmd.Close acForm, "frmPleaseWaitPrinting"
        End If
    End If
    
    Exit Sub
    
Err_Nex_Click:
    MsgBox Err.Description, vbExclamation, Err.Number

End Sub

Open in new window


option 2:

Private Sub Test2()
    On Error GoTo Err_Nex_Click
    
    Dim myRS As DAO.Recordset
    Dim dbs As Database
    Set dbs = CurrentDb
    strSQL = "SELECT * from Patient_Progress_Notes"
    Set myRS = dbs.OpenRecordset(strSQL)
    
    If myRS.EOF Then ' no records
        MsgBox "No Records"
        
        myRS.Close
        Set myRS = Nothing
        dbs.Close
        Set dbs = Nothing
        
        Exit Sub
    End If
    
    If MsgBox("Do you want to send all reports to your default Printer? ", vbYesNo, "OMHA") = vbYes Then
        DoCmd.OpenForm "frmPleaseWaitPrinting"
        Do While Not myRS.EOF
            strFile = Replace(Mid(myRS.Fields("Location"), InStr(myRS.Fields("Location"), "#") + 1), "#", "")
            
            'strFile = myRS.Fields("Location")
               
            Set appExcel = CreateObject("Excel.Application")
            Set myWorkbook = appExcel.Workbooks.Open(strFile)
            For Each wsh In myWorkbook.Worksheets
                If wsh.Visible = xlSheetVisible Then
                    wsh.PrintOut
                End If
            Next wsh
            myWorkbook.Close
        
            Set appExcel = Nothing
            Set myWorkbook = Nothing
        
            myRS.MoveNext
        Loop
        
        DoCmd.Close acForm, "frmPleaseWaitPrinting"
    End If
    
    Exit Sub
    
Err_Nex_Click:
    MsgBox Err.Description, vbExclamation, Err.Number

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.