title: Access Input Form with Custom Error Messages
body: <p><strong>The Solution</strong></p>
  
  <p>The solution was to not try and capture the errors but do the error handling myself as part of the Add New Record command button:</p>
  
  <pre><code>Private Sub buttonNewRecord_Click()
  
  Dim ErrorInt As Integer
  Dim TeleCheck As Variant
  
  Name.SetFocus
  If Name.Text = "" Then
  MsgBox "Name is missing!"
  ErrorInt = ErrorInt + 1
  End If
  
  TeleCheck = DLookup("[Telephone]", "tblColdCallLog", "[Telephone] = '" &amp; Me.Telephone &amp; "'")
  If Not IsNull(TeleCheck) Then
  MsgBox "Telephone number already exists in the table!"
  ErrorInt = ErrorInt + 1
  End If
  
  If ErrorInt &lt; 1 Then
  DoCmd.GoToRecord , , acNewRec
  MsgBox "Record Added!"
  End If
  
  End Sub
  </code></pre>
  
  <p>Original Post:</p>
  
  <p><strong>What I Have:</strong></p>
  
  <p>I have created a simple Access 2013 Form used to input data into a table. On the Form, the user enters data into the fields and clicks on a button made using the Command Button Wizard to Add New Record. </p>
  
  <p>The form has one required field, [Name], and one field set to 'Index: Yes (No Duplicates)', [Telephone Number]. In the Form, this correctly produces error messages if the [Name] field is empty or there is a duplicate number detected in the [Telephone] field that is also in the table.</p>
  
  <p><strong>What I Am Trying To Do:</strong></p>
  
  <p>The error messages that appear are not user friendly. I would like to replace them with custom error messages and if there are no errors, maybe a message that says all went well.</p>
  
  <p><strong>What I Have Tried:</strong></p>
  
  <p>On the Form properties, Events tab, in 'On Error', [Event Procedure]:</p>
  
  <pre><code>Private Sub Error_Sub(DataErr As Integer, Response As Integer)
    If DataErr = 3022 Then
      MsgBox "Duplicate telephone number found in table!"
      Response = acDataErrContinue
    End If
    If DataErr = 3314 Then
      MsgBox "Name is missing!"
      Response = acDataErrContinue
    End If
  End Sub
  </code></pre>
  
  <p>This works but only when you close the Form... When you click the 'Add New Record' Command Button, it simply shows the default error messages when appropriate.</p>
  
  <p>Maybe I should use the Event 'Before Update'? I can't seem to use the same VBA script. I'm not allowed to define DataErr or Response. So, I'll use an Expression instead:</p>
  
  <pre><code>=IIf(Error(3022),MsgBox("Duplicate telephone number found in table"))
  =IIf(Error(3314),MsgBox("Name is missing"))
  </code></pre>
  
  <p>This works... but when there is no error. Even if there is a name in the [Name] field, the error shows but at least it replaces the default error message.</p>
  
  <p>Let's put it in the button itself? I'll have to use the Macro Builder to edit it. It's a bit hard to copy and paste this one so I'll simplify:</p>
  
  <pre><code>OnError GoTo Error_Handling
  GoToRecord New
  If [MacroError]&lt;&gt;0 Then 
     MsgBox = "[MacroError].[Description]"
  End If
  
  Error_Handling:
  If Error(3022) Then
    MsgBox = "Duplicate telephone number found in table!"
  End If
  If Error(3314) Then
    MsgBox = "Name is missing!"
  End If
  </code></pre>
  
  <p>This does the same as the 'Before Update' event; replaces the default error message but regardless of whether or not the error message should be triggered in the first place.</p>
  
  <p>What am I doing wrong? I get the feeling it's something really simple. I've tried a variety of other combinations and endless Googling but I feel stumped.</p>