Timesheet Graphing Problem

[smbarney]

I am working on a timesheet database. By date, you can select an employee and the shift that the employee will work. Shifts are defined by a start time, end time, and a specific color. Using an Afterupdate event, the background color, left property, and width of a textbox are changed to reflect the shift color, start time, and shift length. This works really quite well. However, I need to add up to 20 people per 24 hour shift and every time I change the shift on subsequent entries all of the textboxes are changed. The idea is that you end with a visual representation of shift coverage. Does any one have any idea on how best to fix this so that each entry reflects the appropriate shift? I have screwed around with this for too long. Thanks.


Code:

Private Sub Shift_AfterUpdate()

Dim ShiftLen As String
Dim vbStart As Date
Dim vbEnd As Date
Dim StartPt As Integer
Dim TimeLnLgth As Integer


Me.ShiftColor = Me.Shift.Column(5)
Me.Start = Me.Shift.Column(2)
Me.End = Me.Shift.Column(3)
Me.StartPoint = Me.Shift.Column(6)

StartPt = Me.StartPoint * 1440.18



vbStart = Me.Start
vbEnd = Me.End

If vbEnd < vbStart Then
ShiftLen = DateDiff("n", vbStart, vbEnd + 1) / 60
Else
ShiftLen = DateDiff("n", vbStart, vbEnd) / 60
End If

Me.SheftLength = ShiftLen
TimeLnLgth = 0.2931 * Me.SheftLength * 1440.18
Me.TimeLine.BackColor = Me.ShiftColor
Me.TimeLine.left = StartPt
Me.TimeLine.Width = TimeLnLgth

End Sub