Graph Display Issue

rowfei used Ask the Experts™
I have a report with a graph and table in my database. If I open the report from my computer, I don't have any problem to view it. However, other users opens the report, the graph always block the first row of the table since graph is above the table.

I have try to modify the report to have enough spaces between the graph and table. But user still could not see the first row of the table because the graph block it.

But I don't have any problem to view it from my computer. How can I fix it?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

It works fine on one computer. This implies a software setup problem.
Draw a line just bellow the graph and check.
The problems you are seeing while viewing the chart in print preview mode could result from the fact that  the Chart needs to be resized to fit the screen resolution.  Using a.p.r. Pillai's tip and VBA function from the link below to set certain properties of your chart should fix the problem..



Thanks, Puppydogbuddy.

But can you please be more specific on which codes I need to fix the problem since there's so many codes in Pillai's tip?

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Paste the attached code in a standard module and compile.  If you have trouble getting it to work, download the demo database that comes with the Article and see how it works.

Then follow Pillai's instructions(repeated below):
Open the VBA Module that you have pasted the Code, if you have closed it.

Run the code from the Debug Window (press Ctrl+G to display Debug Window) using the following Syntax:   ChartObject "myReport1", "Chart1"

Or call the function from On_Click() Event Procedure of a Command Button on a Form.
              Private Sub btnViewChart_Click()
                     ChartObject "myReport1", "Chart1"
              End Sub
Select the Chart Type 1, 2 or 3 for Bar Chart, Line Chart or Pie respectively.

The Program will open the Report myReport1 in Design View, modifies the Graph Chart for the selected Type, Saves it and then Re-opens it in Print Preview. You may minimize the VBA Window (Alt+F11) to view the Chart. Since, the Report is saved after the changes, you may open it manually in design view or Print Preview.

When you run the Code for Bar Chart or Line Chart, the Gradient Scheme Color Codes are selected Randomly, which will give different shades of Gradient Colors every time.
Public Function ChartObject(ByVal ReportName As String, _ByVal ChartObjectName As String)
'Author : a.p.r. pillai'Date   : June-2008
'Source Code : from Microsoft Access Help
'and modified certain parameters
Dim Rpt As Report, grphChart As Object
Dim msg As String, lngType As Long, cr As String
Dim ctype As String, typ As Integer, j As Integer
Dim db As Database, rst As Recordset, recSource As String
Dim colmCount As IntegerConst twips As Long = 1440
On Error GoTo ChartObject_Err
cr = vbCr & vbCr
msg = "1. Bar Chart" & cr
msg = msg & "2. Line Chart" & cr
msg = msg & "3. Pie Chart" & cr
msg = msg & "4. Quit" & cr
msg = msg & "Select Type 1,2 or 3"
ctype = "": typ = 0
Do While typ < 1 Or typ > 4 
   ctype = InputBox(msg, "Select Chart Type")
   If Len(ctype) = 0 Then
       typ = 0
       typ = Val(ctype)
   End If
Select Case typ
    Case 4
        Exit Function
    Case 1
       lngType = xlColumnClustered
    Case 2
       lngType = xlLine
    Case 3
       lngType = xl3DPie
End Select
DoCmd.OpenReport ReportName, acViewDesign
Set Rpt = Reports(ReportName)
Set grphChart = Rpt(ChartObjectName)
grphChart.RowSourceType = "Table/Query"
recSource = grphChart.RowSource
If Len(recSource) = 0 Then
   MsgBox "RowSource value not set."
   Exit Function
End If
'get number of columns in chart table/Query
Set db = CurrentDb
Set rst = db.OpenRecordset(recSource)
colmCount = rst.Fields.Count
With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 0.2917 * twips
    .Top = 0.2708 * twips
    .Width = 5.5729 * twips
    .Height = 4.3854 * twips
End With
'Chart type, Title, Legend, Datalabels,Data Table
With grphChart
     .ChartType = lngType
    '.ChartType = xl3DColumnClustered
    '.AutoScaling = True ' only for 3D type
    .HasLegend = True
    .HasTitle = True
    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = "Revenue Performance - Year 2007"
    .HasDataTable = False
    .ApplyDataLabels xlDataLabelsShowValue
End With
'apply gradient color to Chart Series
If typ = 1 Or typ = 2 Then
    For j = 1 To grphChart.SeriesCollection.Count
      With grphChart.SeriesCollection(j)
        '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175))
        .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = Int(Rnd(1) * 54) + 2
        If typ = 1 Then
          .Interior.Color = msoGradientVertical
        End If
        .DataLabels.Font.Size = 10
        .DataLabels.Font.Color = 3
        If typ = 1 Then
            .DataLabels.Orientation = xlUpward
            .DataLabels.Orientation = xlHorizontal
        End If
        End With
    Next j
End If
If ctype = 3 Then GoTo nextstep
 'skip axes for pie chart
 'Y-Axis Title
With grphChart.Axes(xlValue)
    .HasTitle = True
    .HasMajorGridlines = True
    With .AxisTitle
        .Caption = "Values in '000s"
        .Font.Name = "Verdana"
        .Font.Size = 12
        .Orientation = xlUpward
    End With
End With
'X-Axis Title
With grphChart.Axes(xlCategory)
    .HasTitle = True
    .HasMajorGridlines = True
    .MajorGridlines.Border.Color = RGB(0, 0, 255)
    .MajorGridlines.Border.LineStyle = xlDash
    With .AxisTitle
        .Caption = "Quarterly"
        .Font.Name = "Verdana"
        .Font.Size = 10
        .Font.Bold = True
        .Orientation = xlHorizontal
    End With
End With
With grphChart.Axes(xlValue, xlPrimary)
     .TickLabels.Font.Size = 10
End With
With grphChart.Axes(xlCategory)
     .TickLabels.Font.Size = 10
End With
With grphChart
    .ChartArea.Border.LineStyle = xlDash
    .PlotArea.Border.LineStyle = xlDot
    .Legend.Font.Size = 10
End With
'Chart Area Fill with Gradient Color
With grphChart.ChartArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 19
    .TwoColorGradient msoGradientHorizontal, 2
End With
'Plot Area fill with Gradient Color
With grphChart.PlotArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 10
    .TwoColorGradient msoGradientHorizontal, 1
End With
DoCmd.Close acReport, ReportName, acSaveYes
DoCmd.OpenReport ReportName, acViewPreview
     Exit Function
     MsgBox Err.Description, , "ChartObject()"
     Resume ChartObject_Exit
End Function

Open in new window


Got "Syntax error" on the line 11: Dim colmCount As IntegerConst twips As Long = 1440. Any ideas?
My mistake,change line 11 to two lines as follows:

Dim colmCount As Integer
Const twips As Long = 1440

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial