Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

PIE Chart and live data on Form

Students' Exam Scores of five different Subjects are recorded in a Table: tbl_Students. A sample Table image is given below:

The name of the student, from the current record on the Form, is used as criteria in a Query, to filter the exam records of that student, as source data for the PIE Chart on the same Form. Sample Form image with the PIE Chart is given below:

When any one of the exam record, out of five subjects of a  particular student become current on the Form the PIE Chart should display the marks of all five subjects and percentage on Maximum Total Marks, on the same Form.  This is the tricky part; we cannot design a Graph Chart on the Form, while the Form is in normal view to filter the data in the query as source for the same Chart. 

We need three simple queries to filter and prepare the data for the Chart.  Besides that we need a temporary table to store the Total of all Obtainable Maximum  Marks (to calculate the percentage of total Obtained marks) on each subject for the chart (100 x 5 = 500). The image of the temporary table is given below:

We will divide the task in three parts, so that the procedure is easy to understand:

  1. Part-A: Create tbl_Students and tmp_MaxMarks.
  2. Part-B: Design Form frm_Students for tbl_Students and create three simple Queries to prepare data for the Chart.
  3. Part-C: Create a PIE Chart on frm_Students Form.


  1. Create a Table with the Structure, as shown in the first image on the top and name the table as tbl_Students.  You can ignore the two empty fields on the Table.  As you can see the table contains three students’ exam scores of five subjects, out of hundred each.
  2. Key-in those sample data into tbl_Students Table.
  3. Create a small table with two fields, with the structure shown on the above image and save it with the name tbl_MaxMarks.
  4. Create a single record with the sample data, shown on the image above.


  1. Design a Form in Column format, using tbl_Students, leaving enough space at the right side to create the PIE Chart. You can use the Form Wizard to create the Form quickly and save the Form with the name frm_Students.

    We will create three Queries, before we proceed with some more work on the Form.

  2. Create the following Queries by copying, pasting the SQL String into new Query’s SQL Editing Window and saving it with the name as indicated:

    Query-1: StudentsQ

    SELECT [Student] & " " & [Subject] AS [Desc], tbl_Students.Marks
    FROM tbl_Students
    WHERE (((tbl_Students.Student)=[forms]![frm_Students]![student]));

    Query-2: MaxMarksQ

    SELECT tmp_MaxMarks.Desc, [MaxMarks]-DSum("Marks","StudentsQ") AS Max_Marks
    FROM tmp_MaxMarks;

    Query-3: UnionPieChart

    SELECT StudentsQ.* FROM StudentsQ
    UNION ALL SELECT MaxMarksQ.* FROM MaxMarksQ;
  3. Open frm_Students in Design View.
  4. Create two Text Boxes below the existing fields.
  5. Click on the first Text box and press F4 to display the Property Sheet.
  6. Change the Name Property value to Obtained.
  7. Type the following expression into the Control Source Property:
  8. Change the Caption Property Value, of the Child Label of the Textbox, to Total Obtained:.
  9. Click on the second Textbox to select it.
  10. Write the following expression into the control Source Property:
  11. Change the Format Property Value to Percentage.
  12. Change Caption Property value of the Child Label of the Textbox to % on Total:.
  13. Save the Form and open it in Normal View.

Let us run a review of what we did so far.

We have created the main table tbl_Students and designed the Form frm_Students, using tbl_Students.

While the frm_Students is active, the StudentsQ filters the data using the current student's name (say John) on the Form as criteria.

Second Query (MaxMarksQ) Sum ups the total obtained marks from StudentsQ. Subtracts it from Maximum Marks of all five subjects (500) from tmp_MaxMarks. The difference of these two values,if any, is used for calculating the percentage of marks the student lost. In other words if this figure on the PIE chart shows as 10% then the student could only obtain 90% aggregate marks out of 500.

Third Query: UnionPieChart combines the Data of StudentsQ and MaxMarksQ queries and used as Data Source for the PIE Chart.


Our PIE Chart should be created on the frm_Students, but the Form should be kept open in normal view to filter the data in StudentsQ for the chart.  We can create the Chart only when the form is in design view. I hope you can see now why we need a little trick to pull this off.

Now, the frm_Students Form is in open state and the exam data of John (the first student on the Form) is filtered and available in StudentsQ. We should go through the following steps to create the PIE Chart on the frm_Students.

  1. While keeping the frm_Students in Form View, open a new Form in Design View.
  2. Enable Use Control Wizard Button on the Toolbar above and select Insert Chart Tool from the Controls group.
  3. Draw a Chart in rectangular shape, large enough to show the formatting (like Value Labels, Title etc.) we are going to do, like the sample image given above.
  4. Select Queries from the View group from the Wizard control and select UnionPieChart from the displayed query list.
  5. Click Next to proceed to the next screen and click on >> button to select all the displayed fields for the chart.
  6. Click on the PIE Chart Icon to select it and click Next button twice then type the following as title on the Title control:

    Max. Marks Each=100, 500 Total

  7. Select No, don't display a Legend Radio-button then Click Finish.
  8. Double-Click on the PIE Chart to change it into Edit Mode.
  9. Right-Click on an empty area of the Chart to display the context menu.
  10. Select Chart Options from the displayed Menu.
  11. Select the Data labels Tab and put Check Marks in Category Name, Values and Percentage.
  12. Click OK to close the control.

    We will reduce the size of the PIE slice labels to a smaller font size.

  13. Click on one of the PIE slice Labels. this action will select all the Labels together.
  14. Change the Font size to something like: 12 points, using the Font/Size Tool above. 

    Completed design of PIE Chart is given below.

  15. Click outside the Chart Object on the Form, to exit from Chart-Edit Mode, but the chart will be still in selected state.
  16. Right-Click on the chart, to display the shortcut menu, and click Copy to copy the Chart-Object on the Clip-Board.
  17. You may save the Form with a name and close it, if you would like to keep it safe. But, we don’t need it any more.
  18. Now, Change the frm_Students into Design View.
  19. Click somewhere on the Detail Section of the Form to make Detail Section current.
  20. Right-click on the Detail Section and select Paste from the displayed menu.
  21. Drag the PIE Chart and place it to the right side of data fields.
  22. Click on left top corner of the Form and display the Property Sheet (F4) of the Form.
  23. Select the Event Tab of the Property Sheet and click on the On Current property.
  24. Select Event Procedure from the drop-down list and click on the build (. . .) button to open the VBA editing window.
  25. Write the following statement in the middle of the Form_Current() Event Procedure:

    When completed the procedure will look like the following:

    Private Sub Form_Current()
    End Sub

    When you move the records on the Form, from one to the other,the Form_Current() event procedure will update the recordset with the change on the StudentsQ as well.

  26. Close the VBA editing window.
  27. Save the frm_Students Form and open it in Normal View.

The first five records on the Form belongs to the student John. If you move one record at a time you will not find any difference on the Chart up to the fifth record, because values of all the five records are shown on the PIE Chart. The sixth to tenth records belongs to the second student and 11th record onwards belongs to the third student. You may type 6 or 11 on the Record Navigation Control directly to quickly display other students marks on PIE Chart.

  1. MS-Access and Graph Charts
  2. MS-Access and Graph Charts-2
  3. Working With Chart Object in VBA
  4. Column Chart and VBA
  5. Pie Chart Object and VBA
  6. Missing Lines in Line-Chart
  7. Pie Chart and Live Data on Form
  8. Scaling Chart Object
  9. Cross-Tab Union Queries for Chart


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...


Blog Archive

Recent Posts