Msg Box to ask User to record error number and contact administrator?

[CarysW]

Hello again,

So I have error handling in the code of my Custome parameter forms to make sure that the user does not get bombarded if they decide not to save an export.

However, this obviously just corresponds to error number 2501.

I would like to create a msg box that pops up when any other error occurs, or even better add the text to the error message asking the user to make a note of the error number and to contact the Administrator.

Is this possible?

Here is an example of my code:

Private Sub cmdOKPL_Click()
On Error GoTo cmdOK_Error

 
 ' Open MsgBox to tell user that a save box will open next
    MsgBox "Please Rename and Save The Export", vbQuestion, "Export To Excel"
    ' Exports Query to Excel
    DoCmd.OutputTo acOutputQuery, "qryPL", "ExcelWorkbook(*.xlsx)", "", True, "", 0, acExportQualityPrint
    ' Closes Form
    DoCmd.Close acForm, "frmChPL"
 Exit Sub

cmdOK_Error:
 If Err.Number = 2501 Then
        Resume Next
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Next
    End If


End Sub

 

[DCrake]

This is an example on how to manipulate error trapping. This sub is called in the event of an error occurring during the import of a Excel sheet into a table.

btnImport_Click_error:
Select Case Err
   Case 3022 ' duplicate value in index
      s = "Duplicate value in primary index, memberID " & INrs![MemberID] & " already exists."
      s = s & vbNewLine & vbNewLine
      s = s & "If you are importing memberID from your sheet then you must ensure that"
      s = s & vbNewLine
      s = s & "they are unique within your sheet and not already allocated in the database."
      s = s & vbNewLine & vbNewLine
      s = s & "Check the column entries under MemberID in the file and correct any duplications."
      s = s & vbNewLine & vbNewLine
      s = s & "The problem was found around row " & rec & " in your file."
      MsgBox s, vbExclamation + vbOKOnly, "Source File Problem"
      Resume btnImport_Click_exit
   Case 3316 ' not valid entry for combo box
      s = "Invalid entry for Status against " & INrs![FirstName] & " " & INrs![LastName] & "."
      s = s & vbNewLine & vbNewLine
      s = s & "If you are importing Status from your sheet then you must ensure that"
      s = s & vbNewLine
      s = s & "the entries are exactly one of Current, Lapsed or Deceased."
      s = s & vbNewLine & vbNewLine
      s = s & "Also, ensure that any empty cells for Status really are empty;"
      s = s & vbNewLine
      s = s & "Excel has an annoying habit of leaving spaces in otherwise empty cells."
      s = s & vbNewLine & vbNewLine
      s = s & "Check the column entries under Status in the file and correct any errors."
      s = s & vbNewLine & vbNewLine
      s = s & "The problem was found around row " & rec & " in your file."
      MsgBox s, vbExclamation + vbOKOnly, "Source File Problem"
      Resume btnImport_Click_exit
   Case 3421 ' data type conversion error
      s = "An entry in your sheet against " & INrs![FirstName] & " " & INrs![LastName] & " is not valid."
      s = s & vbNewLine & vbNewLine
      s = s & "If you are importing dates from your sheet then you must ensure that"
      s = s & vbNewLine
      s = s & "they are valid dates e.g. not 31st September or 30th February etc."
      s = s & vbNewLine & vbNewLine
      s = s & "Ensure that any numeric cells in your sheet really do contain numbers;"
      s = s & vbNewLine
      s = s & "a common problem is O (letter Oh) in stead of 0 (zero)."
      s = s & vbNewLine & vbNewLine
      s = s & "Also, ensure that any empty cells in this row really are empty;"
      s = s & vbNewLine
      s = s & "Excel has an annoying habit of leaving spaces in otherwise empty cells."
      s = s & vbNewLine & vbNewLine
      s = s & "Check the column entries in this row in the file and correct any errors."
      s = s & vbNewLine & vbNewLine
      s = s & "The problem was found around row " & rec & " in your file."
      MsgBox s, vbExclamation + vbOKOnly, "Source File Problem"
      Resume btnImport_Click_exit
   Case 2391 ' can't find target column heading
      s = "The file contains a column heading that I do not recognise: "
      s = s & vbNewLine
      s = s & Error$
      s = s & vbNewLine & vbNewLine
      s = s & "This is probably caused by incorrect spelling or illegal punctuation."
      s = s & vbNewLine & vbNewLine
      s = s & "Please check the heading spellings in the file and remove any punctuation."
      MsgBox s, vbExclamation + vbOKOnly, "Source File Problem"
      Resume btnImport_Click_exit
   Case 3349 ' illegal data entries in file
      s = "There are data in cells in the file that I can not handle: "
      s = s & vbNewLine
      s = s & Error$
      s = s & vbNewLine & vbNewLine
      s = s & "This is probably caused by the use of dashes (---) in cells to mean blank."
      s = s & vbNewLine & vbNewLine
      s = s & "Please search the file for dashes and remove them."
      MsgBox s, vbExclamation + vbOKOnly, "Data Problem"
      Resume btnImport_Click_exit
   Case 3265 ' illegal data entries in file
      s = Error$
      s = s & vbNewLine & vbNewLine
      s = s & "The column names are not correct: "
      s = s & vbNewLine & vbNewLine
      s = s & "Your column headings in row 1 must be as described; check spelling and spaces."
      s = s & vbNewLine
      s = s & "Typically caused by leading and/or trailing spaces - remove them."
      MsgBox s, vbExclamation + vbOKOnly, "Data Problem"
      Resume btnImport_Click_exit
   Case Else
      MsgBox Err & " - " & Error$, vbExclamation + vbOKOnly, "Error in btnImport_Click"
      Resume btnImport_Click_exit
End Select

 

[CarysW]

Thanks for that. It looks very complicated Haha! I'm sure I can work it into my database.

Thanks again.

 

[DCrake]

Actually it's just case statement that looks at the error number and constructs a meaningfull error message for the user. It uses the case else for all not trapped errors.

David