PC Review


Reply
Thread Tools Rate Thread

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

 
 
jmrubijon@gmail.com
Guest
Posts: n/a
 
      23rd Jul 2012
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.
 
Reply With Quote
 
 
 
 
Rob Parker
Guest
Posts: n/a
 
      26th Jul 2012
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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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.


 
Reply With Quote
 
 
 
 
jmrubijon@gmail.com
Guest
Posts: n/a
 
      30th Jul 2012
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Question for Duane Hookom or anyone else that could help.... GoBrowns! Microsoft Access Reports 26 9th Jun 2009 04:37 PM
Question for Duane Hookom or anyone else that could help.... GoBrowns! Microsoft Access Reports 0 27th May 2009 06:08 PM
Multiple Column Report - esp for Duane Hookom =?Utf-8?B?Sm9obiBE?= Microsoft Access Reports 8 25th Oct 2006 06:19 AM
Duane Hookom's - Outlook Styled Report Demo =?Utf-8?B?TGVl?= Microsoft Access Reports 20 15th Mar 2006 02:28 PM
To Duane Hookom re Concatenate Noel Microsoft Access 8 5th Sep 2003 09:59 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:46 PM.