On click change chart query in ms access

Question

In my ms access project, I have one table CustomerT and 3 columns 2012 2013 2014. I have created a form named TestForm which have a graph named MyGraph and a combobox named SelectYear. In my graph row source I have a query named qGraph

SELECT CustomerT.[2012], CustomerT.[2013], CustomerT.[2014] 
FROM CustomerT;

sample data of the query below

2012     2013  2014
 2.344   3.223  7.11
 5.23    23.27  21.23
 3.234   12.45  67.23
 4.235   7.234  53.56

When I open the form I get a graph as output.

Also what I want to do is that, if I select a year out of 2013,2014 or 2012 from the combobox I want to change the query as below

SELECT CustomerT.[2012]  FROM CustomerT;

CustomerT.[2012] will change according to the selected year and also this will change the graph. I want to do this using vba code builder. I was thinking something like this

    Private Sub SelectYear_Click()

    'code with a query to change the graph


With Me.MyGraph.Axes(1) 
      .MaximumScale = 2015
      .MinimumScale = 2010
   End With 'X-Axis
    End Sub

How can I implement this in ms access?

Answer 1

I'm not sure what you want to do with this table but having one column per year is not a good design in my opinion.

To answer your question, you can change the RecordSource property of the form after updating the combobox:

Private Sub SelectYear_AfterUpdate()

    Dim strRecordSource As String

    strRecordSource = "SELECT CustomerT.[" & Me.SelectYear & "]  FROM CustomerT;"

    Me.RecordSource = strRecordSource

End Sub

I didn't test, but I think it should work. You can also do the same thing when you open the form to set the default RecordSource with all the columns:

strRecordSource = "SELECT * FROM CustomerT;"