Access: Using textbox in report, how to display reference a column from a query using a different textbox

I'm trying to making a report in access that uses a bunch of dynamic textboxes. Lets say that one specific text box called A displays the current year using the control source ="F" & Format(Date(),"yy") and another called B displays the year before ="F" & Format(Date()-360,"yy")

I have a query called MKTCAP with columns called F15 and F14 which are updated automatically and every year, another column is added whith the next consecutive year.

Under textboxes A and B, I have textboxes C and D where i refer to this query, but here i want to use textboxes A and B to access the specific column of the MKTCAP query.

I tried =[MKTCAP].[(A)] and many different combinations of that, and i didnt know how to word this question on google. Am i missing something? Is there a simpler way of doing this? Thanks for the help

Solution

Assuming that Text1 and Text2 are your two textboxes containing F14 and F15, and Text3 and Text4 are the other ones pulling from the query:

Set the RecordSource of the form as the query MKTCAP.

Put this in the Form_Load event:

Private Sub Form_Load()

    Me.Text3.ControlSource = Me.Text1
    Me.Text4.ControlSource = Me.Text2

End Sub

and then in the After_Update event of Text1 and Text2

    Me.Text3.ControlSource = Me.Text1

and

    Me.Text4.ControlSource = Me.Text2

respectively, to keep the rowsource updated should Text1 and Text2 change whilst the form is open.

Your method of finding last year's date is slightly unusual - is this intentional? On Dec 30th, for example, both years would be the same. Why not use

="F" & Format(Date()-360,"yy") - 1