DLookup working too good...

[DraGo150]

Thank you for any help in advance. I have a form that the employees fill in the first, last, and DOB of a client and I have a the below script to look at the table to make sure that the name has not already been entered. It works too good. It is finding names that haven't been entered yet. There are names that are close, so that is why i added the DOB to the search to try and define the search. But it is still finding false results. Can anyone tell me what I am doing wrong. Thank you.



Private Sub DOB_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[FirstName]", "tbl_Client", "[FirstName]='" & Me![FirstName] & "'")) And Not IsNull(DLookup("[LastName]", "tbl_Client", "[LastName]='" & Me![LastName] & "'")) And Not IsNull(DLookup("[DOB]", "tbl_Client", "[DOB]=#" & Me![DOB] & "#")) Then


MsgBox "Defendents Name has already been entered into the database.", vbOKOnly & vbCritical, "Defendent Already Exists"
Me.Undo

Dim strDocName As String
Dim strDocNam As String

strDocName = "rpt_ClientSearch"
DoCmd.OpenReport strDocName, acViewReport

strDocNam = "frm_Client"
DoCmd.Close acForm, strDocNam, acSaveNo


End If
End Sub

 

[PHV]

Use a single DLookUp:
If Not IsNull(DLookup("FirstName", "tbl_Client", "FirstName='" & Me![FirstName] & "' And LastName='" & Me![LastName] & "' And DOB=#" & Me![DOB] & "#")) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

DraGo150,
Please consider using the code TGML tag rather than non-contrasting colors. You choice of green text is a bit difficult to read.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I should have added your example:

Private Sub DOB_BeforeUpdate(Cancel As Integer)
  If Not IsNull(DLookup("[FirstName]", "tbl_Client", "[FirstName]='" & Me![FirstName] & "'")) And _
      Not IsNull(DLookup("[LastName]", "tbl_Client", "[LastName]='" & Me![LastName] & "'")) And _
      Not IsNull(DLookup("[DOB]", "tbl_Client", "[DOB]=#" & Me![DOB] & "#")) Then

    MsgBox "Defendents Name has already been entered into the database.", vbOKOnly & vbCritical, "Defendent Already Exists"
    Me.Undo

    Dim strDocName As String
    Dim strDocNam As String

    strDocName = "rpt_ClientSearch"
    DoCmd.OpenReport strDocName, acViewReport

    strDocNam = "frm_Client"
    DoCmd.Close acForm, strDocNam, acSaveNo
  End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[DraGo150]

Thank you both for you resonse.

I tried both options and it is still saying that the name is in the table. I am trying to enter the name of Anthony Reyes, and below is a copy of my sorted table with all of the R's that are close. Is there anyother way of checking for records in a table other than DLookup before update?


ID FirstName MiddleInt LastName
948 ERIC CHARLES REED
950 RAYMOND REED
1437 RAYMOND RICHARD REED JR
249 CARLOS REYES
952 HAROLD REYES
600 JORGE REYES LOPEZ
1100 RYAN NATHAN REYNOLDS

 

[PHV]

You really tried my suggestion of a SINGLE DLookUp with 3 combined criteria ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DraGo150]

PHV,

I did try it, but now it is working. The only thing I can think of is that it did save when I closed it. I am going to have the employee's try some of their other trouble clients and I will get back to you witht the results.

Thank you again.

 

[MajP]

Is there another way of checking for records in a table other than DLookup before update?

This is basically doing the same thing but is easier to implement, avoids typos, and easier to error check.
Build a query "qrySearch". Then in the query use the form fields as criteria. This is easy because you can use the expression builder to do everything. It is all browse and click.
Instead of a dlookup use a dcount, and check if it returns 1 or more records.
if dcount("*","qrySearch") > 0 then...
This is nice for error checking because you can type known values in your form and open the query and see if it is returning the correct record.