Advertisement

08.21.2008 at 05:07PM PDT, ID: 23668918
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
7.7

Graphs only format properly once reloaded - on first load the legend overlaps the plot area

Asked by ff77 in Access Coding/Macros, Microsoft Access Database, Access Forms

Tags: , , ,

I use a routine to call a chart in a form. When it is first called the graph layout is messy, with the legend overlapping the plot area.

However when I step into the code it draws neatly. I have a button that toggles the datasheet and graph view. When I toggle back from the datasheet the graph draws neatly. The toggle button uses the same routine the loads the graph in the first place - so I can't see any reason why it would show properly second time around.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:

Sub GraphLoad(nGraphID As Long, frmGraph As Form, Optional bShowDataTable As Boolean = False)
 
    Dim rs As DAO.Recordset    'Ensure Microsoft DAO 3.6 Object Library installed
    Dim objChart As Control
    Dim Size As Long    'Chart area height
    Dim sCaption As String
    Dim sLookup As String
    Dim lSize As Single    'Graph resize factor
    Dim cht As Chart    'Ensure Microsoft DAO 3.6 Object Library installed
 
 
 
    On Error GoTo GraphLoad_Error
 
    lSize = 0.6
    If nGraphID = -1 Then
        frmGraph.lblShouldBeVisible.Caption = "False"
    Else
        'embeddedSQL - looks up record for this graph ID
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM CHT_Charts WHERE " & _
                                         "CHT_ID = " & nGraphID, dbOpenForwardOnly)
 
        If rs.RecordCount = 1 Then
            'Set labels on form
            frmGraph.lblGraphTitle.Caption = rs!CHT_GraphTitle
            frmGraph.lblCurrentChartType.Caption = IIf(bShowDataTable, _
                                                       "DataTable", IIf(Nz(rs!CHT_GraphType) = "" _
                                                                        Or Trim(Nz(rs!CHT_SQL)) = "", "DataTable", Nz(rs!CHT_GraphType)))
            frmGraph.lblCurrentGraphID.Caption = nGraphID
            frmGraph.lblShouldBeVisible.Caption = "True"
 
            Call frmGraph.HideAllCharts
            'If table set the chart & skip to skipper
            If rs!CHT_GraphType = "table" Then
                Set objChart = frmGraph.Controls("chtTable")
                objChart.SourceObject = rs!CHT_SQL
                GoTo Skipper:
            End If
            'If graph, not table set chart
            Set objChart = frmGraph.Controls("cht" & IIf(bShowDataTable, _
                            "DataTable", IIf(Nz(rs!CHT_GraphType) = "" _
                            Or Trim(Nz(rs!CHT_SQL)) = "", IIf(Nz(rs!CHT_GraphType) _
                            Like "*HeatmapLink", Nz(rs!CHT_GraphType), "DataTable"), _
                            Nz(rs!CHT_GraphType))))
'remove after!            'objChart.Visible = True
            If TypeOf objChart Is SubForm Then
                If rs!CHT_GraphType Like "*HeatmapLink*" Then
                    'heatmap link stuff
                    DoEvents
                    frmGraph.cmdTableGraph.Visible = False
                    'create query for heatmap
                ElseIf rs!CHT_GraphType Like "*Heatmap*" Then
 
                    'heatmap stuff
                    CurrentDb.QueryDefs("DynamicHeatMapSource_qsl").Sql = _
                    rs!CHT_SQL
                    DoEvents
                    'UpdateHeatMapTable
                    objChart.Form.Requery
                    frmGraph.cmdTableGraph.Visible = False
                    'create query for datatable e.g if table button presses
                ElseIf IIf(bShowDataTable, "DataTable", _
                           IIf(Nz(rs!CHT_GraphType) = "" Or Trim(Nz(rs!CHT_SQL)) = "", _
                               "DataTable", Nz(rs!CHT_GraphType))) = "DataTable" Then
                    DeleteQueryDef "TMP_QRY_DataTable" & Trim(Str(nGraphID))
                    'ISSUE********************************************************************
                    CurrentDb.CreateQueryDef "TMP_QRY_DataTable" & _
                                             Trim(Str(nGraphID)), rs!CHT_SQL
                    objChart.SourceObject = "query.TMP_QRY_DataTable" & _
                                            Trim(Str(nGraphID))
 
                    frmGraph.cmdTableGraph.Visible = bShowDataTable
                Else
                    objChart.Form.RecordSource = rs!CHT_SQL
                    frmGraph.cmdTableGraph.Visible = False
                End If
                'hide print button & show export on subform
                frmGraph.cmdExcel.Visible = True
                frmGraph.cmdPrint.Visible = False
            ElseIf TypeOf objChart Is Label Then
                'just a placeholder - show buttons
                frmGraph.cmdExcel.Visible = False
                frmGraph.cmdPrint.Visible = False
                frmGraph.cmdTableGraph.Visible = False
            Else
                'not subform or placeholder, so set axis labels & units
                objChart.RowSource = rs!CHT_SQL
                frmGraph.cmdExcel.Visible = True
                frmGraph.cmdPrint.Visible = True
                frmGraph.cmdTableGraph.Visible = True
                'Labels
                If rs!CHT_HasLabel = True Then    'set axis & units
                    'Axis Units
                    'Y unit
                    Select Case rs!CHT_Y_AxisUnit
                    Case "Hundreds"
                        objChart.Object.Application.Chart.Axes(xlValue).DisplayUnit = xlHundreds
                        'No units Label
                        objChart.Object.Application.Chart.Axes(xlValue).HasDisplayUnitLabel = False
                    Case "Thousands"
                        objChart.Object.Application.Chart.Axes(xlValue).DisplayUnit = xlThousands
                        'No units Label
                        objChart.Object.Application.Chart.Axes(xlValue).HasDisplayUnitLabel = False
                    Case "Millions"
                        objChart.Object.Application.Chart.Axes(xlValue).DisplayUnit = xlMillions
                        'No units Label
                        objChart.Object.Application.Chart.Axes(xlValue).HasDisplayUnitLabel = False
                    End Select
 
                    'X Unit
                    If rs!CHT_x_AxisUnit <> "None" And Trim(rs!CHT_x_AxisUnit) <> "" Then
                        'Y unit
                        Select Case rs!CHT_x_AxisUnit
                        Case "Hundreds"
                            objChart.Object.Application.Chart.Axes(xlCategory).DisplayUnit = xlHundreds
                            'No units Label
                            objChart.Object.Application.Chart.Axes(xlCategory).HasDisplayUnitLabel = False
                        Case "Thousands"
                            objChart.Object.Application.Chart.Axes(xlCategory).DisplayUnit = xlThousands
                            objChart.Object.Application.Chart.Axes(xlCategory).HasDisplayUnitLabel = False
                        Case "Millions"
                            objChart.Object.Application.Chart.Axes(xlCategory).DisplayUnit = xlMillions
                            objChart.Object.Application.Chart.Axes(xlCategory).HasDisplayUnitLabel = False
                        End Select
                    End If
                    'Axis Labels
                    'Ylabel
                    With objChart.Object.Application.Chart.Axes(xlValue)
                        .HasTitle = True
                        If Left(Trim(rs!CHT_y_AxisLabel), 2) = "F:" Then
                            sLookup = Right(rs!CHT_y_AxisLabel, Len(rs!CHT_y_AxisLabel) - 2)
                            sCaption = DLookup(sLookup, "Stat_Settings")
                        Else
                            sCaption = CStr(rs!CHT_y_AxisLabel)
                        End If
                        If rs!CHT_Y_AxisUnit = "None" Then
                        Else
                            sCaption = sCaption & " (" & rs!CHT_Y_AxisUnit & ")"
                        End If
                        .AxisTitle.Caption = sCaption
                        .AxisTitle.Orientation = xlUpward
                    End With
 
                    'xLabel
                    With objChart.Object.Application.Chart.Axes(xlCategory)
                        .HasTitle = True
                        If Left(Trim(rs!CHT_x_AxisLabel), 2) = "F:" Then
                            sLookup = Right(rs!CHT_x_AxisLabel, Len(rs!CHT_x_AxisLabel) - 2)
                            sCaption = DLookup(sLookup, "Stat_Settings")
                        Else
                            sCaption = CStr(rs!CHT_x_AxisLabel)
                        End If
                        If rs!CHT_x_AxisUnit = "None" Then
                        Else
                            sCaption = sCaption & " (" & rs!CHT_x_AxisUnit & ")"
                        End If
                        .AxisTitle.Caption = sCaption
                        '.AxisTitle.Offset = 0
                        '.TickLabels.Offset = 0
 
                    End With
                End If
 
                'Legend Position
                objChart.Object.Application.Chart.Legend.Position = xlRight
                objChart.Object.Application.Chart.Refresh
 
                'Plot area size
                Size = objChart.Object.Application.Chart.Height
                objChart.Object.Application.Chart.PlotArea.Height = lSize * Size
 
            End If
 
            If frmGraph.Form.lblCurrentChartType.Caption = "DataTable" Then
                frmGraph.Form.lblFilterOn.Left = 3400
                frmGraph.Form.lblFilterOn.Top = 75
            Else
                frmGraph.Form.lblFilterOn.Left = 4735
                frmGraph.Form.lblFilterOn.Top = 345
            End If
Skipper:
            'Hide Icons
            On Error Resume Next
            frmGraph.cmdPrint.Visible = False
            frmGraph.Drilldown.Visible = False
            On Error GoTo 0
            objChart.Visible = True
            If objChart.Name = "ChtTable" Then GoTo Skipper2:
            If objChart.Height <> frmGraph.lblClick.Height Then objChart.Height _
               = frmGraph.lblClick.Height
            If objChart.Width <> frmGraph.lblClick.Width Then objChart.Width = _
               frmGraph.lblClick.Width
            If objChart.Left <> frmGraph.lblClick.Left Then objChart.Left = _
               frmGraph.lblClick.Left
            On Error Resume Next
            If objChart.Top <> frmGraph.lblClick.Top Then objChart.Top = _
               frmGraph.lblClick.Top
            objChart.Object.Application.Chart.Refresh
            On Error GoTo 0
 
        Else
            MsgBox "Graph data missing", vbOKOnly, "Status"
            frmGraph.lblShouldBeVisible.Caption = "False"
        End If
    End If
Skipper2:
    DoEvents
    On Error GoTo 0
    Exit Sub
GraphLoad_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GraphLoad of Module Navigation_mod"
End Sub
Attachments:
 
messy-legend.doc (39 KB) (File Type Details) 
screenshots with and without problem
 
 
Keywords: Graphs only format properly once relo…
Title
1 Excel Graph Plot Area Management
2 plotting a graph
3 Plot Graph?
4 Moving legend in chart
5 Chart Legend
6 Mortgage - graph legend and plot points
 
Loading Advertisement...
 
[+][-]08.22.2008 at 12:15PM PDT, ID: 22293213

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Access Coding/Macros, Microsoft Access Database, Access Forms
Tags: Microsoft, Access / Graph, 2003, charts only position properly on second load or when stepped into
Sign Up Now!
Solution Provided By: boag2000
Participating Experts: 1
Solution Grade: B
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628