Displaying from multiple recordsources

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to make a pretty simple form, but I'm having some trouble. Using
Access 2000. I have a combo box which displays a list of vendor names from a
vendor information table, and a bunch of fields for data entry.

My problem is that I want the form to display some of the information for
the vendor information table when a vendor name is selected. I can't get that
part to work. I'd appreciate any instructions that anyone could give me on
this. I've checked the recordsources, and they're right. Yesterday, I had it
working so that it displayed the vendor information, but didn't update it if
you changed the vendor name. Of course, I didn't touch the database, but now
when I opened it this morning, it won't even do that.

Thanks so much for your help!
 
There are several possible approaches.

Presumably the RowSource for this combo is a query that draws fields from
the Vendor table. If so, you can bring in the extra fields by adding some
hidden columns to the combo, and then referring to them in your text boxes.

For example, if the RowSource is this query:
SELECT VendorID, VendorName, VendorAddress, VendorCity
FROM tblVendor ORDER BY VendorName;
and you set these properties for the combo:
Column Count: 4
Column Widths: 0";2";0";0"
Name VendorID
then you show the VendorAddress in a text box that has control source set
to:
=[VendorID].Column(2)
and the VendorCity with:
=[VendorID].Column(3)
Note that Column() is a zero-based property, i.e. the first column is 0, the
2nd is 1, and so on.

If that approach is not suitable, you could use DLookup() to read the name
from the table. The Control Source of the text box would be something like
this:
=DLookup("VendorAddress", "tblVendor", "VendorID = " & Nz([VendorID],0))
For help with how to supply the arguments for DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html
 
Back
Top