Solved Automatically update a chart based on a combobox

[zelarra821]

I have a bar graph in a form that you can change depending on the year you select in a drop-down. This graphic refers to the range of pages. You can see it in the image that I have attached.

Right now it shows the ones that have data, because this is what I indicated in the query that I created for the graph.

In the "After updating" event of the combobox, I have the following code:

Private Sub Año_AfterUpdate()
    Me.Grafica.RowSource = "SELECT CLibrosLeidosPorRangoDePaginas.Año, CLibrosLeidosPorRangoDePaginas.[1 a 99], CLibrosLeidosPorRangoDePaginas.[100 a 199], CLibrosLeidosPorRangoDePaginas.[200 a 299], CLibrosLeidosPorRangoDePaginas.[300 a 399], CLibrosLeidosPorRangoDePaginas.[400 a 499], CLibrosLeidosPorRangoDePaginas.[500 a 599], CLibrosLeidosPorRangoDePaginas.[600 a 699], CLibrosLeidosPorRangoDePaginas.[700 a 799], CLibrosLeidosPorRangoDePaginas.[800 a 899], CLibrosLeidosPorRangoDePaginas.[900 a 999] " _
                            & " FROM CLibrosLeidosPorRangoDePaginas " _
                            & "GROUP BY CLibrosLeidosPorRangoDePaginas.Año, CLibrosLeidosPorRangoDePaginas.[1 a 99], CLibrosLeidosPorRangoDePaginas.[100 a 199], CLibrosLeidosPorRangoDePaginas.[200 a 299], CLibrosLeidosPorRangoDePaginas.[300 a 399], CLibrosLeidosPorRangoDePaginas.[400 a 499], CLibrosLeidosPorRangoDePaginas.[500 a 599], CLibrosLeidosPorRangoDePaginas.[600 a 699], CLibrosLeidosPorRangoDePaginas.[700 a 799], CLibrosLeidosPorRangoDePaginas.[800 a 899], CLibrosLeidosPorRangoDePaginas.[900 a 999] " _
                            & "HAVING (((CLibrosLeidosPorRangoDePaginas.Año) = [Formularios]![FLibrosLeidosPorRangoDePaginas]![Año])) " _
                            & "ORDER BY CLibrosLeidosPorRangoDePaginas.Año DESC"
    Me.Grafica.Requery
End Sub

If, later, a new range would appear, it would not appear, and you would have to add it manually.

What I would like to achieve is something I do when I select "Todos los años", that I have this code:

Me.Grafica.RowSource = "CLibrosLeidosPorRangoDePaginasTotal"

Which allows me to add ranges if more are created.

I do not know if I can explain. Thank you.

 

[JHB]

You need to use a crosstab query.

 

[zelarra821]

You need to use a crosstab query.

Solved. My problem was that I did not recognize the Year field. I have declared a variable to capture the value of the year, and I have put that variable directly, and it works.

Thank you.