BaldyWeb
Display Related Info From Another Table When a Selection is Made
I often see questions relating to displaying/saving info from related tables, usually called "autofill" or something along those lines. Generally the situation is one where you have two tables, lets say Customers and Orders. You have a bunch of information about the customer and you want to see it after you select a customer on your Orders form.
There are two general methods: displaying the related info, and actually saving it in the Orders table. It would typically be a violation of normalization rules to save the related info in the Orders table. You would save the key field from the Customer table in the Orders table, and use a query to get the related info.
If all you need to do is display the related info on your Orders form, you would use a combo box to display the available customers. The row source of that combo would include the additional fields you want to display, though they can be hidden in the combo itself. The row source of the combo might look like:
SELECT CustID, CustName, CustPhone FROM Customers
If you only wanted to see the name in the combo, you would hide the ID and Phone fields (column width of 0). Then on your Orders form to view the phone number of the selected customer, you'd have a textbox with a control source of:
=ComboName.Column(2)
The Column property is zero based, so 2 is the third column. When the user selects a customer, the textbox will automatically display the phone number, but won't save it to the Orders table.
If your situation is that rare one where saving the related info is necessary, you'd use the same combo. In the textbox, instead of the formula as the control source, you'd list the field in the Orders table where you wanted the phone number saved. In the after update event of the combo, you'd have code like this to copy the value over:
Me.TextboxName = Me.ComboName.Column(2)