Search fields and show record code (1 Viewer)

[mrssevans]

I am working on some code that will check for duplicates after update, but what I am wanting to do is if there is a duplicate to pull up that record. I really don't want to do this with a combo box or list box because my users won't check for duplicates, they are sales people in a hurry. My form is based on a table called "customer TBL" and the form is called "customer FRM". Here is the code that I have so far:

Private Sub HomePhone_AfterUpdate()
Dim Answer As Variant

If DCount("[HomePhone]", "customer tbl", "[homephone] = [Forms]![customer frm]![homephone]") = 1 Then
Answer = MsgBox("This Customer is already in the Database. OK to find existing record", vbOK, "Duplicate!")
If Answer = vbOK Then Cancel = True

Exit Sub
End If
End Sub

It works great, I just need to expand on it. Thanks for the help.

 

[BukHix]

You will want to change your vbOk to vbYesNo and then put something like this:

If Answer = vbYes Then
        Pull the recordset (maybe with a Dlookup)
Else
        Do something else (maybe exit sub)
End If

 

[mrssevans]

Yes, that is exactly what I am looking for. Please excuse my lack of vb knowlege, but could you help me with pull the record set? That is where I am stuck. Thanks a bunch.

 

[BukHix]

The syntax for a Dlookup is similar to that of a Dcount.

txtPhoneNumber = ("[HomePhone]", "customer tbl", "[homephone] = [Forms]![customer frm]![homephone]")

Although I would do it like this:

txtPhoneNumber = DLookup("[HomePhone]", "customer tbl", "[homephone]= '" & Me!homephone & "'")

txtPhoneNumber is the name of the text box you want to fill in with the data you are looking up.

 

[mrssevans]

Thanks, I will give this a try and see if I can get it to work. I may require a bit more assistance because this looks like some code that I tried yesterday and failed everytime. But maybe my luck has changed and it will work perfectly. Thanks again.

 

[mrssevans]

Well, this works just like it is supposed to but here is the problem. I added an unbound field and called it txtphonenumber and ran the code. If you say yes on the dialog box all it does is fill in the txtphonenumber with the same number. Is there a way for it to pull up the whole record of the matching phone number? THanks so much for your help.

 

[BukHix]

Well, for every you record you want to get you just ask for it by name.

txtAddress = DLookup("[Address]", "customer tbl", "[homephone]= '" & Me!homephone & "'")

txtZip = DLookup("[Zip]", "customer tbl", "[homephone]= '" & Me!homephone & "'")

etc...


____________________________________________________________

The ever increasingly inaccurately named UBB Board List for PC Support.
____________________________________________________________

 

[David R]

Can you explain what went wrong with the suggestions in http://www.access-programmers.co.uk/ubb/Forum7/HTML/002711.html ? There's no sense in starting new solutions until we know what was wrong with the old ones.

David R