Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

juddymar58

Programmer
Nov 15, 2011
17
AU
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
 
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
 
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.

Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top