Empty Recordset Export

[jd.willis]

I have a query that is ran daily and the results of the query are exported to excel.

I'm working on automating this. However this query does not always have results I have code put together to export the query when the results are present into the new file. What I would like to do is modify the code somehow if the recordset is empty then in the new excel spreadsheet I would like to have "NO DATA FOR THIS DATE" put into a specific cell.

Thank you in advance for any and all help!

CODE:

Private Sub TMCeDRO5002_Click()
 On Error Resume Next
    Dim sCriteria As String
    Dim db As Database
    Dim rst As Recordset
    Dim objApp As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim strTemplatePath As String
    Dim sOutput As String
    
    sCriteria = " 1 = 1 "
    
    If COID <> "" Then
        sCriteria = sCriteria & " AND [10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)].COID = """ & COID & """"
    End If
    
    If RPT_Date <> "" Then
        sCriteria = sCriteria & " AND [10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)].RPT_Date = """ & RPT_Date & """"
    End If
        
    If "PT_NUM" <> "" Then
        sCriteria = sCriteria & " AND [10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)].pt_num = """ & "pt_num" & """"
    End If
    
    If "PT_NAME" <> "" Then
        sCriteria = sCriteria & " AND [10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)].PT_NAME = """ & "PT_NAME" & """"
    End If
    
    If "PT" <> "" Then
        sCriteria = sCriteria & " AND [10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)].PT = """ & "PT" & """"
    End If
    
    If "TTL_QTY" <> "" Then
        sCriteria = sCriteria & " AND [10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)].TTL_QTy = """ & "TTL_QTY" & """"
    End If
   
   If "TTL_GROSS" <> "" Then
        sCriteria = sCriteria & " AND [10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)].TTL_GROSS = """ & "TTL_Gross" & """"
    End If
    
    
    Set db = CurrentDb()
    'This is new
    strTemplatePath = "W:\HER\HER - Administration\revops\RevOps_Databases\Monarch\ROSS_Uploads\01 - Daily\Excel Templates\TMC_eDRO5002 (2010-02-23).xlt" ' template file reference
    sOutput = "W:\HER\HER - Patient Access Management\PasCoord\Process Improvement Team\Jason\Revops\TMC_eDRO5002" & " (" & Format(Date, "yyyy-mm-dd") & ").xls" 'output file name and path
    

    
    'This is new
    Set objApp = New Excel.Application
    'This is new 'Your excel spreadsheet file goes here
    Set objBook = objApp.Workbooks.Add(strTemplatePath)
    'Name of sheet you want to export to
    Set objApp = objBook.Parent
    Set objSheet = objBook.Worksheets("eDRO5002")
    objBook.Windows(1).Visible = True
    'Opens the recordset and sets the variable
    
    Set rst = db.OpenRecordset("10q_Daily_eDRO5002_Patient_Access_HCD_Auth_Accts (HH)")
    
    
    With objSheet
        .Select
        'Clears the current contents in the workbook range
        .Range("A5:g65000").ClearContents
        'rst Copies the recordset into the worksheet
        .Range("A5").CopyFromRecordset rst
    End With
    objBook.SaveAs (sOutput)
    objBook.Close
    rst.Close
    objApp.Visible = False
    Set rst = Nothing
    Set db = Nothing
    Set objSheet = Nothing
    Set objBook = Nothing
    Set objApp = Nothing
    MsgBox "TMC eDRO5002 has been published"
End Sub

 

[JamesMcS]

I would nest all those IFs, and put another one in at the end to say if [field]="" Then...

 

[vbaInet]

Have a look in the help files regarding the EOF and BOF methods of a recordset. Type rst.EOF in the search box.

 

[jd.willis]

Would I do something like this:
IF COID = "" Then
RST = "NO DATA FOR THIS DATE"
End if


I'm still learning VB, and trying to understand.

 

[jd.willis]

Thank you!

What I did was set up another table for No Data Messages.

Added the following and its working great!


If rst.EOF = True Then

Set rst = db.OpenRecordset("30t_NO_DATA")

End If


Thank you!

 

[dbDamo]

Can you not just use the RecordCount property to check how many records there are???

 

[jd.willis]

Thank you! I will look at that option as well.!

 

[vbaInet]

By the way, you would normally test for this AFTER opening the recordset, not as a condition for opening the recordset.

For added "safety" use:

rst.BOF = True And rst.EOF = True

If both are true then your 100% sure that no records exists, set the cell to "No records", then exit sub.

Set rst = db.OpenRecordset("30t_NO_DATA")

If rst.EOF = True and rst.bof = true Then
     Set the cell here
     Exit Function/sub - whichever applies in your case
End If

 

[jd.willis]

Thank you for that! I added the line to my code.

 

[vbaInet]

No problemo!!