Check for duplicates with 2 fields

[Guest]

Hi,
I have a form that gets its data from a table with 3 fields: Key (auto
number), Item (text), and Detail (text). When I add a new record I want to
test for a duplicate records based on both the Item and Detail fields. If
there is a duplicate record I want to display my own error message and undo
the entry. How is the best way to do this? Also, the Detail field can be a
blank.
Thanks,

 

[Pieter Wijnen]

One way is to add a unique index on those fields & use Form Error to trap &
replace the default error msg (probably the best)

Or you can use the Forms Before Update even

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = DCount(*,"MyTable","Item='" & Me.Item.Value & "' And
Description='" & Me.Description.Value & "' And ID<>" & Nz(Me.ID.Value,0))
If Cancel Then MsgBox "Dooh"
End Sub