The Solution
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:
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] = '" & Me.Telephone & "'")
If Not IsNull(TeleCheck) Then
MsgBox "Telephone number already exists in the table!"
ErrorInt = ErrorInt + 1
End If
If ErrorInt < 1 Then
DoCmd.GoToRecord , , acNewRec
MsgBox "Record Added!"
End If
End Sub
Original Post:
What I Have:
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.
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.
What I Am Trying To Do:
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.
What I Have Tried:
On the Form properties, Events tab, in 'On Error', [Event Procedure]:
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
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.
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:
=IIf(Error(3022),MsgBox("Duplicate telephone number found in table"))
=IIf(Error(3314),MsgBox("Name is missing"))
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.
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:
OnError GoTo Error_Handling
GoToRecord New
If [MacroError]<>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
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.
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.
