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?
 
Ryan ChongConnect With a Mentor Commented:
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
 
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
 
shieldscoAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.