Lookup data between Form and Table

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

I have a form on which I am using a Combo Box to choose a CustomerID code
and I want to be able to bring through the Customer's details (names,
addresses, etc). I have used the following as my control source in one of
the fields, however regardless of which Customer account I choose I still
have the first record displayed on the output of the form


=DLookUp("[FirstName]","Customers","[CustomerID] =
Forms![Invoices]![CustomerID]") & " " &
DLookUp("[LastName]","Customers","[CustomerID] =
Forms![Invoices]![CustomerID]")

Any and all advice on where I am going wrong gratefully received.

Thanks

Jean
 
You need the dlookups to use the CustomerID (from the form) as a variable;
currently it's part of your hard-wired string, and since it doesn't evaluate
to a real record, the dlookups return the first record. Try:

=DLookUp("[FirstName]","Customers","[CustomerID] = " &
Forms![Invoices]![CustomerID] ) & " " &
DLookUp("[LastName]","Customers","[CustomerID] = " &
Forms![Invoices]![CustomerID])

That will work if CustomerID is a number; if it's a string you'll need
delimiters for the string, thus:
=DLookUp("[FirstName]","Customers","[CustomerID] = '" &
Forms![Invoices]![CustomerID] & "'") & " " &
DLookUp("[LastName]","Customers","[CustomerID] = '" &
Forms![Invoices]![CustomerID] & "'")

HTH,

Rob
 
Thank you Rob

I would have really struggled to get this far. I appreciate your help.

Jean
 
Back
Top