CODEWITHSUNDEEP
formsms-accessvba
Bubaya
Bubaya

Reputation: 823

Access Forms: Passing custom properties to Chart

I am building a form that is supposed to show some histograms that aggregate some queries. Since access has no native support for histogram charts, the SQL query set as the data source for each chart is relatively verbose and repetitive. The idea is to draw all corners of each column in the histogram manually in a xy-plot. Currently I achieve this by the following query:

select
    bin(field,50,-1000,1000) - 25 AS bin,
    1 as cnt,
    3 as ord
FROM tab
GROUP BY bin(field,50,-1000,1000) - 25

union all

select
    bin(field,50,-1000,1000) - 25 AS bin,
    Count(*) AS cnt,
    4 as ord
FROM tab
GROUP BY bin(field,50,-1000,1000) - 25

union all

select
    bin(field,50,-1000,1000) + 25 AS bin,
    Count(*) AS cnt,
    1 as ord
FROM tab
GROUP BY bin(field,50,-1000,1000) + 25

union all 

select
    bin(field,100,-2000,2000) + 500 AS bin,
    1 as cnt,
    2 as ord
FROM tab
GROUP BY bin(field,50,-1000,1000) + 25

order by bin, ord

where bin is a VBA function that chooses the bin depending on the bin width and boundaries. You may notice that the binning configuration occurs eight times in this query.

I would like to add custom properties to the chart control that can be referred to in the query. Does anyone know how to achieve this?

Remark: You might suggest that I could use other controls to configure the binning. However, I have many of these charts, and I would like to easily add new ones with individual binning. If I had other controls for the binning, I would have to copie this as well and adjust the references to them in the SQL, which I suppose not to amount to less work.

Upvotes: 0

Views: 92

Answers (1)

Erik A
Erik A

Reputation: 32642

There's no such thing as the current chart, since a form can contain many charts. You can, however, identify all charts on a form, and do a specific operation with them:

Dim ctl As Control
For Each ctl In Me.Controls 'Or Forms!SomeForm.Controls
    If TypeOf ctl Is Access.ObjectFrame 'OLE object, can be chart
        If ctl.OLEClass = "Microsoft Graph Chart"
            'Do stuff with the ctl.Tag property
        End If
    End If
Next

Upvotes: 1

Related Questions