help with lookup

[lab18]

How can i acomplish this?
I have a form to enter customer information, first and last name, address etc.
I want that when i enter the last and first name, if the customer is allready in, to auto fill the rest of the information, but if its not to then let me fill all of it. Now keep in mind the first and last name are 2 separate controls on the form and separate fields on the table. I tried using the combobox but I couldnt get it to work the way i wanted it.
This probably very simple, but Im new with access, and dont know VB.
Thanks in advance

 

[aqif]

Hi

I presume that you must have customer Id or any unique identity of customer. Write a code like this on AfterUpdate event of you last name text box

Dim StrId, StrAddress,StrField1, StrField2 as String

StrId= =Nz(DLookUp("CustomerId","TblCustomers","CustFName= Forms!MyForm!TxtCustFName And CustLname = Forms!MyForm!TxtCustFName"),0)

' Will return if no matching cutomer will be found

If StrId<>0 then

StrAddress=Nz(DLookUp("CustomerAddress","TblCustomers","CustFName= Forms!MyForm!TxtCustFName And CustLname = Forms!MyForm!TxtCustFName"),"N/A")

Me.TxtAddress=StrAddress

StrField1=Nz(DLookUp("Field1","TblCustomers","CustFName= Forms!MyForm!TxtCustFName And CustLname = Forms!MyForm!TxtCustFName"),"N/A")

Elseif StrId=0 then
Msgbox("No Matching Information is Found")

End If

Please let me know if you need more help.

Cheers!
Aqif

 

[aqif]

Correction

Please correct the Field Name as CustLname=Forms!MyForm!TxtCustLName instead of CustLName=Forms!MyForm!TxtCustFName in the code below


Nz(DLookUp("CustomerAddress","TblCustomers","CustFName= Forms!MyForm!TxtCustFName And CustLname = Forms!MyForm!TxtCustFName"),"N/A")