VBA example - Microsoft Access: column chart


VBA chart-column chart

In this example, you will learn VBA code to show the number of customers by sex in column chart. When you select any province in a combo box, the number of customers living in that selected province will show in the column chart on a form. The form of program has a combo box(CboProvince), and a chart(Graph1). See the figure below:

 VBA example - Microsoft Access VBA Column Chart show the number of custombers by sex 

-When the form loads, the column chart shows the number of customers by sex in all provinces.(See the form above).

-If you want to select any province, click arrow down of combo box and select the province.

To have a form as the figure above, you have to create the form in Form Design. See the figure below:

VBA example - Microsoft Access VBA Column Chart Form Design 

- Drag and drop a combo box, and a chart to the form.

- When you drag and drop a chart on the form, you have to follow a few steps of Chart Wizard. The Chart Wizard will introduce you what you have to do to create a chart. There are only two fields that you have to select: Sex and Addr. For the chart you just create, it is not the same as the chart you see in design form above. It looks like the figure below:

VBA example - Microsoft Access VBA Create Column Chart  

Do not wonder! you will see it look the same as the example chart above when you view the form.

- Set Name properties of a combo box control:

Name: CboProvince

 

Then apply the VBA Code below:

  

Option Compare Database

Dim Rs As DAO.Recordset

 .........................................................................................................

Private Sub Form_Load()

    CboProvince.RowSourceType = "Value List"

    Set Rs = CurrentDb.OpenRecordset("SELECT DISTINCT Addr FROM TblCustomer", dbOpenDynaset)

    CboProvince.AddItem "All"

    While Not Rs.EOF

        CboProvince.AddItem Rs("Addr")

        Rs.MoveNext

    Wend

    Graph1.Requery

End Sub

 ...........................................................................................................

Private Sub CboProvince_Change()

    If CboProvince.Value = "All" Then

        Graph1.RowSource = "TRANSFORM Count(*) AS [Count] SELECT [Sex] FROM [TblCustomer] GROUP BY [Sex] PIVOT [Addr];"

        Graph1.Requery

    Else

        Graph1.RowSource = "TRANSFORM Count(*) AS [Count] SELECT [Sex] FROM [TblCustomer] WHERE Addr='" & CboProvince & "'  GROUP BY [Sex] PIVOT [Addr];"

        Graph1.Requery

    End If

End Sub

Remark: For the code in front of Graph1.RowSource, you can copy it from the Row Source property of Graph1 of Data tab. The code does not include WHERE clause. You have to add it by yourself.

VBA example - Microsoft Access VBA Column Chart copy code from rowsource 



Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.