Make combo box record as table field name , MS Access

Question

I have combo box in form, i want to have it's drop down value to be Table fields name.Table name is tblCap, and the field are Year1,Year2 and Year3.I want that in the Combo box to have dropdown list as Year1,Year2,Year3 and when that year is selected it should display related field in subform.

Any help would be appreciated.

Thank you.

Answer 1

Update after comments.

Once the RowSource has been set-up as per @Gustav answer, in order to hide/unhide the TextBox controls according to the value of ComboBox you need to set their Visible property to True/False.

On the Events tab set the AfterUpdate of your ComboBox control to [Event Procedure] and on the code behind file set the following:

Private Sub YourComboControlName_AfterUpdate()
    With Me
        Select Case .YourComboBoxName.Value
            Case "Year1":
                With .YourSubformName.Form
                    .Your2000TextBoxControlName.ColumnHidden = false 
                    .Your2001TextBoxControlName.ColumnHidden = true
                    .Your2002TextBoxControlName.ColumnHidden = true 
                End With

            Case "Year2":
                With .YourSubformName.Form
                    .Your2000TextBoxControlName.ColumnHidden = true
                    .Your2001TextBoxControlName.ColumnHidden = false
                    .Your2002TextBoxControlName.ColumnHidden = true 
                End With

            Case "Year3":
                With .YourSubformName.Form
                    .Your2000TextBoxControlName.ColumnHidden = true 
                    .Your2001TextBoxControlName.ColumnHidden = true 
                    .Your2002TextBoxControlName.ColumnHidden= false 
                End With
        End Select
    End With
End Sub