TimeLine Representation on Forms

[Guest]

I have a database from which I print timelines. The database fields include
start date and duration...the duration is expressed in weeks. When I print a
report, I have no problems drawing the timeline through an "On Format" Event
Procedure. However, that same Event is not available on a Form.
Unfortunately, my users really want to graphically see the timeline as
opposed to just seeing dates.

Does anyone know how I can "draw" a solid box that represents the start date
and duration on a Form?

Any help would be most appreciated.
Thanks!

 

[Marshall Barton]

I have a database from which I print timelines. The database fields include
start date and duration...the duration is expressed in weeks. When I print a
report, I have no problems drawing the timeline through an "On Format" Event
Procedure. However, that same Event is not available on a Form.
Unfortunately, my users really want to graphically see the timeline as
opposed to just seeing dates.

Does anyone know how I can "draw" a solid box that represents the start date
and duration on a Form?

Use the form's Current event.

You can maipulate the size and position of a control
(rectangle?) simply by manipulating its properties.

Not sure I inderstand your scenario very clearly, but it
should be mostly a matter of scaling.

Me.rect.Left = (startdate - lowestdate) / (highestdate -
lowestdate) * Me.Width
Me.rect.Width = duration / (highestdate - lowestdate) *
Me.Width

 

[Guest]

I guess when one has an idea clearly implanted in one's mind, one tends to
not clearly or completely write what it is one needs.

I have a project which consists of about 40 tasks. Each of the steps has
different start dates and durations. My users would like to be able to see
on the screen a line for each task and a graphical bar representing the start
date and duration for each.

I have been playing with some stuff, but noted that if I create a continuous
form, the bars are the same for each task.

 

[Rob Oldfield]

I don't think that you can get a graphical object to be different sizes on
each record of a continuous form. How about this idea (not very high tech
and may well not look as good as you were hoping)?

Stick with the idea of a continuous form but have an additional field
(called KludgeIt maybe). Before you display the form update KludgeIt to a
string...

Start, End, KludgeIt
1/1/5, 15/1/5, "---------"
16/1/5, 30/1/5, " --------"
16/1/5, 14/2/5, " ---------------"

That would give you the basic functionality, maybe playing with the font and
character used to make it look prettier (e.g. there's a big black square in
the webdings font)

Though just had another thought, possible to do it as a chart? I don't
think the native Access ones are going to do the job but the Stock charts in
Excel are coming close (although they're all vertical). Maybe something
better available if you have a later version?

 

[Rob Oldfield]

There again, changed my mind on the Excel chart option. A stacked bar with
the left most one set to have no fill and no border gives you what you're
after I think. (Though having never played with them all that much I'm not
sure how easy it's going to be to get date labels on the axis.)

 

[Marshall Barton]

I hadn't grasped the Continuous form aspect of the question
before. This makes the problem a lot trickier. But, if
you've done it on a report, at least you understand the
scaling issues (which I have a difficult time explaining).

The only time I had to do this kind of thing was to make a
Ghantt chart, which sounds like the same kind of thing
you're trying to do. In that case, I used a font with a
solid block character and the String function to make the
bar. Use a text box that spans the maximum width of the
time line:
=String(((startdate - lowestdate) / (highestdate -
lowestdate) * textbox.Width) / XX, " ") & String((duration
/ (highestdate - lowestdate) * textbox.Width) / XX, "K")

Where XX represents the width of the space character in
twips and K represents the character in the solid block font
that has the same width as a space character (you'll have to
play around with it to figure that out).

The solid block font can be downloaded from:
http://www.mvps.org/access/forms/frm0055.htm

 

[Guest]

Rob and Marshall...you guys are geniuses! Never would I have thought to use
a special font as text to represent the bar! I was stuck with the thought of
somehow manipulating a rectangle which, as you have both pointed out, is
seemingly impossible to do on a continuous form.

Thanks for the help!