CODEWITHSUNDEEP
vbams-accesscharts
alexpbell
alexpbell

Reputation: 85

Charts/graphs in Access VBA

How to initialize a chart with data in MS Access VBA? Eventually I found a suggestion here which with a bit of modification kept intellisense happy and resulted in this code:

Dim objchart As Chart

Dim arrData(3, 1) As Double

arrData(0, 0) = 1
arrData(1, 0) = 1
arrData(2, 0) = 1
arrData(3, 0) = 1

arrData(0, 1) = 1
arrData(1, 1) = 1
arrData(2, 1) = 1
arrData(3, 1) = 1

Set objchart = Me.Chart1

With objchart
    .ChartTitle = "test"
    .ChartType = acChartLine
    .ChartValues = arrData
End With

But this throws

Compile error: Type mismatch

on the line

    .ChartValues = arrData

I have tried it as "row-first" (arrData(1, 3)) and also just passing in a single array (arrData(3)). These both result in the same type mismatch error. While the intellisense is telling me that Chart1 exists, and that .ChartValues is a valid field, it doesn't tell me what kind of object it is expecting. I have googled extensively on this and come up empty. The best references I could find for Access VBA were Building VBA Apps and this but neither go into detail on Charts or ChartObjects.

Obviously I'd like to get past this type mismatch error. Even better would be if someone can give me some general advice on how to go about this when the closest thing to a language reference is silent on the part of the language you need.

Upvotes: 0

Views: 7601

Answers (1)

alexpbell
alexpbell

Reputation: 85

This is one way to do it. First, create a new table and add some data:

Private Sub Form_Load()

  Dim db As DAO.Database
  Dim rec As Recordset
  Dim tbl As DAO.TableDef

  Set db = CurrentDb
  Set tbl = db.CreateTableDef("tbl")

  With tbl
    .Fields.Append .CreateField("first", dbInteger)
    .Fields.Append .CreateField("second", dbInteger)
  End With

  db.TableDefs.Append tbl
  db.TableDefs.Refresh

  Set rec = db.OpenRecordset("tbl")

  rec.AddNew
  rec("first").Value = 0
  rec("second").Value = 2
  rec.Update

  rec.AddNew
  rec("first").Value = 1
  rec("second").Value = 2
  rec.Update

  rec.AddNew
  rec("first").Value = 2
  rec("second").Value = 2
  rec.Update

  rec.AddNew
  rec("first").Value = 3
  rec("second").Value = 2
  rec.Update

  Set rec = Nothing
  Set db = Nothing

End Sub

Second, graph that data by referencing the new table:

Private Sub command0_click()

  Dim objchart As Chart
  Set objchart = Me.Chart1

  With objchart
    .ChartTitle = "tbl: second ~ first"
    .RowSource = "tbl"
    .ChartAxis = "first"
    .ChartValues = "second"
  End With

End Sub

Upvotes: 1

Related Questions