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`
Perhaps you have multiple records with the same value in the attribute you are using as your lookup value?