Link to home
Start Free TrialLog in
Avatar of spandor
spandor

asked on

Display value according to selected value in another field

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?
Avatar of Paul Cook-Giles
Paul Cook-Giles
Flag of United States of America image

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).
Avatar of spandor
spandor

ASKER

Ok, that works. However, what you can recommend when I choose from combo and I have next 4 columns to match with appropriate data?
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)
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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.