Sorry about the title, wasn't sure how to say it. I have a module that verifies a table has data:
Public Sub CheckTable()
Dim fIsTableEmpty As Boolean
Dim db As Database
Dim rs As Recordset
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("Scrap", dbOpenTable)
If rs.RecordCount = 0 Then
fIsTableEmpty = True
MsgBox "Nothing in the table, Please import Cognos report"
On Error GoTo Error_Handler
Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim strSelectedFile As String
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add "Excel Files", "*.xls"
.Filters.Add "Excel 2007", "*.xlsx"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
strSelectedFile = vrtSelectedItem
' If Not IsEmpty(strSelectedFile) Then
If Len(Trim(strSelectedFile) & "") > 0 Then
Dim StrSQL As String
StrSQL = "Delete * from Scrap;"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Scrap", strSelectedFile, True, , False
DoCmd.OpenForm "FileNotSelected", acNormal, , , , , False
Set fd = Nothing
I call it from a form button:
Private Sub btnTotal_Click()
'Turns off the Access warning messages
'Turns the Access warning messages back on
DoCmd.OpenForm "ByTotalScrap", acNormal, , , , , False
What I want to happen is if the module finds that there is no data to stop running the button code on the form. Instead, it resumes back and continues to finish the button code and open the 'ByTotalScrap' form. That is fine if the user selects an Excel file from within the Module code, but if the users clicks the Cancel button when selecting an Excel file the form is still opened and there is no data. Hope that makes sense. Thanks for any help on this.