Message comes up when duplicate name & dob entered 2

[Dave177]

Hello,

I have seen similar posts to this but nothing exact.

Basically, I would like a message to come up on the BeforeUpdate of the field [DateOfBirth] but both fields [Surname] and [DateOfBirth] need to be used to identify a duplicate. i.e. the message should come up if there is the same date of birth for someone with the same surname.

I have got the following code but it doesn't work...

Private Sub DateOfBirth_BeforeUpdate(Cancel As Integer)

    Dim strSurnameAndBirthdate As String
    
    strSurnameAndBirthdate = Nz(DLookup("SurnameDateOfBirth", qrySecSurnameDateOfBirth, "SurnameDateOfBirth = """ & Me.Surname & """ & """ & Me.DateOfBirth & """"), "")
    If Len(strName) > 0 Then
    MsgBox "Already Exists"
    Cancel = True
    End If

End Sub

As you can probably guess I am not that good at coding and have hijacked some similar code from thread702-1361603.

Thank you for any help.

Dave

 

[Remou]

There is a lot to be said for adding a unique key for name and date of birth and then trapping the error (Form Error event). However, if you still want to go this way:

[tt]strOK = Nz(DLookup("Surname", "TableOrQueryWithThisInfoName", "Surname = '" & Me.Surname & "' AND DateOfBirth=#" & Format(Me.DateOfBirth,"yyyy/mm/dd") & "#"), "")

If strOK<>"" Then
'Found[/tt]

 

[Dave177]

Thanks, Remou.

The reason why I'm doing it this way is that I will then give the user the option to view the already entered record, or to ignore and create a new one with the same surname and birth date. Although it will be quite rare that two of our members will have the same birth date and surname, it is possible e.g if they are twins. Do you have any views on this?

 

[Remou]

No, not really. It depends, as you say, on the number of the members and the part of the world they come from.

 

[Dave177]

I've now got what you suggested in i.e.

strOK = Nz(DLookup("Surname", "TableOrQueryWithThisInfoName", "Surname = '" & Me.Surname & "' AND DateOfBirth=#" & Format(Me.DateOfBirth,"yyyy/mm/dd") & "#"), "")

If strOK<>"" Then
  'Found

But if someone has a surname such as "O'Connor" I think that the "O'" causes problems and I get the following mesage

Run-time error '3075':
Syntax error (missing operatior in query expression 'Surname = 'O'g' AND DateOfBirth=#1978/05/13/#'.

Is there a way around this?

David

 

[SeeThru]

It is the ' that causes the error.

You could use a text replace to remove any before the check, but this is a pain.

The other option is to open a recordset using the captured details:

set rst=currentdb.openrecordset "SELECT Surname, DateOfBirth FROM MyTable WHERE Surname = '" & Me.Surname & "' AND DateOfBirth= Me.DateOfBirth"
if rst.eof=false then rst.movelast
if rst.recordcount>0 then
'add code to do whatever - show otehr records, allow adds, etc
end if

You may get the same issues with the ' symbol - in which case you may need to use CHR(34) to create the quotes...

"SELECT Surname, DateOfBirth FROM MyTable WHERE Surname = " & Chr(34) & Me.Surname & Chr(34) & " AND DateOfBirth= Me.DateOfBirth"


SeeThru

http://www.synergyconnections.co.uk

 

[Remou]

Replace works, too, it is a standard solution:

strOK = Nz(DLookup("Surname", "TableOrQueryWithThisInfoName", "Surname = '" & Replace(Me.Surname,"'","''") & "' AND DateOfBirth=#" & Format(Me.DateOfBirth,"yyyy/mm/dd") & "#"), "")

 

[Dave177]

Thank you both very much!