?
Solved

MSChart with MSAccess 2013

Posted on 2014-03-10
4
Medium Priority
?
862 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

719 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