Access Report Graph/Chart Set Maximum Value of X Axis and Scale of X Axis 1

[juddymar58]

Hi, I have been searching and can't seem to get this working.
I have a ms access bar graph which has a list of tasks on the y axis. The x axis shows the percentage completed of each task (value between 0-100)
I'm trying to set the maximum value of the x axis to display 100 and the scale in increments of 10 (as its a percentage it will never be greater than 100).
As per the attached file it if there isn't a task which isn't 100% complete the scale doesn't go all the way to 100.
I have been trying to set it via vba
Me!graphDesignProgress.Axes(1).MaximumScale = 100
But keep getting an error message so I think I'm on the wrong track
If the solution can be done with vba what code should I be using and where do I call the code from, is it the chart 'On Updated' event?
Any advice appreciated
Thanks
Justin

 

[SkipVought]

hi,

Use a Percent Bar Chart type.

 

[juddymar58]

Thanks for the reply. I ended up going with a different solution which gave me a bit of extra control over the grouping and appearance of the chart. I just created a continuous report with a textbox to show each task. Next to this I have 100 small textboxes called (box1 to box100) and in the on open event I just call the following to set conditional formatting for each textbox. This gives me a bar graph for each task reflecting the percentage complete, the bonus is I have grouping on the report to group tasks under their respective categories. Pretty happy with the outcome.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo HandleError
Const mdlConstBlue = 16748574
Dim objFrc As FormatCondition
Dim intCount As Integer
intCount = 1
Do While intCount < 101
'REMOVE FORMAT CONDITIONS FOR BOX
Me.Controls("box" & intCount).FormatConditions.Delete
' Create three format objects and add them to the FormatConditions
Set objFrc = Me.Controls("box" & intCount).FormatConditions.add(acExpression, , "[fldNZPercentComplete]>=" & intCount)
With Me.Controls("box" & intCount).FormatConditions(0)
.BackColor = mdlConstBlue
End With
intCount = intCount + 1
Loop

Exit_HandleError:
Exit Sub

HandleError:
'Resume Next
Resume Exit_HandleError

End Sub

 

[juddymar58]

I've included a screenshot of the report in case anyone wants to use it in the future.
Thanks
Justin

 

[dhookom]

I would simply use a little code in the On Format event of the detail section to set the Width of a text box. Set the fill of the text box to the appropriate color and align the text where ever you want.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim int100Pct As Integer  [COLOR=#4E9A06]' width of 100 Percent
[/color]    int100Pct = 1440 * 6 [COLOR=#4E9A06]     ' 6 inches
[/color]    Me.PercentCompleted.Width = Me.PercentCompleted / 100 * int100Pct
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[juddymar58]

Thanks Duane, I knew there was going to be an easier solution but its up and running now so I'll leave it as is If I have to alter the width of the chart I'll take up your solution rather than adjusting my 100 separate textboxes.

Thanks
Justin