Newbie question for Access 2003

[drosearup]

My experience has been mainly SQL Server. I am working on an Access 2003 database with a simple data entry form (frmNewPatient) for inserting new records into a Patient table. Before executing the insert, I want to check that there is no matching record already in the Patient table. The form has text boxes named txtLastName, txtFirstName, and txtSSN for entering unique data for a patient. Using VBA, I want to execute a query such as:

select * from Patient
where Patient.LAST_NAME = [Forms]![frmNewPatient]![txtLastName]
and Patient.FIRST_NAME = [Forms]![frmNewPatient]![txtFirstName]
and Patient.SSN = [Forms]![frmNewPatient]![txtSSN]

Also, how to check that the result set is empty.

Thanks,

Doug

 

[boblarson]

Instead of the Insert, you would use the Before Update Event and then you can use DCount instead:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim strWhere As String
 
strWhere = "[Last_Name] = " & Chr(34) & Forms!frmNewPatient.txtLastName & Chr(34) 
strWhere = strWhere & " [First_Name] = " & Chr(34) & Forms!frmNewPatient.txtFirstName & Chr(34)
strWhere = strWhere & " [SSN] = " & Chr(34) & Forms!frmNewPatient.txtSSN & Chr(34)
 
If DCount("*", "PatientTableNameHere", strWhere) > 0 Then
   MsgBox "Patient already exists.", vbExclamation, "Duplicate Entry"
   Cancel = True
End If
 
End Sub

 

[Dartagnan]

Just a thought. If you have a primary key on the SSN (no duplicates) column then Access will automatically find duplicate entries.

 

[drosearup]

I tried your suggested VBA code. I had to insert 'AND' between the second and third equality statements. It works great now.

Thanks

 

[drosearup]

Dartagnan, I'll suggest that to the user group doing the data entry. Thanks.