Custom Error Msg for Import

[Access_Help]

Hi,

I am looking to make a custom messages for this code, If it imports successfully then output "Import Successful" else "Error in Source File".

New to coding, so any pre-written script to help would be appreciated.

Private Sub cmdImportExcel_Click()

'the path to the excel workbook
Dim strExcelPath As String

strExcelPath = Environ("USERPROFILE") & "\My Documents\Students.xlsx"
'import data from excel
Call DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel8, "Students", strExcelPath, _
True, "A1:D11")

On Erro GoTo ErrorHanMsg1
MsgBox "Import Successful", , "Ok - 1"

Exit Sub
'This comes before End Sub or End Function Statement
ErrorHanMsg1:
MsgBox Err.Number & vbCr & Err.Description

 

[theDBguy]

Hmm, to do that, the only thing I could think of is to import the data into a temp table and then use a recordset to loop through the records to check for valid data and compose your error message.

 

[vba_php]

do u want an error message if the import completely fails or if there is just *some* data that is not imported, but the rest is? you already have an error handler in your code, so you can simply modify the conditional statement in the error handler to:

msgbox "Error in Source File", vbCritical