Duplicate in indexed field

[awjensen]

I am trying to present the user with a more friendly error messsage than the
following which occurs when the user adds a record with a with a duplicate
value in an index.

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or
relationship......."

This message does not appear to have an error number.

I have the following code in the forms error event:


If DataErr = 3022 Then
MsgBox "There is already a company by the same name or" & Chr(13) &
" a person with the same first and last name in the database", vbOKOnly,
"Error"
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If

The above code does not trap the generic error message.

There are three fields involved (lastname, firstname, and companyname.) I
should mention that the code is in a form that is used only to add a new
name and that the error occurs when I try and close the form (Insert the new record.)

Any help would be appreciated.

 

[Rockn]

Which program or database are you using? I am assuming Access

 

[MustBNuts]

Assuming you are using Access and/or VB, you could try an On Error in your event.

The easiest way to do this is to create a command button for the save, then in the subroutine(event) add the following:

On Error goto ErrorHandler (add this just under the line Private Sub cmdSave_Click() )

then at the bottom, just before the End Sub add this

ErrorHandler:
MsgBox Err.Description
Resume Exit_cmdSave_Click (add this if it's Access, skip if it's VB)

Hope this helps.

MBN

PS: if you are still getting the error by exiting out without clicking the Save button, you can either input code to force the save or look into "step through code" that is available with Visual Basic and Visual Basic Editor in Access. Stepping through the code allows you to see exactly when/where the error is thrown.

 

[coderitr]

The problem is that the insert event on the data control (or whatever data access method you are using) does not fire the Form1.Error event. As MustBNuts said, you need to handle the error inside the procedure where the insert is performed. If you're using ADO, the error number that you will get may for a duplicate key violation may be different for different database providers. The best way around this is to query the database to see if the record already exists before attempting the insert. Then you can show whatever message you want. Using a generic error handler, you will have to write more code to handle specific errors and display custom messages based on those. This way, you can nicely handle a duplicate key occurence and have the error handler take care of the more serious errors for you.

 

[AbvAvgUser]

I second the suggestion of coderitr. Before you update the table/recordset, check for duplicates. If they are there, don't submit the data to the table at all. Check for duplication thru code, that will be easier to handle.

On Error is use to handle those errors/exceptions which are unpredicatable and unforseen. Here you can foresee the problem of dupliates. So handle them thru code and that is quite easy to do.

However, there is a utility called error look up that comes with Visual Studio 6. Use that to find out the exact error number. It is somewhat laborious, but still will do your work.

 

[Ricewalker]

Another similar way to handle this is to use the On Error Resume Next and then after you post the record check the value of ERR.Number
RS.AddNew
..... Add fields to RS
RS.Update
If Err.Number <> 0 then
Select Case Err.number (the error number you want for a duplicate is 3022)
Case 3022
Msgbox "Crystal Clear Error Message"
Case Else
MsgBox "Error: " & Err.Description & " ErrNmb: " & Err.Number
End Select
RS.Close
Set RS = Nothing

Hope this was clear, its been a while since I've done VB (working in Delphi 7)

Rick Anderson