Form message for duplicate record

[jereece]

I have a form with Text box that I am using for a case number. I have to use a text box formatted as text instead of formatted as a number because the format of the numbering sequence is 00\-000\-AAA\-00;;. In the table design, I chose "no duplicates" because each case number has to be unique. The last 2 digits is actually a recommendation number for the case. When someone tries to enter a duplicate number, the message that Access provides is "You can not go to the selected record". That does not tell the user that they are trying to enter a duplicate record. How can I make Access say something like "You have entered a duplicate case number. Please try a different number".

Thanks,
Jim

 

[Moniker]

When you get the Access error message, there will be an associated error number with it. You need to trap that error number and then you can replace the message box with whatever you like. If you're not sure what I'm talking about, search for "Error Trapping" in here and you'll find plenty of examples. Generically, it looks like this:

Sub SomeRoutine()

On Error GoTo SomeRoutine_Error

... Your Code Here ...

Exit Sub

SomeRoutine_Error:
    Select Case Err.Number
        Case 123  [B][COLOR="Red"]<--- The error number you're trying to trap[/COLOR][/B]
            MsgBox "Your custom error message here"
            Resume Next
        Case Else [B][COLOR="Red"]<--- Something else went wrong[/COLOR][/B]
            MsgBox Err.Number & " - " & Err.Description
    End Select

End Sub

 

[jereece]

There is no number with the message Access is giving me. I just says "You can not go to the selected record.". It does not provide any kind of number. Any other suggestions?

Thanks,
Jim

 

[neileg]

You could validate the case number in the Before Update event of the text box. Use DCount() to count the number of existing records with that case number. Obviously, if it's anything other than 0 then it's a duplicate.

 

[missinglinq]

I do the same as Neil, and popup a messagebox telling the user the number already exists, then dumping the attempted record. BTW, as a rule, ID numbers, SSNs and the like shouldn't be numerical, but rather text, as yours is formatted. Fields, as a rule, really only need to be set to a numerical datatype if they're going to be used in mathematical equations.

Linq