How to have field autosearch for duplicate entry?

[klbrownie]

I have a form setup to keep track of patients and a subform setup for their separate visits. I need to find a way so when a user inputs the number of a person who is already in the database in the main form, a message will come up to let the user know before they start entering visit data in the subform. Any ideas?

[ADezii]

1. ASSUMPTIONS:
   • Table Name is tblPatients.
   • tblPatients contains a Field named [PatientNum]{LONG}, which is a Numeric Value uniquely identifying a specific Patient.
   • The Field Name on your Form Bound to [PatientNum], and also named PatientNum, is used to enter Patient Numbers.
2. The following Code, placed in the BeforeUpdate() Event of the PatientNum Field on the Form, will check for the existence of a Duplicate Patient. If this is a Duplicate Entry, the Update of the Field will be cancelled, and the User notified via a Message Box.
   

   Private Sub PatientNum_BeforeUpdate(Cancel As Integer)
   If DCount("*", "tblPatients", "[PatientNum] = " & Me![PatientNum]) > 0 Then
     MsgBox "Patient [" & Me![PatientNum] & "] already exists in the Database!", vbExclamation, "Duplicate Patient"
       Cancel = True
   End If
   End Sub

[klbrownie]

Thanks for the response!

I put the code in, but it does not do anything. I think it might be because the patient number field is a text field. This is because it has dashes is it. Will this affect the results?

[ADezii]

It does make a difference if Patient Number is TEXT instead of NUMERIC. Notice the slight change in Code Line# 4 below:

Private Sub PatientNum_BeforeUpdate(Cancel As Integer)
Dim intResponse As Integer

If DCount("*", "tblPatients", "[PatientNum] = '" & Me![PatientNum] & "'") > 0 Then
  MsgBox "Patient [" & Me![PatientNum] & "] already exists in the Database!", vbExclamation, "Duplicate Patient"
    Cancel = True
End If
End Sub

[klbrownie]

IT works!! Thank you so much for your help!

[ADezii]

You are quite welcome.