VBA example - Microsoft Access: pie chart


VBA chart-pie chart

In this example, you will learn VBA code to show conditionally the quantity of products in pie chart. When you select any quantity in a combo box and click the operator, the pie chart of product quantities that meet the selected criteria or condition displays on a form. The form of program has a option group(GroupCriteria), a combo box(CboQuantity), a command button(Filter), and a chart(PieChart) . See the figure below:

 VBA example - Microsoft Access VBA Pie Chart show the quantity of products 

-When the form loads, the pie chart shows the quantities of all products.(See the form above).

-If you want the pie chart to show the quantity of product equal to the selected quantity in the combo box, click equal to operator and select quantity then click filter command button.

-If you want the pie chart to show the quantity of product greater than the selected quantity in the combo box, click greater than operator and select quantity then click filter command button.

-If you want the pie chart to show the quantity of product less than the selected quantity in the combo box, click less than operator and select quantity then click filter command button.

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 Pie Chart Form Design 

- Drag and drop an option group, a combo box, a command button, and a chart to the form.

- The option group must be created with Control Wizard.

- 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: Product name and Quantity. 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 Pie Chart  

To make it looks the same as the example chart above:

- Double-click on the pie chart--> Right click on it--> Chart Options...

- Click Data Label Tap

- Check Category Name, Value, and select New line in front of Separators--> Ok

- Set Name propertie of  an option group, a combo box, and a chart:

Name: GroupCriteria

Name: CboQuantity

Name: PieChart

- Set Name and Caption properties of  a command button:

 Name: CmdFilter

Caption: Filter

 

Then apply the VBA Code below:

 
Private Sub Form_Load()
    CboQuantity.RowSource = "SELECT DISTINCT Quantity FROM TblProduct"
    PieChart.Requery
End Sub
 ....................................................................................................................
Private Sub CmdFilter_Click()
    If CboQuantity <> "" Then
        If GroupCriteria.Value = 1 Then
            PieChart.RowSource = "SELECT [ProductName],Sum([Quantity]) AS       [SumOfQuantity] FROM [TblProduct]   WHERE Quantity > " & CboQuantity.Value & " GROUP BY [ProductName];"
            PieChart.Requery
        ElseIf GroupCriteria.Value = 2 Then
            PieChart.RowSource = "SELECT [ProductName],Sum([Quantity]) AS [SumOfQuantity] FROM [TblProduct]   WHERE Quantity = " & CboQuantity.Value & " GROUP BY [ProductName];"
            PieChart.Requery
        ElseIf GroupCriteria.Value = 3 Then
            PieChart.RowSource = "SELECT [ProductName],Sum([Quantity]) AS [SumOfQuantity] FROM [TblProduct]   WHERE Quantity < " & CboQuantity.Value & " GROUP BY [ProductName];"
            PieChart.Requery
        End If
    Else
        MsgBox "Please select quantity of products.", vbInformation
        CboQuantity.SetFocus
    End If
End Sub

  

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

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



Comments

hector comment

 hector

asdas


2017-09-17
Arpit Kesharwani comment

 Arpit Kesharwani

Thanks a lot


2017-07-30
seo comment

 seo

Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword....wait there's even more Now what if i told you there was a simple Wordpress plugin that does all the On-Page SEO, and automatically for you? That's right AUTOMATICALLY, just watch this 4minute video for more information at. <a href="http://www.SEORankingLinks.us">Seo Plugin</a>
seo http://www.SEORankingLinks.us/


2016-07-16



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.