How do I extract multiple fields when using a combo box?

[Sugada]

Here's the problem -- any help is greatly appreciated!!

For example,
I have one table that has the products:

ID Name Cost
1 Toy $1
2 Game $5

I have a second table with customers in it. There are fields in the customer table that have bio information, but other fields, ID NAME and Cost that exactly match those from the products table.

I have a combo box whereby I can click the ID or Name or Cost field from the customer table(form) and bring up the data from the corresponding field in the products table.

Here's the question:
Is there a way for me to click on the ID field in the customer table, and have the program fill in the other appropriate field. Thus, instead of clicking on all three ID, Name, Cost to fill in the info, I can click on ONE of these fields in the customer table, and the other fields will fill in automatically.

Again, I hope this isn't too trivial and that I've explained myself ok. Thanks in advance for any help.

Thanks, Sugada

 

[hsitz]

If the ID determines the name and cost of the item, then it's bad database design to actually have fields in the referencing table for those items. You can always display them by just doing some sort of lookup into the foreign table using the ID.

To show them on a form, one easy way is to set up textboxes, which will not be editable, with the following control sources:

the [nameofforeigntable] will be whatever table you're getting the recordsource of the combobox from.

For the Name textbox:

=dlookup("[namefield]", "[nameofforeigntable]", "ID = " & me.[nameofyourcomboboxx])

For the Cost textbox:

=dlookup("[costfield]", "[nameofforeigntable]", "ID = " & me.[nameofyourcomboboxx])


When configured this way these textboxes should automatically readjust whenever you change the comboboxvalue. -- Herb

 

[Sugada]

Thanks Herb. I'm a little confused and if I may recap by using my situation instead of the post I found and copied.

I'm not one for reinventing the wheel, so while searching for a solution to my problem, I found a posting that was very similar to my own. Unfortunately, it had no responses. I just cut and pasted it into my own posting.

Anyway,

I have a table that has Branch and employees. I simply want to select the branch from a combo box, which I already have working just great.

The next field is employees. I only want the combo box to list employees with a matching branch that I just selected.

From reading your post, where does the =dlookup go? In the event on the employee properties? And under what? After_update? Please clarify using my situation.

Thanks, Sugada