Text box to populate from combo box selection

[JSHEL]

This is probably really simple but it's driving me crazy.
The facts.
one table. fields are [Sitename] and [address]
On the form I have a combo box with a row source of [Sitename]
and a txt box that I want to show the corresponding address of the sitename afterupdate.
I have tried a few different ways and nothing works I even tried the sync of combo boxs tutorial but that pulls from two different tables and i can't seem to make it work with one. let alone a txt box.
thanks for the help.




Jshel

[pbaldy]

The simplest way would be to add the address field to the combo row source, then have a textbox with a control source of:

=ComboName.Column(1)

The column property is zero based, so 1 is the second column.

[JSHEL]

I knew it must have been something really simple. lol
Thanks a ton.
Jshel

[pbaldy]

Happy to help, and welcome to the site by the way!

[JSHEL]

Ok i guess i'm going to drag this out a bit more. The suggestion below works great for displaying the information but i also need it to save to the table. is there something i can add to the control source to preform both functions?

[pbaldy]

You probably shouldn't, but this type of thing in the after update event of the combo:

Me.BoundTextboxName = Me.ComboName.Column(1)

where BoundTextboxName is the name of a textbox bound to the field you want it saved to.

[JSHEL]

Why shouldn't I use this afterupdate command? I guess what i'm asking is what are the pro's and con's. And is there a better way to accomplish what i'm trying.

Thank you for your time.

Jshel

[pbaldy]

I meant you generally shouldn't store related data. Typically you only store the key value in related tables (let's say employee number), and you get the related information (employee name, address, etc) by joining the employee table to the related table in a query.

No problem at all using the after update event.

[jmitchelldueck]

Would there ever be any situation in which it is beneficial to store the auto-populated text box in the same table as the data you are selecting from? Or does a query work the best?

Thanks.

[pbaldy]

Sometimes it's appropriate. If I'm picking a part number for an invoice and the auto populated text is the price for that part, it makes sense to save the price with the invoice, as the price in the parts table will change over time.