Solved

MSChart with MSAccess 2013

Posted on 2014-03-10
4
835 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

860 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