Timeline Report - error in code?

[NKA]

I have created a timeline (gantt style) report using the Timeline example
from the Access Web.

I haven't modified the code in any shape or form other than to change field
names.

My minDate is always Date() and my maxDate is variable.
My current data set has a max of 215 days.
When I run the report, the timelines all look great, bar one - which is the
one task that spans the whole 215 days. I expect to see the 'bar' span the
entire width of the timeline - but it doesn't! It sits at what looks to be
about 5 days!!

The line of code to determine the width of the timeline is
.Width = intDayDiff * sngFactor

The values translate as
.Width = 215 * 54.9907 (which equals 11823.0005) but the value returned
for .Width is 284 ?????

Coding is not my strong point - so I am at an absolute loss now as another
extended date (31/03/2009) which is 124 days, works fine!

I have pasted the code below in the hope someone might be able to assist.

Many thanks
NKA



The code is:

Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single

On Error Resume Next


Me.ScaleMode = 1 'Twips
sngFactor = Me.boxMaxDays.Width / mintDayDiff ' calculate max width of
timeline, divide by max no of days to get single value

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True
intStartDayDiff = Abs(DateDiff("d", Me.StartDate, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.EndDate, Me.StartDate))

If intStartDayDiff = 0 Then intStartDayDiff = 1
With Me.boxGrowForDate
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
.Width = intDayDiff * sngFactor
End With
Me.lblTotalDays.Left = Me.boxGrowForDate.Left
Me.lblTotalDays.Left = Me.boxGrowForDate.Left + Me.boxGrowForDate.Width
Me.lblTotalDays.Caption = intDayDiff & " day(s)"
Else
Me.boxGrowForDate.Visible = False
Me.lblTotalDays.Visible = False
End If

 

[Marshall Barton]

I have created a timeline (gantt style) report using the Timeline example
from the Access Web.

I haven't modified the code in any shape or form other than to change field
names.

My minDate is always Date() and my maxDate is variable.
My current data set has a max of 215 days.
When I run the report, the timelines all look great, bar one - which is the
one task that spans the whole 215 days. I expect to see the 'bar' span the
entire width of the timeline - but it doesn't! It sits at what looks to be
about 5 days!!

The line of code to determine the width of the timeline is
.Width = intDayDiff * sngFactor

The values translate as
.Width = 215 * 54.9907 (which equals 11823.0005) but the value returned
for .Width is 284 ?????

Coding is not my strong point - so I am at an absolute loss now as another
extended date (31/03/2009) which is 124 days, works fine!


The code is:

Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single

On Error Resume Next


Me.ScaleMode = 1 'Twips
sngFactor = Me.boxMaxDays.Width / mintDayDiff ' calculate max width of
timeline, divide by max no of days to get single value

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True
intStartDayDiff = Abs(DateDiff("d", Me.StartDate, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.EndDate, Me.StartDate))

If intStartDayDiff = 0 Then intStartDayDiff = 1
With Me.boxGrowForDate
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
.Width = intDayDiff * sngFactor
End With
Me.lblTotalDays.Left = Me.boxGrowForDate.Left
Me.lblTotalDays.Left = Me.boxGrowForDate.Left + Me.boxGrowForDate.Width
Me.lblTotalDays.Caption = intDayDiff & " day(s)"
Else
Me.boxGrowForDate.Visible = False
Me.lblTotalDays.Visible = False
End If

I don't see anything that would cause the width to be wrong.

The only thing that jumps out is that this line does not
look right:
Me.lblTotalDays.Left = Me.boxGrowForDate.Left +
Me.boxGrowForDate.Width

 

[NKA]

Hi Marshall,

Thanks for getting back to me. The line you are referring to is for a label
that is displayed to the right of the timeline (ie; 215 day(s)). Could it be
then that because the timeline for this particular task extends the full
width, when the label is applied it screws the formula? I don't see how
though...

 

[Duane Hookom]

This code looks familiar. I think the issue might be hidden by the On Error
Resume Next. It would be interesting to see what happens if you capture the
errors. I am thinking the high datediff might be pushing the control width
beyond the left margin which might cause an error so the size reverts back.

 

[Marshall Barton]

I didn't notice the On Error Resume Next until Duane pointed
out, but that certainly could be masking all kinds of
problems.

The label can not be messing with the formula, but if the
label is positioned on top of the text box it may make
things look strange. I am more woried about the width being
wrong and strongly suggest that you get rid of that Resume
Next.

 

[NKA]

Hi Duane,

Thank you for your response - how do I capture the errors (I'm not very good
unless it's in Wizard form I'm afraid!)?

 

[Duane Hookom]

Try see what happens when you place an apostrophe in front of the line:
' On Error Resume Next

 

[NKA]

Hi Duane,

Tried that! The report displayed OK. But when I scrolled to page 3 (the
page containing the largest dateline) - I got the following error:

Run-time error '2100'"
The control or subform control is too large for this location.

So I am totally stumped now!

Are you able to suggest an alternative that will display my timeline
correctly?

 

[Duane Hookom]

I'm not sure what the values are. However, try change the code to:
With Me.boxGrowForDate
.Width = 1 'add this line
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
.Width = intDayDiff * sngFactor
End With

 

[NKA]

Duane - I added the line as suggested, but now the timeline displayed is less
than that shown for 1 day!

To describe what is happening, I have taken 3 random records - all with
start date of Date().

1. End date 30/12/08 (27 days)
2. End date 31/03/09 (118 days)
3. End date 30/06/09 (209 days) - also value of mintDaysDiff

Without added .width line, the report looks like this

1. ---------- (27)
2. ---------------------------------------- (118)
3. -(209)

With added .width line = 1, the report looks like this

1. ---------- (27)
2. ---------------------------------------- (118)
3. ----(209)


..... But should look like this

1. ---------- (27)
2. ---------------------------------------- (118)
3.
--------------------------------------------------------------------------(209)

I really appreciate any help you can give with this (or an alternative that
will do the same thing!) as this is really frustrating me now!!

Many thanks in advance
NKA

 

[Duane Hookom]

I would set up a break point to fire when the day diff = 209.
If ... = 209 Then
0=0 'add a breakpoint on this line
End If
Watch the values by mousing over and stepping through the code pressing F8.

 

[Don Maloney]

Probably NOT an error in code.
I have the same issue when TWIPS goes ove 10000.
Even though tit is only about 7 inches.
When I manually change fro 10045 (my calculated value) to 9000 the code runs.
I think it is a MS access limitation

 

[Duane Hookom]

You might want to try dim the variables to longer...

Dim intStartDayDiff As Long
Dim intDayDiff As Long
Dim sngFactor As Double

On Error Resume Next


Me.ScaleMode = 1 'Twips
sngFactor = Me.boxMaxDays.Width / mintDayDiff ' calculate max width of
timeline, divide by max no of days to get single value

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True
intStartDayDiff = Abs(DateDiff("d", Me.StartDate, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.EndDate, Me.StartDate))

If intStartDayDiff = 0 Then intStartDayDiff = 1
With Me.boxGrowForDate
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
.Width = intDayDiff * sngFactor
End With
Me.lblTotalDays.Left = Me.boxGrowForDate.Left
Me.lblTotalDays.Left = Me.boxGrowForDate.Left + Me.boxGrowForDate.Width
Me.lblTotalDays.Caption = intDayDiff & " day(s)"
Else
Me.boxGrowForDate.Visible = False
Me.lblTotalDays.Visible = False
End If

 

[Don Maloney]

I just reran my application.
I have three subforms down the page
I set the top for each subform and calculate the height.
to compress the empty space between forms.
When the subform .top Twips
goes over 9360 I get the same error
if less or = 9360 no problem
9361 Error message.

Even though the form is at 11 inches when this happens and the height of the
form is 3" .
I tink it must be a bug/limitation in Access.

Access does limit the form size to a max of 22 x 22 inches

 

[Duane Hookom]

"three subforms"? I don't recall any mention of "subform" in previous
postings. Is it me or is this a new revelation? Do you really mean "subform"
or should this be "subreport"? I would never send a form to do the job of a
report.

 

[Don Maloney]

Duane,
I was commenting on the original question and why there is an error when
..top .width etc gets the error.
I was running an app and got it too with good code.

Why it happens.

Access limits the size of the form to 22 x 22.
If the form is created to be 10 by 10 then access uses this as the new limit
and will NOT allow a .width .top . height control to go over the limit.
SO, to fix this make the report width as large as possible in design mode.
I am not sure what will happen in printing.
THen when you run your app the width number should be ok since 11823 twips
is only 8.2 inches.
For this case make the width of the form 11 1/2 inches and print landscape.
My guess is that if the report/form is 11 inches wide the twips will fit and
the error will go away.