Thank you for your response. I am a beginner in coding and am unfamiliar with error trapping. To clarify some of your confusion as I was not clear. My thought process is to:
1. Chose a excel worksheet to import using the picker
2. Delete the old data in the tables
3. Import new information into the cleared table as raw data
THIS IS WHERE I GET THE ERROR CODE WHEN THE WRONG WORKSHEET/WORKBOOK IS CHOSEN. I WOULD LIKE TO STOP THE SYSTEM FROM AUTO GENERATING THE DEBUGING MESSAGE AND PUT MY OWN TO ALSO INCLUDE STOPPING THE REST OF THE CODE.
3. Open the tables for review - there could be 500,000 records.
4. Update and Append the tables
5. Message complete or incomplete
I hope this is clear. Thanks for the info on the code tags. Never knew they were there. Always just copied and pasted and always auto corrects without the indents. I am going to attempt to post the code again with the code tags.
Code:
Private Sub cmdBRP_Click()Dim dbs As DAO.Database
Dim SelectedFile As String
Dim FilePicker As FileDialog
Dim SQLdelete As String
Set dbs = CurrentDb
Dim strXls As String
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
FilePicker.AllowMultiSelect = False
FilePicker.Filters.Add "Excel", "*.xls*", 1
FilePicker.InitialFileName = "C:\Users\"
FilePicker.Title = "Please Select the Excel Data..."
FilePicker.Show
If FilePicker.SelectedItems.Count <> 0 Then
'Delete table records
CurrentDb.Execute "DELETE * FROM tblImportFM_BRP", dbFailOnError
CurrentDb.Execute "DELETE * FROM tblAppImportFM_BRP", dbFailOnError
'Select one file
SelectedFile = FilePicker.SelectedItems(1)
'Transfer Information
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportFM_BRP", SelectedFile, True, "CC BRP!"
'Open Tables for review
DoCmd.OpenTable "tblImportFM_BRP", acViewNormal, acEdit '
'Execute Append Query (converting info from import and updating into appended table)
dbs.Execute "appqryImportFM_BRP", dbFailOnError
'Message when successful
MsgBox ("The data has been successfully loaded")
'Message if no file was selected
Else
Call MsgBox("No file was selected.")
End If
End Sub