Solved

MSChart with MSAccess 2013

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
type of query 11 40
Access sql to sql server express 10 31
Delete QueryDef IF it Exists: Access VBA 5 28
Criteria for Date for DCount 4 16
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.

813 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

11 Experts available now in Live!

Get 1:1 Help Now