Why is my DLOOKUP returning only a partially correct record?

266 Views Asked by At

Alright. This is my first post ever so I'll try to be as detailed as I can. I have a Person_AfterUpdate() event on a Form named frm_Contact_Info, that is supposed to check a table, named "tbl_Contact_Info," for an existing record. If the record exists, then a message pops up stating that the record already exists, and to check the name. Then it asks if I am creating a new record with the existing name, or if the record that is returned is the same person. The problem I am having is that the DLookup shows the correct name, but does not return the correct phone number or address to my form.

Here is my code. I'm not super skilled at programming, but I tried to make it as understandable as possible. If you want screen shots, let me know. This is the first time I've actually tried to build a database, and I know my skills are not as great as some of yours. I've tried to research as much as I could to find the answer to this, but everything I've tried so far brings me back to this problem where the DLOOKUP is returning the correct NewPerson, but not the correct phone number or e-mail address. Please help!

Private Sub Person_AfterUpdate()

Dim NewPerson As String
Dim stLinkCriteria As String
Dim PersID As Integer

On Error GoTo E:

NewPerson = Me.Person.Value
stLinkCriteria = "[Person] = " & "'" & NewPerson & "'"

If Me.Person = DLookup("[Person]", "tbl_Contact_Info", stLinkCriteria) Then
    MsgBox "The name, " & NewPerson & ", has already been added to the database." _
    & vbCr & vbCr & "Please check the name and try again.", vbInformation, "Duplicate Information"
    Me.Undo
End If

PersID = DLookup("[ID]", "tbl_Contact_Info", stLinkCriteria)
Me.DataEntry = False
DoCmd.FindRecord PersID, , , , , acCurrent

Response = MsgBox("Are you adding a new record with the same name?", vbYesNo, "New or Existing Record")

If Response = vbYes Then
    DoCmd.GoToRecord , "", acNewRec, 1
    Me!Person.Value = NewPerson
Else
    Me.DataEntry = True
    DoCmd.GoToRecord , "", acNewRec, 1

End If
Exit Sub

E:
Me!Person.Value = NewPerson
End Sub`
1

There are 1 best solutions below

1
nicomp On

Perhaps you have multiple records with the same value in the attribute you are using as your lookup value?