new idea duplicate surname and DOB

[goju]

hi ,

see below for the current code to find a duplicate surname (this works Fine)


now that ive properly thought it through what i need to do is insert a surename and if (by Chance) when i insert the Date of Birth if this is the same highlight it as a duplicate record. However if these people are twins then i need the option to insert the duplicate record.

any ideas on how to go about this, see code below.

Private Sub Text24_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Surename.Value
stLinkCriteria = "[Surename]=" & "'" & SID & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("Surename", "tblData", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Duplicate Name " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

 

[Pat Hartman]

Rather than writing code to handle things that a database engine can handle, you should create unique indexes and trap the error codes if you want to display pretty messages for your user.
You can create a unique index with three fields, surname, forname, and DOB. That should take care of the twins problem.