Message box pop up upon search results

[lals101]

Dear All,
I'm kind of new to VBA in access and i'm trying to create a small dialog box that appears after entry of data in a form. Please bear with me.

After a user enters an id number into a text box i wish a piece of code to run to check whether in the form's table the id number already exsists. If it does then a message in the form of a dialog box would appear informing the user. note the table is allowed to accept duplicate ID numbers.

i have written some code below as a start but doesn't work.
Private Sub patient_id_AfterUpdate(Cancel As Integer)
Dim strType As String
strType = "[patient_id]=[tbl_cmd_treatment_record_master.patient_id]"
If vbYes = MsgBox("already", vbYesNo, "check") Then
DoCmd.OpenForm "frm_cmd_stock"
End If
End Sub

any help much appreciated

thanks

 

[John Big Booty]

I'd test the Dcount() of [patient_id] ,something like;

If Dcount("patient_id", "tbl_cmd_treatment_record_master", "patient_id = " & Me.patient_id) <> 0 Then
     If  MsgBox("already", vbYesNo, "check") = vbYes Then
          DoCmd.OpenForm "frm_cmd_stock"
     End IF
End IF