Duplicate entries in a field

[Gina]

I'd like to be able to somehow let users know when they've
entered a duplicate value in a field. I don't want to set
the field to "indexed - no duplicates" because there are
some occasions when a duplicate value is acceptable (and
necessary).
Does anyone have some advice or suggestions on how to do
this?
Thanks so much.

 

[John Vinson]

I'd like to be able to somehow let users know when they've
entered a duplicate value in a field. I don't want to set
the field to "indexed - no duplicates" because there are
some occasions when a duplicate value is acceptable (and
necessary).
Does anyone have some advice or suggestions on how to do
this?
Thanks so much.

You'll neet to use a Form to enter the data: datasheets don't have any
usable events. You can use some VBA code in the BeforeUpdate event of
the field such as (assuming that the field is of Text type, named
field, and there is a textbox on the form named txtField - use your
own names of course):

Private Sub txtField_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone ' get the Form's recordset
rs.FindFirst "[field] = '" & Me!txtField & "'"
If Not rs.NoMatch Then ' duplicate found
iAns = MsgBox("There already is a record for " & Me!txtField & _
vbCrLf & "Add it anyway?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End If
End Sub

You can get fancier, giving the user the option of displaying the
found record, but this should get you started.

 

[Gina]

Excellent! That's just what I was looking for...
Thanks for the detailed response.
I really appreciate it!

Gina

-----Original Message-----

I'd like to be able to somehow let users know when they've
entered a duplicate value in a field. I don't want to set
the field to "indexed - no duplicates" because there are
some occasions when a duplicate value is acceptable (and
necessary).
Does anyone have some advice or suggestions on how to do
this?
Thanks so much.

You'll neet to use a Form to enter the data: datasheets don't have any
usable events. You can use some VBA code in the BeforeUpdate event of
the field such as (assuming that the field is of Text type, named
field, and there is a textbox on the form named txtField - use your
own names of course):

Private Sub txtField_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone ' get the Form's recordset
rs.FindFirst "[field] = '" & Me!txtField & "'"
If Not rs.NoMatch Then ' duplicate found
iAns = MsgBox("There already is a record for " & Me! txtField & _
vbCrLf & "Add it anyway?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End If
End Sub

You can get fancier, giving the user the option of displaying the
found record, but this should get you started.


.