How do I convert VBA code on an Access 2007 form to display results in pie graph?

Posted on 2009-04-23
Last Modified: 2013-12-25

It's been awhile since I've used VBA.  The code attached calculates totals for the various CPT code groups. I've also attached the form displaying results.

I would love to display the results in a pie graph. Can this be done?  In a report, I don't care.

Option Compare Database

Option Explicit


Private Sub CalcCT_Enter()

    Dim db As Dao.Database

    Dim rs As Dao.Recordset ' Calculate Exam Totals

    Dim rs1 As Dao.Recordset 'Calculate Head and Neck

    Dim rs2 As Dao.Recordset 'Calculate Chest

    Dim rs3 As Dao.Recordset 'Calculate Spine

    Dim rs4 As Dao.Recordset 'Calculate Pelvis

    Dim rs5 As Dao.Recordset 'Calculate Extremity

    Dim rs6 As Dao.Recordset 'Calculate Abdomen

    Dim rs7 As Dao.Recordset 'Calculate Pulmonary

    Dim rs8 As Dao.Recordset 'Calculate Guidance

    Dim rs9 As Dao.Recordset 'Calculate Rad therapy

    Dim rs10 As Dao.Recordset 'Calculate Other procedure

    Dim rs11 As Dao.Recordset 'Recontruction

    Dim rs12 As Dao.Recordset 'Read only


    Dim dblCTTotal As Double ' Total Exams

    Dim dblCT1 As Double 'Same as above

    Dim dblCT2 As Double '         "

    Dim dblCT3 As Double '         "

    Dim dblCT4 As Double '         "

    Dim dblCT5 As Double '         "

    Dim dblCT6 As Double '         "

    Dim dblCT7 As Double '         "

    Dim dblCT8 As Double '         "

    Dim dblCT9 As Double '         "

    Dim dblCT10 As Double '        "

    Dim dblCT11 As Double '        "

    Dim dblCT12 As Double '        "


    Set db = CurrentDb


    Set rs = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent]")


            Do Until rs.EOF

                      dblCTTotal = rs![Total Exams] + dblCTTotal




            CTTotal = dblCTTotal


    Set rs1 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] >= 70000 And [CPT_Code] <= 70999")


               Do Until rs1.EOF

                      dblCT1 = rs1![Total Exams] + dblCT1




            CT1 = dblCT1


    Set rs2 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] >= 71000 And [CPT_Code] <= 71999")


               Do Until rs2.EOF

                      dblCT2 = rs2![Total Exams] + dblCT2




            CT2 = dblCT2


    Set rs3 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] >= 72125 And [CPT_Code] <= 72132")


               Do Until rs3.EOF

                      dblCT3 = rs3![Total Exams] + dblCT3




            CT3 = dblCT3



    Set rs4 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] = 72192 Or [CPT_Code] =72193")


               Do Until rs4.EOF

                      dblCT4 = rs4![Total Exams] + dblCT4




            CT4 = dblCT4


    Set rs5 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE ([CPT_Code] >= 73200 And [CPT_Code] <= 73202) Or ([CPT_Code] >= 73700 And [CPT_Code] <= 73702)")


               Do Until rs5.EOF

                      dblCT5 = rs5![Total Exams] + dblCT5




            CT5 = dblCT5


    Set rs6 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] >= 74150 And [CPT_Code] <=74170")


               Do Until rs6.EOF

                      dblCT6 = rs6![Total Exams] + dblCT6




            CT6 = dblCT6


    Set rs7 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] = 75746")


               Do Until rs7.EOF

                      dblCT7 = rs7![Total Exams] + dblCT7




            CT7 = dblCT7


    Set rs8 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] = 76360 Or [CPT_Code] =75989 Or [CPT_Code] =76365")


               Do Until rs8.EOF

                      dblCT8 = rs8![Total Exams] + dblCT8




            CT8 = dblCT8


    Set rs9 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] = 76370")


               Do Until rs9.EOF

                      dblCT9 = rs9![Total Exams] + dblCT9




           CT9 = dblCT9


    Set rs10 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] = 76380")


               Do Until rs10.EOF

                      dblCT10 = rs10![Total Exams] + dblCT10




            CT10 = dblCT10


    Set rs11 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] = 76375")


               Do Until rs11.EOF

                      dblCT11 = rs11![Total Exams] + dblCT11




            CT11 = dblCT11


    Set rs12 = db.OpenRecordset("Select [Total Exams] FROM [qrycurrent] WHERE [CPT_Code] = 76140")


               Do Until rs12.EOF

                      dblCT12 = rs12![Total Exams] + dblCT12




            CT12 = dblCT12


    End Sub


Private Sub Form_Open(Cancel As Integer)


End Sub

Open in new window

Question by:Nigel-SA
    LVL 15

    Expert Comment

    by:Simon Ball
    its a dirty way, but the easiest would be to add a table to your database, with the correct number of fields... and one blank record.. id=1

    in your vba code, add a docmd.runsql("update mytable set myfield=myvalue where id = 1")...

    this wouldpopulate your table data aset

    ( you could even get cheeky and use a 2nd function which accepts field and value and adds those in to the sql)

    then write yourself a report which draws a pie chart based on the data in the table...

    LVL 15

    Expert Comment

    by:Simon Ball
    itmight be possible to create a chart object in coee, and assign it its array of variables... etc, but it'd be a lot less work to do it via the above method... thats  assuming that your form isn;t already bound to a table which you could use..

    Author Comment

    Sudonim, can you expound more on the 2nd method?  Not sure the first will work as I have about 5 of these types forms, could get very confusing - thanks
    LVL 15

    Expert Comment

    by:Simon Ball
    i am just doing a sample DB of the first method... i never use charts in access so  trying to work it out..

    i bet its possible to create a chart on the fly thouh by passing it some info.

    having a look for you
    LVL 15

    Accepted Solution


    access charts = yuck.

    i made a table, with fields xtype = CT, fld = CT1 to CT10, value ( use rand to give them a value one to 100)

    ID      xtype      fld      value
    24      CT      CT1      52
    25      CT      CT2      77
    26      CT      CT3      5
    27      CT      CT4      59
    28      CT      CT5      47
    29      CT      CT6      30
    30      CT      CT7      62
    31      CT      CT8      65
    32      CT      CT9      26
    33      CT      CT10      28

    made a form to do some VB to update the table with values... which you can use in your function...

    see code snippet

    then i made a query which selects only the CT xtype ( for this chart...) and shows the fields Fld and Value

    SELECT CtTable2.fld, CtTable2.value
    FROM CtTable2
    WHERE (((CtTable2.xtype)="CT"));

    then i made a blank report, went to insert chart,  selected my query, and pie chart and used the defaults...

    your different reports could all update the sme table, with different xtype value fo each of your charts..

    and a query for each leading to a chart...  could even put all the charts on one report....

    Private Sub Command0_Click()
    Dim i As Integer
    Dim val As Integer
    Dim fld As String
    Dim sqlSTr As String
    For i = 1 To 10
    ' make up a number for the ct value'
    val = CInt(Rnd() * 100)
    'get field name from i '
    fld = "ct" & CStr(i)
    sqlSTr = "update CtTable2 set CtTable2.[value] = " & val & " where (((CtTable2.xtype) = " & Left("""", 1) & "CT" & Left("""", 1) & ") AND ((CtTable2.fld) = " & Left("""", 1) & fld & Left("""", 1) & "));"
    'MsgBox sqlSTr '
    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlSTr
    DoCmd.SetWarnings True
    Next i
    End Sub

    Open in new window


    Author Comment

    You're right - it's ugly, thanks for the effort Sudonim.

    Author Closing Comment

    MS Access graphical functionality is not easy

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Access question - SELECT/UPDATE 11 25
    Access - LDB lock file 4 24
    Sql code problem 6 14
    Excel To Access Database Transfer 14 25
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now