search duplicate by surname and DOB

[frankie13]

I have an input form, for new members. Sometimes we enter a member twice, duplicating information. What i am trying to implement is for a click of a command button (cmdCheckDuplicate), to query a table by taking the values of the members surname(txtSurname) and Date of Birth(txtDOB), and checking they haven't been entered before.

So far i have this piece of code, which only checks duplicates by Surname. Can somebody tell me how i can add the date check as well to this code.

Private Sub Command108_Click()

Dim strSql As String
Dim strSurname As String
Dim strFirst As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset


Set rsc = Me.RecordsetClone

strSurname = Me.txtSurname.Value


stLinkCriteria = "[Surname]=" & "'" & strSurname & "'"


'Check StudentDetails table for duplicate StudentNumber
If DCount("Surname", "tblVolunteer", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "WARNING: A Volunteer with this surname (" _
& strSurname & ") has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"

strSql = "SELECT DISTINCT tblVolunteer.*, Year(Now())-Year([tblVolunteer]![DOB]) AS intAge, qryMedConIDjoinMedConName.MedicalConditionName, qryMedicationIDjoinMedicationName.Name " & _
"FROM (tblVolunteer LEFT JOIN qryMedConIDjoinMedConName ON tblVolunteer.SubjectID = qryMedConIDjoinMedConName.tblVolunteerMedicalCondition.SubectID) LEFT JOIN qryMedicationIDjoinMedicationName ON tblVolunteer.SubjectID = qryMedicationIDjoinMedicationName.tblVolunteerMedication.SubjectID " & _
"WHERE tblVolunteer.[Surname]=" & "'" & strSurname & "'"

'WHERE (((tblVolunteer.Surname)="woodbourne"));
'MsgBox strSql

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySurnameSearch")
qdf.SQL = strSql
Set qdf = Nothing
Set db = Nothing
'strMedCon = 0
'strMedication = 0

DoCmd.OpenForm "subFormSurnamecheck"

Else
MsgBox "There are no other records with the surname (" _
& strSurname & ")." _
& vbCr & vbCr & "Proceed with data entry.", vbInformation _
, "Duplicate Information"


'Go to record of original Student Number

'rsc.FindFirst stLinkCriteria
' Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing


End Sub]


many thanks
frankie 13

 

[frankie13]

sorry about the size of the font (was trying to make it smaller) forgot HTML works differently.

 

[MStef]

Hello frankie13!
I suggest you to make ONE index with this two fields.
It'll prevent to enter two records with the same data in
this two fields.

(MStef alias Štef)

 

[frankie13]

I cant do that Stef, coz sometimes a member might have the same surname and Date of Birth (e.g. twins), and they both must be allowed to be entered.

 

[MStef]

Ok Frankie13!
I made "DemoFrankie13A97.mdb" only for you.
Look at it. (MStef alias Štef)