asked on

Filling in a text box automatically based on a selection in a previous combo box

I have 5 fields in a table: Consignee (to), Consignee street, Consignee city, Consignee state and Consignee zip. All the information for each record has been entered into the table. I then created a main table and created a field called Consignnee (to) in it and made it a combo box that points back the Consignee (to) field in the first table so that i have a drop down box for users to select from instead of typing everytime. When a user selects a name from the Consignee (to) box on the form i need the other 4 fileds to be filled in automatically based on their selection in the Consignee (to) box. I have the field in the table set to column 1 as containing the data for that field and the column count set to 5. On the form i have all the fields showing and in Consignee Street i have the control source set to me.consignee_to_.column(2). When i open the form and select an answer in the Consignee (to) the Consignee Street just shows Name# in the field and not the address.

Dale Fye

If you use a query or the entire Consignee table as the rowsource for your combo box, then you can use the AfterUpdate event of that combo to display the other information. When I do this, I generally set the Locked property of those textboxes to Yes, to prevent the use from thinking they can be changed. if the SQL looks like:

SELECT Consignee, [Consignee street], [Consignee city], [Consignee state], [Consignee zip]
FROM tbl_Consignees

Then the code would look something like:

Private sub cbo_Consignee_AfterUpdate

me.txt_ConsigneeStreet = me.cbo_Consignee.column(1)
me.txt_ConsigneeCity = me.cbo_Consignee.column(2)
me.txt_ConsigneeState = me.cbo_Consignee.column(3)
me.txt_ConsigneeZip = me.cbo_Consignee.column(4)

End Sub

ASKER CERTIFIED SOLUTION

ramrom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ramrom

As you might guess I was perfecting my reply while fyed was perfecting his. So you have 2 similar answers. Have fun with it.

Dale Fye

Really?

Answers were identical, one was submitted 10 minutes before the other.

AisinAuto

ASKER

i chose the second one because the frst also had a query in it which i did not use. I did only what the second response said and it worked. If you want to split it evenly that is fine.