Bar graph showing percentage complete/overall progress

[ginabenina]

Hello,
Any help is greatly appreciated. I'm trying to show the progress of each project based on milestones with the activities ranging from 1 - 100. A single bar graph for each project.
I was trying to use the following set in the On Format of the detail section with no luck (http://www.tek-tips.com/viewthread.cfm?qid=1749100):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim int100Pct As Integer  ' width of 100 Percent
    int100Pct = 1440 * 6      ' 6 inches
    Me.txtPC.Width = Me.txtPC / 100 * int100Pct
End Sub

The control source of txtPC are Long Integers. Not sure what I am missing. :banghead:

 

[isladogs]

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim int100Pct As Integer  ' width of 100 Percent
    int100Pct = 1440 * 6      ' 6 inches
    Me.txtPC.Width = Me.txtPC / 100 * int100Pct
End Sub

The control source of txtPC are Long Integers. Not sure what I am missing. :banghead:

A bracket! Either that or rearrange the formula!
Currently you are DIVIDING by int100Pct but you need to go forth and MULTIPLY

Me.txtPC.Width = (Me.txtPC / 100) * int100Pct

or

Me.txtPC.Width =  int100Pct*Me.txtPC / 100

 

[arnelgp]

What is the value of txtPc? Is it the percentage already?

 

[ginabenina]

Thank you for the reply.

Just tried adding brackets, rearranging, still not working.

The value is currently a Long Int, not formatted as anything. I did have it as a double, formatted as a percentage at one point to no avail.

 

[arnelgp]

So you divide it with the Goal value or the overall total, whatever then you have percentage then multiply by int100pct variable.

 

[Mark_]

Please take a look at the sample attached. It shows several ways of doing graphing like this in ACCESS.

 

[ginabenina]

The width of the textbox is not changing at all.

Dim int100Pct As Integer
    int100Pct = 1440 * 6
    Me.txtPC.Width = (Me.txtPC / 100) * int100Pct

Should the number be a Double opposed to a Long Int?

Taking a look now at the Custom Charting.

 

[ginabenina]

I am unable to run the report, Run-time error '1200': control or subform control too large for this location.
Compiler stops here,

 Me.C_Duration.Width = aiLeng 'and   setting the real width

Running Access 2016

 

[Mark_]

Question,

What type of control is txtPC? Where are you getting the value for txtPC as you are using it in your calculation.

For myself, I would one control for data entry (or get it from a table/query/function) and a second for display.

If txtPC is a label then you would need to reference Me.txtPC.Caption to get the value it is displaying. If it is a text box that you are using for data entry, do you really want to change the size when a user enters data into it? If they put in a 0, that means they would need to leave the form and reopen it to be able to change it.

 

[ginabenina]

It is a textbox, the value is coming from a table as a Long Int, populating a report. The idea was to have the text box resize based on the percentage complete to mimic a bar graph. Should i be approaching this completely differently?

 

[isladogs]

I do this type of chart a lot in both forms & reports & its fairly simple to set up though you often need to tweak the scaling to get it to work

Attached are 3 screenshots from a report with several subreports with bar charts
- full report / report design & one of the subreports full size

This the full code to achieve the subreport shown with explanatory comments
Its more complex than you want but the idea is the same

Similar code is used in each subreport & in various forms

Dim ScalingFactor As Integer
Const CHARTWIDTH = 6  'multiplying factor to fit the space available
Const TwipsNumber As Integer = 567 (twips per cm)

Function GetScale()
'get size of largest value for chart
GetScale = Nz(DMax("CountOfPastoralRecordID", "qryPRecordsByFaculty", "PupilID = '" & Me.PupilID & "'"), 0)  
End Function

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Bar1 is the textbox used to create the bars in the chart
Me.Bar1.Width = CHARTWIDTH * TwipsNumber * Me.CountOfPastoralRecordID \ ScalingFactor

If Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.1 > (CHARTWIDTH + 0.5) * TwipsNumber Then
    'large value - show value in white font on top of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width - 1 * TwipsNumber
    Me.txtCount.ForeColor = vbWhite
Else 'small value - show value in black font to right of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.3
    Me.txtCount.ForeColor = vbBlack
End If

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'add label to show maximum value fitting space available
'this is rounded up from max value used in chart
ScalingFactor = Round((GetScale + 5) / 10, 0) * 10
Me.LblScale.Caption = "(Full Width = " & ScalingFactor & ")"
End Sub

Hope that helps - feel free to ask questions if necessary

 

[Mark_]

Sorry about the error.

I had been tinkering with it and gotten distracted. The report defaults to using the current year and I hadn't checked for 2018 when I put that together last year. Please see the attached updated.

For myself, on a report I would be using a label. That way I don't forget and try to do something silly with it.

 

[Mark_]

If Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.1 > (CHARTWIDTH + 0.5) * TwipsNumber Then
'large value - show value in white font on top of bar
Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width - 1 * TwipsNumber
Me.txtCount.ForeColor = vbWhite
Else 'small value - show value in black font to right of bar
Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.3
Me.txtCount.ForeColor = vbBlack
End If[/CODE]

Would it trouble you if I blatantly used this same logic in my demo? I've really been meaning to get back to it and this is a very nice touch!

 

[isladogs]

Would it trouble you if I blatantly used this same logic in my demo? I've really been meaning to get back to it and this is a very nice touch!

Feel free - if I put it on the forum, its there for others to use ....
The subreport screenshot shows the effect well

EDIT: I also posted lots of other screenshots of the same idea (used in forms & reports) in various posts in this thread
https://www.access-programmers.co.uk/forums/showthread.php?t=297309

 

[ginabenina]

I do this type of chart a lot in both forms & reports & its fairly simple to set up though you often need to tweak the scaling to get it to work

Attached are 3 screenshots from a report with several subreports with bar charts
- full report / report design & one of the subreports full size

This the full code to achieve the subreport shown with explanatory comments
Its more complex than you want but the idea is the same

Similar code is used in each subreport & in various forms

Dim ScalingFactor As Integer
Const CHARTWIDTH = 6  'multiplying factor to fit the space available
Const TwipsNumber As Integer = 567 (twips per cm)

Function GetScale()
'get size of largest value for chart
GetScale = Nz(DMax("CountOfPastoralRecordID", "qryPRecordsByFaculty", "PupilID = '" & Me.PupilID & "'"), 0)  
End Function

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Bar1 is the textbox used to create the bars in the chart
Me.Bar1.Width = CHARTWIDTH * TwipsNumber * Me.CountOfPastoralRecordID \ ScalingFactor

If Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.1 > (CHARTWIDTH + 0.5) * TwipsNumber Then
    'large value - show value in white font on top of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width - 1 * TwipsNumber
    Me.txtCount.ForeColor = vbWhite
Else 'small value - show value in black font to right of bar
    Me.txtCount.Left = Me.Bar1.Left + Me.Bar1.Width + TwipsNumber * 0.3
    Me.txtCount.ForeColor = vbBlack
End If

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'add label to show maximum value fitting space available
'this is rounded up from max value used in chart
ScalingFactor = Round((GetScale + 5) / 10, 0) * 10
Me.LblScale.Caption = "(Full Width = " & ScalingFactor & ")"
End Sub

Hope that helps - feel free to ask questions if necessary

Thank you! Working on it now.. will reply with updates!

 

[ginabenina]

Function GetScale()
'get size of largest value for chart
GetScale = Nz(DMax("CountOfPastoralRecordID", "qryPRecordsByFaculty", "PupilID = '" & Me.PupilID & "'"), 0)  
End Function

If I am using a value instead of a count would I just have GetScale equal to that column from the query? I think maybe my query is not set up correctly. Everything is compiling.
My query has a list of areas, with values based on what status they are in. Each status is assigned a value between 1-100. Should I change the GetScale variable equal to the max value of 100?

 

[isladogs]

If the max possible value is always going to be 100, then set both GetScale and ScalingFactor to that value.

In fact just set scaling factor to 100 and remove GetScale

Or just hardcode 100 wherever either of those items are used...if that value will never change

 

[ginabenina]

Thank you ridders!
Just back to work after flu, still working this. I appreciate your help!!!