Display value according to selected value in another field

spandor
spandor used Ask the Experts™
on
Hey,

I have a subform in my form. In this subform I have several columns. In first one I have a combo field filled with data from a table A. When I choose a particular value from this combo I'd like to display appropriate value in the next column in my subform as a text field. This value is also from table A. How I can obtain?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
The simplest way is to make the source of your combo box a two-column query that has the 'particular' and 'appropriate' values from table A.
In the OnExit event of the combo box, put

me.TextField = me.ComboBoxName.column(1)

That will set the TextField = the second column of the query (Access counts columns in combo boxes starting with 0).

Author

Commented:
Ok, that works. However, what you can recommend when I choose from combo and I have next 4 columns to match with appropriate data?
Paul Cook-GilesSenior Application Developer

Commented:
If you can build the combo source-query to have five columns, you can replicate the syntax to populate additional text fields from the query's columns:  

Here's the line that works now:
   me.TextField = me.ComboBoxName.column(1)

Add more lines beneath it:

me.AnotherField = me.ComboBoxName.column(2)
me.SomeField = me.ComboBoxName.column(3)
me.RandomField = me.ComboBoxName.column(4)
me.LastTextField = me.ComboBoxName.column(5)
Distinguished Expert 2017

Commented:
You can do this WITHOUT CODE.  Modify the RecordSource of the form to join to the lookup table.  Use a left join so that you will still see the main record even when the field the combo is bound to is empty.  That will allow you to bind controls on the form to the fields from table2.

WARNING - When you display lookup fields this way, they are updateable.  This is generally not desirable because people may make accidental changes thinking that they are changing just this record when in fact they are changing the "lookup" record in table2.  So, best practice is to sett the locked property for these controls to Yes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial