asked on

Graph Display Issue

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?

Hamed Nasr

It works fine on one computer. This implies a software setup problem.
Draw a line just bellow the graph and check.

puppydogbuddy

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..

http://www.msaccesstips.com/2008/06/working-with-chart-object-in-vba.shtml

rowfei

ASKER

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?

puppydogbuddy

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
   Else
       typ = Val(ctype)
   End If
Loop
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
rst.Close
With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 0.2917 * twips
    .Top = 0.2708 * twips
    .Width = 5.5729 * twips
    .Height = 4.3854 * twips
End With
grphChart.Activate
'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
        Else
            .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
nextstep:
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
grphChart.Deselect
DoCmd.Close acReport, ReportName, acSaveYes
DoCmd.OpenReport ReportName, acViewPreview
ChartObject_Exit:
     Exit Function
ChartObject_Err:
     MsgBox Err.Description, , "ChartObject()"
     Resume ChartObject_Exit
End Function

Open in new window

rowfei

ASKER

Got "Syntax error" on the line 11: Dim colmCount As IntegerConst twips As Long = 1440. Any ideas?

ASKER CERTIFIED SOLUTION

puppydogbuddy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial