Link fields on a form depending on what is chosen from a dropdown
Posted on 2006-11-21
I'm sorry, I don't really know how to word the title for this (let alone google it), but here's what I'm trying to do:
On a form, someone chooses a customer ID from a combo box that is bound on the Customer ID field of tblIPCustomer, and then depending on what is chosen, will populate the name and address to other textboxes on the same form and also populate to the same table that everything on the form will populate to: UNEP.
I thought that I would be able to add a query to the Row Source of the combo box that I want to update so that you could choose the one customer name that the query would return from the dropdown. I used this in the Row Source:
SELECT tblIP_Customer.Customer_Name, tblIP_Customer.Address FROM tblIP_Customer WHERE (((tblIP_Customer.Customer_id) Like Forms!frm_Add_UNEP_Order![Customer ID]));
This just gives me a blank field. When I run the query on it's own, I still just get a blank record. I thought maybe it's because I hadn't saved the record yet, but even after that, it still just gives a blank record. However, if I change the query to always find whatever customer ID that I have in the dropdown, it finds it.
Another option I can think of to accomplish the same thing is to choose a customer ID from the dropdown and just have it populate a name and address box and then later copy those values to the UNEP table with an On Close or something. (I realize that the fact that the customer name and the customer ID are both in both tables makes the database horribly un-normalized, but there's nothing I can do about that. I inherited this when I came to work here.)
If anyone has any suggestions to make what I've already thought of work, or a totally different way to do it, I'm all ears!