To Search for a Record by a field and see if there is duplicate name

[powelly]

Sorry for the title did not know what to call this.

I have 1 "All" table with the following field names

ID --> Autonumber and Primary Key
Firstname --> Text
Surname --> Text
Doctor --> Text
Diagnosis --> Text
Notes --> Text
DOB --> Date/Time

I have a form "New" to add new patients to the data base
I would like the DOB to find any duplicated data.

which i have working with this code

If Nz(DLookup("[DOB]", "ALL", "[DOB]"), Me.DOB) <> Me.DOB Then GoTo NO Else GoTo YES

That works great but i would like to, if a duplicate is found to
see if DOB "duplicate" has the same FirstName
and Surname and then warn the user that the patient already
exist but if it doesn`t the carry on.

Thank you in advanced sorry for carrying on :-)

[NeoPa]

Try this code instead of yours :

Dim strWhere As String
strWhere = "(([DOB]=" & Format(Me!DOB,"\#m/d/yyyy\#") & ") AND " & _
           "([Firstname]=' & Me!Firstname & "') AND " & _
           "([Surname]=' & Me!Surname & "'))"
If IsNull(DLookup("[DOB]", "ALL", strWhere)) Then
    'All good - continue
Else
    Call MsgBox("Clash found etc...")
    'Don't process record
End If

BTW Your question was actually quite understandable and the title was quite clear too.

[powelly]

Thank you

I had to change code slightly it`s now

Private Sub DOB_BeforeUpdate(Cancel As Integer)
 
Dim strWhere As String
strWhere = "([DOB]=" & Format(Me!DOB, "\#m/d/yyyy\#") & ") AND " & _
           "([Firstname]=" & Me!FirstName & ") AND " & _
           "([Surname]=" & Me!Surname & ")"
If IsNull(DLookup("[DOB]", "ALL", strWhere)) Then
    'All good - continue
Else
    Call MsgBox("Clash found etc...")
    'Don't process record
End If

I keep getting error

Run-time error '64479':

The expression you entered as a query parameter produced this error:'The object doesn`t contain the automation object 'test."

I`m using access 97 if that makes any different

[NeoPa]

I'm afraid your changes (to my buggy code :() were not quite right.
I'm not sure why you got the message you did get but let's start off with some properly fixed code and see where that gets us. BTW Now I know you're using this in a BeforeUpdate event procedure, I can fill it out a bit more too.

Private Sub DOB_BeforeUpdate(Cancel As Integer)
    Dim strWhere As String
 
    strWhere = "(([DOB]=" & Format(Me!DOB,"\#m/d/yyyy\#") & ") AND " & _
               "([Firstname]='" & Me!Firstname & "') AND " & _
               "([Surname]='" & Me!Surname & "'))"
    If Not IsNull(DLookup("[DOB]", "ALL", strWhere)) Then
        Call MsgBox("Clash found etc...")
        Cancel = True
    End If
End Sub

Try this out.

[powelly]

Sorry about that access was giving me errors in vb with " and () in places don`t know why but when i used this code

Private Sub DOB_BeforeUpdate(Cancel As Integer)
    Dim strWhere As String
 
    strWhere = "(([DOB]=" & Format(Me!DOB,"\#m/d/yyyy\#") & ") AND " & _
               "([Firstname]='" & Me!Firstname & "') AND " & _
               "([Surname]='" & Me!Surname & "'))"
    If Not IsNull(DLookup("[DOB]", "ALL", strWhere)) Then
        Call MsgBox("Clash found etc...")
        Cancel = True
    End If
End Sub

It worked BEAUTIFULY Just as i wanted it to.

So :-) Thank you NeoPa again very much you have helped me with other Problems before as well.

Me so HAPPY :-) :-) :-) :-) :-) :-) :-)

Cheers ROB

[NeoPa]

I'm sorry to say that my original code was posted with some critical quotes missing :embarrassed:
I'm pleased it's working for you now :)

[powelly]

Thats ok every one has there day.THANK YOU again