Duplicate entries

[jamesd]

I have a form that books equipment out to users .
What I want is to prevent duplicate users or equipment id.
I have this code in before update
Private Sub Gun_ID_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Gun_ID.Value
stLinkCriteria = "[Gun_ID]=" & "'" & SID & "'"

If DLookup("Gun_ID", "Temp_EquipmentLog", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning User Gun_ID " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

End If

Set rsc = Nothing
End Sub

When I run this I get an error message sayin it does not recognise "Gun_ID" as a valid field name.
I have used this code before without any problems. I have checked the spelling and format of "Gun_ID"
Any Ideas ?

 

[Dragos]

just an idea . why aren't you putting a unique index on the GUN_ID field? okay, it is a character-type field but if you don't have thousands of records and a WAN it will work acceptable and will prevent duplicates.