Meter bar for each record in access form/report

  • Thread starter Thread starter mcdonaldsemc
  • Start date Start date
M

mcdonaldsemc

Is it possible to have a meter chart "graphong" the value for each
individual record in an Access form or report. My previous attempts
have failed. Using a modification of the following snippet seems like
it would work but I dont know how to reference the value foreach
record. The control always shows the value for the first/top record
in the form/datasheet. Any help would be appreciated!


Heres the basis of what ive been testing (posted elsewhere):

1. Create a Label control, with a sunken style, (we'll call this
baselbl)
and make it transparent in colour.


2. Create a label control, of 0.00 width, identical in height to
the
baselbl (we'll call this lblmeter); align the lblmeter exactly with
the left
edge of the baselbl control, and send it to the back (i.e. behind
the
baselbl.) Make its edges transparent.


3. To update the Progress Meter place the following code in a module
in
the form and send it the current and total amounts to measure as
appropriate:


Sub updatemtr (currentamt, totalamount)
Dim MtrPercent as Single
MtrPercent = currentamt/totalamount
Me!baselbl.Caption = Int(MtrPercent*100) & "%"
Me!lblmeter.Width = CLng(Me!baselbl.Width * MtrPercent)
Select Case MrtPercent
Case Is < .15
Me!lblmeter.BackColor = 255
Case Is < .50
Me!lblmeter.BackColor = 65535
Case Else
Me!lblmeter.BackColor = 65280
End Select
End Sub


4. Note this same tip can also be used to create a simple horizontal
bar chart
on a report
 
This looks very similar to a project that I was trying to accomplish to
create a schedule for our presses. I found an example of a shipping schedule
(sorry, I don't remember exactly where I found it). The only name I can find
on the original database is Shawn Gamble and it was a Ship Rotation Schedule.

Anyway, he was using a report to display a bar graph that was built much as
you describe. Here is the code that comes from the report detail's On Format
event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'hours of job
Dim lngStart As Long 'start time of job
Dim lngLMarg As Long
Dim dblFactor As Double


'put a line control in your page header that starts 1/1 and goes to 12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 1440
lngStart = DateDiff("n", "1/26/2007 6:00 AM", Me.[DateTime])
lngDuration = Me.Duration * 60
'set the color of the bar based on a data value
'Me.txtName.BackColor = Me.VesselColor
Me.txtJobName.Width = 10 'avoid the positioning error
Me.txtJobName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtJobName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

I thought that the bit that might help you was the "Me.MoveLayout = False"
bit. I don't know much about the VBA language but this seemed to be the part
of the code that allowed multiple records to be placed into a single report.
I thought I would throw it out here in case you found it helpful. Also, I do
still have the database that I could send you if you think it would help.

Hope this helps,
Steven
 
Back
Top