Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    customizing duplicate index message (2K/XP)

    Is there a way to customize the message received when you have a duplicate unique index. Or is there an easy way to test for it before you save the record? The index in question is made up of four fields that define a unique location (in a cemetery, we do not want anyone to share a grave.). Is there a way to catch this before the record is saved?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: customizing duplicate index message (2K/XP)

    You can trap the error on a form (not directly in a table, but you shouldn't let end users edit a table directly anyway). TheOnError event of a form lets you handle this kind of error. Here is an example:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const conDuplicateKey = 3022
    Dim strMessage As String
    Select Case DataErr
    Case conDuplicateKey
    strMessage = "Please check your data. The grave is already in use."
    Case Else
    strMessage = AccessError(DataErr)
    End Select
    MsgBox strMessage, vbExclamation
    Response = acDataErrContinue
    End Sub

    Setting Response to acDataErrContinue tells Access to suppress the built-in error message. If you set it to acDataErrDisplay (or don't set it, this is the default value), the built-in error message is shown.

    You can also test for duplicates: in the BeforeUpdate event of the form, build a WHERE condition (as a string) based upon the data entered by the user. Then test if there are any existing records satisfying this WHERE condition. You can use a DLookup for this, or open the data behind the form as a DAO recordset and do a FindFirst, or open a DAO or ADO recordset with the WHERE condition as filter and test if it has records. Whatever the method, if you find that there is an existing record satisfying the WHERE condition, issue a warning and set the Cancel argument of BeforeUpdate to True.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: customizing duplicate index message (2K/XP)

    Addition: if you want to find out what the error numbers are, so that you can trap them, you can use code like this during development. You wouldn't want to leave it that way in the production version.

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Dim strMessage As String
    strMessage = "Error number " & DataErr & " occurred, with description:" & vbCrLf & AccessError(DataErr)
    MsgBox strMessage, vbExclamation
    Response = acDataErrContinue
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: customizing duplicate index message (2K/XP)

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: customizing duplicate index message (2K/XP)

    Out of curiousity, will you have no multidepth graves? If you do have, then you would need to add an additional field to your key because they would be in the same location.
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: customizing duplicate index message (2K/XP)

    I assume that they would just number the graves differently, but it is a good point since they are just starting burying in the side of the mountain one grave above another.
    Thanks
    Zave

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: customizing duplicate index message (2K/XP)

    I was talking about interrments in the same plot, which is allowed in some cemetaries and under specific conditions.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: customizing duplicate index message (2K/XP)

    In Jerusalem that is not done so I never thought of it.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: customizing duplicate index message (2K/XP)

    Pehaps it's only a USA phenomenon, since most of our cemetaries are hemmed in by urban sprawl.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •