How to make a message "data can't double"

[Stefent Tan]

I have a textbox call Staff_ID, I don't want my user to type the same Staff_ID . So I write the vba code like this

Private Sub Staff_ID_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
 
Set rsc = Me.RecordsetClone
SID = Staff_ID.Value
stLinkCriteria = "[Staff_ID]=" & "'" & SID & "'"
 
    If DCount("Staff_ID", "[Employee]", stLinkCriteria) > 0 Then
        Me.Undo
        MsgBox "Staff_Id = " & SID & " Already Have..." _
        & vbCr & vbCr & "Type another staff_ID.", vbInformation _
        , "Data cannot Double"
        Me.New.SetFocus
 
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
        End If
Set rsc = Nothing
End Sub

The problem when I try it I Have a message " Run time Error "3420" Object Invalid or no longer set.When I open the debug at ( rsc.findfirst stlinkcriteeria ) have a yellow color.

Please help me to solve this problem. Thank you

[Mihail]

Access manage itself your problem.
Assuming that the Staff_ID text box is bound to Staff_ID field all you need is to set the Indexed property for Staff_ID field (table design view -> General tab) to Yes (No Duplicates).

This way Access do not allow duplicates for this field.

By the way, the event _AfterUpdate() is not useful because you need to looking for duplicates _BeforeUpdate (BEFORE the value is passed to table, isn't it ?)

Good luck !

[Stefent Tan]

thanks, but it still no work, maybe you have another way for my case, all I need is making my application look like a professional. Thanks

[Mihail]

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If DCount("[Staff_ID]", "Employee", "[Staff_ID]='" & Staff_ID & "'") Then
        MsgBox ("Existent ID")
        Cancel = True
    End If
End Sub