Help with modifying report from Duane Hookom - Hook'D on Access website

[jmrubijon]

Hello all,

I was wondering if someone would be able to help me out. My boss has asked for a timeline type report in access, I found this website http://www.access.hookom.net/Samples.htm which has pretty much what I'm after, so happy days. I'm still relativly new to access and am just learning VBA - I can sort of modify it to suit, but I can't really write it at this stage. What I'm after is a colour bar which will represent the time it takes to complete a project, which this does. However when I try to run the report I get a error: Run-time error '6': Overflow. When I debug the line "Me.txtName.Left = (lngStart * dblFactor) + lngLMarg" is highlighted, and I don't know what todo.

CODE
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'number of days co-location takes
Dim lngStart As Long 'start date of co-location
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 / 365
lngStart = DateDiff("d", #1/1/2008#, Me.[StartDate])
lngDuration = DateDiff("d", Me.[StartDate], Me.[DevelopmentDateEnd])
'set the color of the bar based on a data value
Me.txtName.BackColor = Me.ServiceStyleColor
Me.txtName.Width = 10 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

Additonal information (not sure if this helps). The project started about mid 2008 and will run through until 2015, I'm not sure if it the wide range of dates is affecting this. There are also around 450 different projects (records).

Any help appreciated, my boss will think I'm a hero if I can pull this off.

Many thanks.

 

[Rob Parker]

I've used Duane Hookom's code in the past, and found no problems with it.
I've just had a look at the Calendar crew rotation sample, which seems to be
where the code you're using comes from. A quick play with the data in the
sample database got me an overflow error - I changed the year of the End
Date of one of the data records, and boom!!! So it's likely that your wide
range of dates is indeed the problem. The sample code is setting dblFactor
(used to scale the txtName box) for 365 days (1 year), and the sample data
all fits within that range. I suspect that if your wanting to cover
approximately 7 years, you'll need to change the 365 in the line
dblFactor = Me.boxTimeLine.Width / 365
to a suitably larger value (the number of days between your start date and
end date).

If you're using a set of textboxes in the header, showing months (as in the
sample - it's what the comment line in the code refers to), you'll probably
want/need to change these to show years for the project.

You may also want to include code somewhere that checks for data which will
cause problems (eg. EndDate earlier than StartDate; or missing StartDate or
EndDate, or ...). You could do this in either your data entry form(s), or
by using error trapping on the timeline report. Such features are not
normally included in sample databases, but are essential in real-life
applications.

HTH,

Rob


Hello all,

I was wondering if someone would be able to help me out. My boss has asked
for a timeline type report in access, I found this website
http://www.access.hookom.net/Samples.htm which has pretty much what I'm
after, so happy days. I'm still relativly new to access and am just learning
VBA - I can sort of modify it to suit, but I can't really write it at this
stage. What I'm after is a colour bar which will represent the time it takes
to complete a project, which this does. However when I try to run the report
I get a error: Run-time error '6': Overflow. When I debug the line
"Me.txtName.Left = (lngStart * dblFactor) + lngLMarg" is highlighted, and I
don't know what to do.

CODE
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'number of days co-location takes
Dim lngStart As Long 'start date of co-location
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 / 365
lngStart = DateDiff("d", #1/1/2008#, Me.[StartDate])
lngDuration = DateDiff("d", Me.[StartDate], Me.[DevelopmentDateEnd])
'set the color of the bar based on a data value
Me.txtName.BackColor = Me.ServiceStyleColor
Me.txtName.Width = 10 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

Additonal information (not sure if this helps). The project started about
mid 2008 and will run through until 2015, I'm not sure if it the wide range
of dates is affecting this. There are also around 450 different projects
(records).

Any help appreciated, my boss will think I'm a hero if I can pull this off.

Many thanks.

 

[jmrubijon]

Thanks for the reply Rob.

I changed the 365 to 2555 (365 x 7) and it worked!! Exactly what I was after, thanks for pointing it out to me.

I don't quite understand error trapping yet, but I've booked myself into a VBA course so I should hopefully pick up a few things.

Thanks again for the reply.