1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Duplicate in indexed field

Discussion in 'Software Development' started by awjensen, Apr 5, 2004.

Thread Status:
Not open for further replies.
  1. awjensen

    awjensen Thread Starter

    Mar 25, 2002
    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

    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,
    Response = acDataErrContinue
    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.
  2. Rockn


    Jul 29, 2001
    Which program or database are you using? I am assuming Access
  3. MustBNuts


    Aug 21, 2003
    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

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

    Hope this helps.


    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.
  4. coderitr


    Oct 12, 2003
    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.
  5. AbvAvgUser


    Oct 3, 2002
    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.
  6. Ricewalker


    Apr 12, 2004
    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
    ..... Add fields to RS
    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
    Set RS = Nothing

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

    Rick Anderson
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/217273

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice