error Trapping question

[jauner]

Is there a way to error trap for the 3022 error? When a user enters the same account number in twice. I would rather have it say that it was a duplicate instead of the base cryptic access message.

Also where would the code go? In the after update event of the account field?

[Cosmos75]

Are you trying to avoid a user from entering the same number in two different textboxes on a form?

If that is the, then for both text boxes, you will want to check that the number in the other textbox is different from the number the user is trying to enter. You would do this on the BeforeUpdate event.

e.g. Suppose we have two textboses, txt1 and txt2, on a form.

Private Sub txt1_BeforeUpdate(Cancel As Integer)
If Me.txt1 = Me.txt2 Then
MsgBox "You entered the same number that is in txt2. Try again.", vbCritical
Cancel = True
End If
End Sub

Private Sub txt2_BeforeUpdate(Cancel As Integer)
If Me.txt2 = Me.txt1 Then
MsgBox "You entered the same number that is in txt1. Try again.", vbCritical
Cancel = True
End If
End Sub
Here's an article on editing records on a form that shows you how to use the different events for forms and controls (http://accessdb.info/index.php?option=com_content&task=view&id=42&Itemid=42) (on my website).

If you want to trap the error when it occurs, then try something like this

Sub HandleErr3022()
On Error GoTo ErrMsg:

'Code Here

ErrResumeHere:

ErrMsg:
If Err.Number = 3022 Then
MsgBox "Error Number: " & Err.Number & _
"Error Description: " & Err.Description & _
vbNewLine & vbNewLine & _
"You entered the same account number twice!", vbCritical
End If
'Keep in mind that you can have the code resume
'where it left off using Resume Next,
'or at another point in the code using Resume ErrResumeHere:
End Sub
Here a link to an article I wrote on error handlling and logging (http://www.accessdb.info/index.php?option=com_content&task=view&id=64&Itemid=45).

Hope that helps!

[OBP]

You can trap it as shown by cosmos and switch off the system message and use your own, remember to switch the system messages back on afterwards.
Another method, which I have used, is to search the table for the new record when the data is entered in to the field but before it is saved.