In MS Access, how a control can displays a value based on a field selection using a combo box that displays a Field list?

Question

I have a table like this one :

This table represents a species (No = 3) and the number of individual for the last 6 years. (FYI, the table cannot be displayed differently.)

I created a Combo Box which displays the last 6 years (based on a field list).

What I have to do so that the turquoise rectangular automatically displays "31", for example, if the year "2014" is selected?

Answer 1

The question doesn't contain enough detail about the host form and other bound controls, so the following is just a pattern to follow.

Set the Control Source of the turquoise (i.e. cyan) control to something like

= DLookup("[" & comboboxYear.Value & "]", "TableName", "ELEMENT_ID=" & [ELEMENT_ID])

This assumes that there is a bound control named [ELEMENT_ID] on the form. Brackets wrap the ComboBox value to ensure the value is interpreted as a column name and not a number.