Timeline Report from Access Web 
Author Message
 Timeline Report from Access Web

I've emailed Dev about this, but I haven't received a response (I can't
imagine the volume of email he gets from that site; I'm not faulting him
for not responding to every question).  I was very e{*filter*}d when I found
this timeline code.  The code is very smartly written and provides a very
creative solution to something I really needed Access to do.  There's a
bug, though, that I can't figure out.

The timeline boxes work properly 9 times out of 10, but occasionally the
timeline box doesn't start where it should.  I can't find any pattern to
it.  If you open up the DB at
http://www.*-*-*.com/ , take a
look at entries 4 and 5.  They have the exact same dates, but they appear
on the timeline as starting at different dates.

After I downloaded the code, I added a couple things (BTW the error occurs
in the original DB, so I know it's nothing I've done) like a progress field
and a progress bar that appears below each timeline bar.  Now here's the
strange thing:  the progress bar always appears where it should, even when
the other bar is in error.  That's how I can quickly spot the error; the
timeline bar and progress bar aren't starting at the same point.  To get
the progress bar to scale, I just multiplied the percent complete by the
scaling factor.

It looks like this (if the progress is 50%):

    ================   <--Timeline bar
                                                           =======  <--
Progress bar

When it should look like this:

    ================  <-- Timeline bar
    =======  <-- Progress bar

My question has two parts: 1) Why does the original DB show boxes with the
same starts dates at different locations in the timeline, and 2) Why does
the progress bar work correctly every time?

The code as I've altered it appears below (original DB file at
http://www.*-*-*.com/ ):

Option Compare Database
Option Explicit

Private mdatEarliest As Date
Private mdatLatest As Date
Private mintDayDiff As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single
Dim pctFactor As Single

  On Error Resume Next

  Me.ScaleMode = 1 'Twips
  sngFactor = Me.boxMaxDays.Width / mintDayDiff

  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.Caption = intDayDiff & " Day(s)"
  Else '
    Me.boxGrowForDate.Visible = False
    Me.lblTotalDays.Visible = False
  End If

  If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
    Me.boxGrowForPercent.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.boxGrowForPercent
      .Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
      .Width = intDayDiff * sngFactor * [PercentComplete]
    End With
    Me.lblTotalDays.Left = Me.boxGrowForDate.Left
    Me.lblTotalDays.Caption = intDayDiff & " Day(s)"
  Else '
    Me.boxGrowForDate.Visible = False
    Me.lblTotalDays.Visible = False
  End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT Min([StartDate]) AS MinOfStartDate " _
                & " FROM qryTimelineInitiative2", dbOpenSnapshot)
  If rs.RecordCount > 0 Then
    mdatEarliest = rs!MinOfStartDate
  End If
  Set rs = db.OpenRecordset("SELECT Max([ExpCloseDate]) " _
                & "AS MaxOfEndDate FROM qryTimelineInitiative2",
dbOpenSnapshot)
  If rs.RecordCount > 0 Then
    mdatLatest = rs!MaxOfEndDate
  End If

  mintDayDiff = DateDiff("d", mdatEarliest, mdatLatest)

  Me.txtMinStartDate.Caption = Format(mdatEarliest, "mm/dd/yyyy")
  Me.txtMaxEndDate.Caption = Format(mdatLatest, "mm/dd/yyyy")
  Set rs = Nothing
  Set db = Nothing
End Sub

Thank you, thank you, thank you!!!!!!!



Sun, 29 Sep 2002 03:00:00 GMT  
 Timeline Report from Access Web


: I've emailed Dev about this, but I haven't received a response (I can't
: imagine the volume of email he gets from that site; I'm not faulting him
: for not responding to every question).

Thank you. I'm indeed swamped and I thank you for posting the question here.

: I was very e{*filter*}d when I found
: this timeline code.  The code is very smartly written and provides a very
: creative solution to something I really needed Access to do.  There's a
: bug, though, that I can't figure out.
:
: The timeline boxes work properly 9 times out of 10, but occasionally the
: timeline box doesn't start where it should.  I can't find any pattern to
: it.  If you open up the DB at
: http://www.*-*-*.com/ , take a
: look at entries 4 and 5.  They have the exact same dates, but they appear
: on the timeline as starting at different dates.

I agree, there's something weird going on that's not so obvious at first
glance.  It seems as if after record ID 4, the dates being read off actually
belong to record ID 6 but the ID remains 5. So it's as if portion of the
record is skipping the ID=5 altogether.

I haven't yet figured out what's going on exactly and will post back here
any new findings.

 Thanks
 -- Dev



Sun, 29 Sep 2002 03:00:00 GMT  
 Timeline Report from Access Web

: It seems as if after record ID 4, the dates being read off actually
: belong to record ID 6 but the ID remains 5. So it's as if portion of the
: record is skipping the ID=5 altogether.

Hmm.. I posted that too soon. The problem is that after processing id 3, the
assignmnet to the Width property is ignored for id 4, hence the box retains
the left property from the previous record, ID=3.

Still looking for the answer.

 -- Dev



Sun, 29 Sep 2002 03:00:00 GMT  
 Timeline Report from Access Web
Ok, it seems to be related to the values of mdatLatest and mdatEarliest.
They are max & min respectively of the date field and apparently this is
what causing the previous left value to carry over to the next record if
mdatLatest and mdatEarliest belong to the same record.

For a temporary fix, change the code in Report_Open so that these vars are
looking at fixed dates instead, for example.

mdatEarliest = #1/1/1997# 'rs!MinOfStartDate
and
mdatLatest = #1/1/2001# 'rs!MaxOfEndDate

  -- Dev



Sun, 29 Sep 2002 03:00:00 GMT  
 Timeline Report from Access Web
Within the Detail_Format code you can add the following prior to the If
statement.

Me.boxGrowForDate.Width = 0

It appears to me (I could be wrong) that the problem is within the width
property and by resetting to 0 everything works as expected.

Let me know if I misunderstand something...
--
Doug
Reply to is anti-spammed remove the "z" from email


Quote:
> Ok, it seems to be related to the values of mdatLatest and mdatEarliest.
> They are max & min respectively of the date field and apparently this is
> what causing the previous left value to carry over to the next record if
> mdatLatest and mdatEarliest belong to the same record.

> For a temporary fix, change the code in Report_Open so that these vars are
> looking at fixed dates instead, for example.

> mdatEarliest = #1/1/1997# 'rs!MinOfStartDate
> and
> mdatLatest = #1/1/2001# 'rs!MaxOfEndDate

>   -- Dev



Wed, 02 Oct 2002 03:00:00 GMT  
 Timeline Report from Access Web
Seems to fix the problem; report works perfectly now!  Thanks!

Dev, thanks for the original code and for taking time to look into the
problem.

Stevan



Quote:
> Within the Detail_Format code you can add the following prior to the If
> statement.

> Me.boxGrowForDate.Width = 0

[snip]


Sat, 05 Oct 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. VB to Execute Access Report - Access report displayed on Web

2. Timeline In a Report

3. Timeline milestones - Access mdb file

4. Access Report on the Web

5. Accessing attributes of a Crystal Report (8.5) as a web service

6. Accessing Crsytal Reports through Web Browser

7. Access 2000, CR8 & web reporting

8. Trouble accessing Databases on another server with Web Report Engine

9. blank report using web using Web Forms Viewer

10. from visual basic to access report(send query to access report))

11. Printing Access reports or using Access to print/preview reports

12. Active Reports, Crystal other Web report writers??

 

 
Powered by phpBB® Forum Software