Error Message

[Guest]

Hi,

I've created an index in some of my tables to aviod duplicate entries.
However the message that appears when a duplicate has been detected is not
very helpful. I was wondering if it was possible to alter it?

Ian

 

[Arvin Meyer [MVP]]

Using the BeforeUpdate event, you can do your own checking for duplicates:

Private Sub txtPersonID_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

Dim db As Database
Dim rst As Recordset
Dim strSQL As String

Set db = CurrentDb

' Add quotes to Me!PersonID for a text type
' strSQL = "SELECT [PersonID] FROM tblPeople WHERE [PersonID] = """ &
Me![txtPersonID] & """"

strSQL = " SELECT [PersonID] FROM tblPeople WHERE [PersonID] = " &
Me![txtPersonID]
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "This Person ID is already allocated, please select another",
vbOKOnly, "Duplicate PersonID"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access