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


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

Who is Participating?
Simon BallConnect With a Mentor Commented:

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

Simon BallCommented:
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...

Simon BallCommented:
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..
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Nigel-SAAuthor Commented:
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
Simon BallCommented:
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
Nigel-SAAuthor Commented:
You're right - it's ugly, thanks for the effort Sudonim.
Nigel-SAAuthor Commented:
MS Access graphical functionality is not easy
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.