customizing duplicate index message (2K/XP)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote