problem while input numbers to a field

islandboy

Hi guys, i am new to this forum and new to Access 07 and VBA

i need to a custom msg if someone enters a dublicate number in a

field in the form but i cant make it to work with the code bellow i

wont allow you to enter any new record.

The form is Employee Table, Field N I N


thanks.



Private Sub NIN_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.[N I N]
stLinkCriteria = "[NIN]=" & "'" & SID & "'"

'Check Employee Table table for duplicate
If DCount("NIN", "Employee Table", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning NIN Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original record
'rsc.FindFirst stLinkCriteria
'Me.Bookmark = rsc.Bookmark

End If

Set rsc = Nothing

End Sub