Close Access if table is NULL

[Bill Pugh]

Hi Folks:

I would like to know what code I could add to the below code to make Access close if the report I'm exporting is NULL. The DailyQC report looks at a table called Daily_INV_Certs and exports it to a mapped drive as an Excel spreadsheet. If the table Daily_INV_Certs is empty I want Access to quit.

Thanks

Function testExport()
On Error GoTo testExport_Err

Dim strDate As String
Dim strFileName As String
Dim strFilePath As String

strDate = CStr(DatePart("m", Date) & DatePart("d", Date) & DatePart("yyyy", Date))
strFileName = "DailyCerts" & strDate & ".xls"
strFilePath = "T:\Pugh\" & strFileName


DoCmd.OutputTo acReport, "DailyQC", "MicrosoftExcel(*.xls)", strFilePath, False


testExport_Exit:
Exit Function

testExport_Err:
MsgBox Error$
Resume testExport_Exit

End Function

 

[fpendino]

Create a recordset on that table. Use the Count Records method for the recordset and check to see if it equals zero.

 

[Bill Pugh]

Create a recordset on that table. Use the Count Records method for the recordset and check to see if it equals zero.

Not sure how to do this? Any examples?

Thanks

 

[ghudson]

Try this...

Hi Folks:

I would like to know what code I could add to the below code to make Access close if the report I'm exporting is NULL. The DailyQC report looks at a table called Daily_INV_Certs and exports it to a mapped drive as an Excel spreadsheet. If the table Daily_INV_Certs is empty I want Access to quit.

Thanks

Function testExport()
On Error GoTo testExport_Err

Dim strDate As String
Dim strFileName As String
Dim strFilePath As String

strDate = CStr(DatePart("m", Date) & DatePart("d", Date) & DatePart("yyyy", Date))
strFileName = "DailyCerts" & strDate & ".xls"
strFilePath = "T:\Pugh\" & strFileName

[COLOR=Blue]If DCount("*", "Daily_INV_Certs") < 1 Then
    MsgBox " < 1" 'for testing
    DoCmd.Quit acQuitSaveNone
Else
    MsgBox " Not < 1" 'for testing
End If[/COLOR]

DoCmd.OutputTo acReport, "DailyQC", "MicrosoftExcel(*.xls)", strFilePath, False

testExport_Exit:
Exit Function

testExport_Err:
MsgBox Error$
Resume testExport_Exit

End Function

 

[Bill Pugh]

Thank you! Work like a charm!