Solved

MSChart with MSAccess 2013

Posted on 2014-03-10
4
823 Views
Last Modified: 2014-03-10
I would like to control a chart on my screen by using a combo box selection.  As I am new to vba I would like to know how i can use the chart object through code.

I want the row source based on the combo selection.   So each time I change the data selection the graph would refresh based on the selection.

Any help would be great appreciated.
0
Comment
Question by:ICTIndika
  • 3
4 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39916886
New to VBA and wanting to control a graph through VBA! I hope you have experience with something similar as this is about as tough as it gets in VBA, although the rewards are great!

Firstly, you'll need a to add a reference to Microsoft Graph.

Listed below is an module I wrote that did all sorts of thing, there were other functions to turn on and off the secondary axes.

Private Sub GraphLayout()
On Error GoTo EH

Dim strUnitsP As String
Dim strUnitsS As String
Dim bHasHealthMax As Boolean
Dim sHealthMax As Single
Dim iDays As Integer
Dim lRecs As Long
Dim iRPD As Integer
Dim iLgnd As Integer
Dim lStartdt As Long
Dim lEnddt As Double

If Me.cboAxes = 1 Then
    With Me!gphResults.Axes(xlValue)
        If .AxisGroup = xlSecondary Then .Delete
    End With
    If Me.OptSupply = 1 Then
        Me!gphResults.ChartTitle.Text = "Results from Location " & Me.lstSupplies & " for " & Me.lstParameters.Column(1) & " by " & Me.lstParameters.Column(2) & " from " & Format(Me.txtDateFrom, "Short Date") & " to " & Format(Me.txtDateTo, "Short Date")
    Else
        Me!gphResults.ChartTitle.Text = "Results from System " & Me.lstSupplies & " for " & Me.lstParameters.Column(1) & " by " & Me.lstParameters.Column(2) & " from " & Format(Me.txtDateFrom, "Short Date") & " to " & Format(Me.txtDateTo, "Short Date")
    End If
   
    If DLookup("[parHasWHOMax]", "Parameter", "[parParameterCode] = '" & Me.lstParameters & "'") Or DLookup("[parHasUSEPAMax]", "Parameter", "[parParameterCode] = '" & Me.lstParameters & "'") Then
        bHasHealthMax = True
    End If
   
    iLgnd = DMax("[Lngth]", "F600_GraphLocationLengths")
    iLgnd = (iLgnd - 10) * 3
   
    lStartdt = CLng(DMin("[DT]", "tempDataForGraph"))
    lEnddt = Int(DMax("[DT]", "tempDataForGraph")) + 1
   
    strUnitsP = DLookup("[resUnit]", "tempDataForGraph")
   
    Me!gphResults.Axes(2, xlPrimary).AxisTitle.Text = strUnitsP
    Me!gphResults.ChartArea.Font.Size = 6
   
    If Me.OptSupply = 1 And Not bHasHealthMax Then
        Me!gphResults.HasLegend = False
        'Me!gphResults.PlotArea.Width = 375
    Else
        Me!gphResults.HasLegend = True
        'Me!gphResults.PlotArea.Width = 270 - iLgnd
    End If
   
    iDays = lEnddt - lStartdt
    lRecs = DCount("*", "tempDataForGraph")
    iRPD = lRecs / iDays
    ''Try to get a sensible displau of X Axis labels and tick marks (in range of 4 to 8)
   
   
    With Me!gphResults
        If .SeriesCollection(1).AxisGroup = xlSecondary Then
            .SeriesCollection(1).AxisGroup = xlPrimary
        End If
        With .Axes(xlCategory)
            If iDays <= 2 Then
                ''Display Major Unit scale at 6 hourly
                .MajorUnit = 0.25
                ''Display Minor Unit Scale @ 3 Hourly
                .MinorUnit = 0.125
                ''Set format of date label
               .TickLabels.NumberFormat = SystemSDate & " hh:mm"
            ElseIf iDays > 2 And iDays < 9 Then
                ''Display attempt at avg daily label
                .MajorUnit = 1
                ''Display attempt at avg daily tick mark
                .MinorUnit = 0.2
                ''Set format of date label
               .TickLabels.NumberFormat = SystemSDate
            Else
                ''We need to appropriately space out with about 6 labels over time range
                iDays = CInt(iDays / 6)
                ''label at no of days for spacing * records per day
                .MajorUnit = iDays
                '' tick mark per day
                .MinorUnit = iDays
                ''Set format of date label
               .TickLabels.NumberFormat = SystemSDate
            End If
            .MinimumScale = lStartdt
            .MaximumScale = lEnddt
        End With
       
        With .Axes(xlValue)
            If Not Me.chkLog Then
                .ScaleType = xlScaleLinear
            Else
                .ScaleType = xlScaleLogarithmic
                If .CrossesAt > .MinimumScale Then
                    .MinimumScale = .MinimumScale / 10
                    .CrossesAt = .MinimumScale
                End If
            End If
        End With
        If bHasHealthMax Then
            With .SeriesCollection(1)
            '    .XValues = "{0,363}"
            '    .Values = "{45,45}"
            '    .Name = "new"
                .MarkerStyle = xlMarkerStyleNone
            '    .Border.Color = vbRed
            End With
        End If
       
    '    .Object.SeriesCollection(2).AxisGroup = xlSecondary
    End With

ElseIf Me.cboAxes = 2 Then
    If Me.OptSupply = 1 Then
        Me!gphResults.ChartTitle.Text = "Results from Location " & Me.lstSupplies & " from " & Format(Me.txtDateFrom, "Short Date") & " to " & Format(Me.txtDateTo, "Short Date")
    Else
        Me!gphResults.ChartTitle.Text = "Results from System " & Me.lstSupplies & " from " & Format(Me.txtDateFrom, "Short Date") & " to " & Format(Me.txtDateTo, "Short Date")
    End If
   
    lStartdt = CLng(DMin("[DT]", "tempDataForGraph"))
    lEnddt = Int(DMax("[DT]", "tempDataForGraph")) + 1
   
    strUnitsP = DLookup("[resUnit]", "tempDataForGraph", "[samLocation] = '" & Me.lstParameters & " By " & Me.lstParameters.Column(2) & "'")
    strUnitsS = DLookup("[resUnit]", "tempDataForGraph", "[samLocation] = '" & Me.lstTest2 & " By " & Me.lstTest2.Column(2) & "'")
   
    iDays = lEnddt - lStartdt
    lRecs = DCount("*", "tempDataForGraph")
    iRPD = lRecs / iDays
    ''Try to get a sensible displau of X Axis labels and tick marks (in range of 4 to 8)
   
   
    With Me!gphResults
        If .SeriesCollection(1).AxisGroup = xlPrimary Then
            .SeriesCollection(1).AxisGroup = xlSecondary
        End If
        .Axes(2, xlSecondary).HasTitle = True
        .Axes(2, xlSecondary).AxisTitle.Orientation = 90
        .Axes(2, xlPrimary).AxisTitle.Text = strUnitsP
        .Axes(2, xlSecondary).AxisTitle.Text = strUnitsS
        .ChartArea.Font.Size = 6
        With .Axes(xlCategory)
            If iDays <= 2 Then
                ''Display Major Unit scale at 6 hourly
                .MajorUnit = 0.25
                ''Display Minor Unit Scale @ 3 Hourly
                .MinorUnit = 0.125
                ''Set format of date label
               .TickLabels.NumberFormat = SystemSDate & " hh:mm"
            ElseIf iDays > 2 And iDays < 9 Then
                ''Display attempt at avg daily label
                .MajorUnit = 1
                ''Display attempt at avg daily tick mark
                .MinorUnit = 0.2
                ''Set format of date label
               .TickLabels.NumberFormat = SystemSDate
            Else
                ''We need to appropriately space out with about 6 labels over time range
                iDays = CInt(iDays / 6)
                ''label at no of days for spacing * records per day
                .MajorUnit = iDays
                '' tick mark per day
                .MinorUnit = iDays
                ''Set format of date label
               .TickLabels.NumberFormat = SystemSDate
            End If
            .MinimumScale = lStartdt
            .MaximumScale = lEnddt
        End With
       
       
        With .Axes(xlValue)
            If Not Me.chkLog Then
                .ScaleType = xlScaleLinear
            Else
                .ScaleType = xlScaleLogarithmic
                If .CrossesAt > .MinimumScale Then
                    .MinimumScale = .MinimumScale / 10
                    .CrossesAt = .MinimumScale
                End If
            End If
        End With
       
    End With

End If

Exit_Sub:
    Exit Sub
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39916891
The lstParameters was a list box were the users could select one or more parameters for inclusion in the graph. There was another Sub, that built the data into a temporary table for graphing.


Kelvin
0
 

Author Comment

by:ICTIndika
ID: 39917129
Thanks, Yes I can see that there is some real power using VBA.  In my case, my graph is very simple as I only need to refresh its data based on a user selection.  I have the data in a table with just a few columns.  For Example, I have a bar chart that displays total spent for each month based on a cost-center. If the user selects a different Cost-Center, the graph would refresh on the screen.   I think this comes down to set the row source for the graph and refreshing it.  
I know this sound simple by so am I when it comes to VBA…, but I am trying...
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 39918176
In the case you want, if the cost centre is the only change, embed your graph into a report and set the parent/child to the cost centre. The set the report recordsource to the costcentre in the usual way and the graph will reset itself. VBA not needed here (apart from setting the report recordsource - which you would do for any report).


Kelvin
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

21 Experts available now in Live!

Get 1:1 Help Now