Timelines

W

Wierdbeard65

Hi,

I'm trying to write a scheduling application for my company. I need a view a
bit like a GANTT chart. On the vertical axis will be resources (Instructors,
Rooms etc) and on the horizontal axis is time, to a resolution of 1 day.

The events I want to record will be between 1 and 5 days long.

Ideally, the bar should contain a sub-form so I can edit data relating to
the event in-situ, but otherwise a coloured bar is fine (I need to be able to
specify the colour) with text in it. Clicking on an event would then bring up
a dialog with event details.

Can anyone offer any advice on how to do this, preferably without installing
any extras? I had a look at some ActiveX grid controls, but:
1) I'm not sure how to use them
2) I have no budget for a paid-for solution.

My database contains tables for Instructors, Rooms and courses. I then have
a table to link the three together to form an event - this is what I need to
display :)

Many thanks for any and all suggestions....
 
R

ryguy7272

M

Marshall Barton

Wierdbeard65 said:
I'm trying to write a scheduling application for my company. I need a view a
bit like a GANTT chart. On the vertical axis will be resources (Instructors,
Rooms etc) and on the horizontal axis is time, to a resolution of 1 day.

The events I want to record will be between 1 and 5 days long.

Ideally, the bar should contain a sub-form so I can edit data relating to
the event in-situ, but otherwise a coloured bar is fine (I need to be able to
specify the colour) with text in it. Clicking on an event would then bring up
a dialog with event details.

Can anyone offer any advice on how to do this, preferably without installing
any extras? I had a look at some ActiveX grid controls, but:
1) I'm not sure how to use them
2) I have no budget for a paid-for solution.

My database contains tables for Instructors, Rooms and courses. I then have
a table to link the three together to form an event - this is what I need to
display :)
Click to expand...


I've done something similar on a continuous form. After you
determine the beginning and ending points of the timeline,
you can scale the dates as the number of days after the
start point. Then the detail section can use a text box of
the appropriate width with an expression like:

=String(s*DateDiff("d", [BegDate],[Start]), " ") &
String(s*DateDiff("d", [BegDate], [End Date]), "A")

where s is the scale factor for the Widtth of the timeline
text box, its FontSize and the character width of the block
character displayed for an "A". If the "A" character is too
wide choose a narrower block character ("I" is the thinest).
The color of the event bar is controled by the text box's
ForeColor property.
 
W

Wierdbeard65

Thanks to both of you for taking the time to respond!

Unfortunately, I can't afford to have additional fonts to install, not can I
have a paid-for solution and I'm afrad Access is the way I need to go, for
various reasons!

Anyway, I found an example online which does what I want, but produces a
report not a form. It uses grouping, which isn't available on a form, as far
as I am aware. This is ok, except the mouse then becomes a zoom toom :-/ I
need to be able to click on a bar and open its underlying properties (or
double-click, or right-click and add to the context-sensitive).

Can anyone tell me how to either do grouping in a form, or trigger the
mouse-click event in a report?

Thanks!
 
M

Marshall Barton

Wierdbeard65 said:
Unfortunately, I can't afford to have additional fonts to install, not can I
have a paid-for solution and I'm afrad Access is the way I need to go, for
various reasons!
Click to expand...

In what way can you not afford a free public domain font?
The installation of a font should just be a matter of
copying the file to Windows Font folder so it's hardly a
burden. Your whole problem becomes fairly simple if you can
use the solid black font.
Anyway, I found an example online which does what I want, but produces a
report not a form. It uses grouping, which isn't available on a form, as far
as I am aware. This is ok, except the mouse then becomes a zoom toom :-/ I
need to be able to click on a bar and open its underlying properties (or
double-click, or right-click and add to the context-sensitive).

Can anyone tell me how to either do grouping in a form, or trigger the
mouse-click event in a report?
Click to expand...


Grouping in a continuous form can be fudged in a very
restricted way by playing some messy games with the form's
record source query but the result is usually
unsatisfactory. Even you wanted to pursue that, it will not
get you different length controls at different horizontal
positions. OTOH, you have not explained why you need
grouping so it may or may not be an onerous exercise.

The reason it's easy to do in a report is because you can
use VBA code in the detail section's Format event to adjust
things differently for each detail record, but then you can
not have a clickable button. You can do all those things in
a form, but only if the form is displayed in Single view so
no grid like appearance.

The only way I have found to get the effect I think you want
is a continuous form. However you can not use VBA code to
adjust each detail's control properties (i.e. Left, Width)
on a continuous form, hence simulating it with strings of
spaces and solid block characters.
 
W

Wierdbeard65

Thanks again, Marshall.

I think you have understood my problem, although I'm not certain!

It seems that the report somehow uses grouping to cause all the records in a
particular group to appear on "top" of each other. Combined with the onformat
event and we get the effect of a timeline with all the events for the grouped
field to appear on one line.

My concern with the fonts option is that the install is required. My
application will be on a network share and various people will access it. If
they all have to install the font, it will be a pain - although if that's the
only solution...! If I go with the font solution, can I alter the colour of
the bar on the fly? Can I also have text appear on the bar? I will
investigate this further :)

The important thing (for me) is to have a method by which the user can open
a modal form from the bar (details of the associated event). In a lot of
ways, a right-click (context sensitive) option may well be preferable anyway.
(That way I can have a delete option as well as a "new" option.) Can this be
done on a report?
 
M

Marshall Barton

Wierdbeard65 said:
I think you have understood my problem, although I'm not certain!

It seems that the report somehow uses grouping to cause all the records in a
particular group to appear on "top" of each other. Combined with the onformat
event and we get the effect of a timeline with all the events for the grouped
field to appear on one line.
Click to expand...

All kind of things can be done in reports that can not be
done anywhere else. Using grouping to lay data from
multiple records on top of each other is a report only
feature. Having clickable controls on a report has never
been a feature of Access reports.

I thought you were trying to display a Ghantt chart kind of
thing and trying to get a form to deal with multiple but
different timelines is where the difficulty lies.

As I said before, you can do all kinds of things with a form
in Single view, but you can not then use simple bound
controls and multiple detail records. Instead you could use
a recordset to retrieve the needed data and code to place a
bunch of text boxes anywhere on the form with whatever
property values you need. This is what I think you may need
if all you want is a single timeline.

If you need an arbitrary number of timelines, then you will
need to make a choice between using a third party tool or
dealing with the font issue.

I do not have a perfectly clear picture of what you are
trying to accomplish so I am kind of shooting in the dark
with only a low power light inadequately illuminating the
target..

My concern with the fonts option is that the install is required. My
application will be on a network share and various people will access it. If
they all have to install the font, it will be a pain - although if that's the
only solution...! If I go with the font solution, can I alter the colour of
the bar on the fly? Can I also have text appear on the bar? I will
investigate this further :)
Click to expand...

There are ways to deal with the install issue, but if you
can use a form in Single view, you won't need any of that.

If you need multiple timelines, then the color and text are
another level of complications that may or may not be doable
depending on the exact details of how you want things
displayed.

The important thing (for me) is to have a method by which the user can open
a modal form from the bar (details of the associated event). In a lot of
ways, a right-click (context sensitive) option may well be preferable anyway.
(That way I can have a delete option as well as a "new" option.) Can this be
done on a report?
Click to expand...

No, not on a report. There is no way that I ever head of to
determine where the right click occured.

Of course clicking buttons on a form is easy to do.
 
W

Wierdbeard65

Thanks again for your reply.

The application is a scheduling app for a training centre. The view I am
trying to create will have the dates across the top and the rooms down the
side. Whenever a course is scheduled, there needs to be a bar, next to the
room's name and under the corresponding dates. The bar's colour will relate
to other details and the text will be the name of the course. Obviously, any
given room will have many courses scheduled (at different times) so although
it's a GANTT - style display, I don't want each course on a separate line.
One room should be one row!!

I need the user to be able to add, modify and delete courses from this view.
I have a form that contains the course details, which I want to be able to
display from the timeline. So, either double-clicking or right-clicking the
course in the timeline should give access to the details. I don't mind if the
new course generation is done from a button, not am I bothered if the delete
button is on the details form (although if I'm going for a context sensitive
menu, then it would make sense to put them here.

What I don't understand (and have posted elsewhere) is that the Access 2007
docs say:
"a) In the Navigation Pane, right-click the form or report where you want
the shortcut menu to appear, and then click Design View.

b) On the Design tab, in the Show/Hide group, click Property Sheet.

c) Select the control or object to which you want to attach the shortcut
menu.

Note To select the entire object, select Form or Report from the list at
the top of the Property Sheet task pane."

The whole help page clearly implies that menus can be attached to controls
of reports or forms, yet when I try, they don't appear :-( If I attach it to
the whole report, then it appears, but then I don't know which course has
been selected :-(

I'm beginning to think that I'm going to have to use code to create the
whole screen (on a blank form) dynamically, but I had hoped to avoid this.
 
M

Marshall Barton

Wierdbeard65 said:
The application is a scheduling app for a training centre. The view I am
trying to create will have the dates across the top and the rooms down the
side. Whenever a course is scheduled, there needs to be a bar, next to the
room's name and under the corresponding dates. The bar's colour will relate
to other details and the text will be the name of the course. Obviously, any
given room will have many courses scheduled (at different times) so although
it's a GANTT - style display, I don't want each course on a separate line.
One room should be one row!!
Click to expand...

That's a grid kind of thing, not a ghantt chart. So,
whatever I've been talking about does not apply. I have
never tried to do a grid type form with all the things you
want to do so I don't know off the top of my head how it
might be done. Maybe a spreadsheet would be easier to deal
with??

I need the user to be able to add, modify and delete courses from this view.
I have a form that contains the course details, which I want to be able to
display from the timeline. So, either double-clicking or right-clicking the
course in the timeline should give access to the details. I don't mind if the
new course generation is done from a button, not am I bothered if the delete
button is on the details form (although if I'm going for a context sensitive
menu, then it would make sense to put them here.

What I don't understand (and have posted elsewhere) is that the Access 2007
docs say:
"a) In the Navigation Pane, right-click the form or report where you want
the shortcut menu to appear, and then click Design View.

b) On the Design tab, in the Show/Hide group, click Property Sheet.

c) Select the control or object to which you want to attach the shortcut
menu.

Note To select the entire object, select Form or Report from the list at
the top of the Property Sheet task pane."

The whole help page clearly implies that menus can be attached to controls
of reports or forms, yet when I try, they don't appear :-( If I attach it to
the whole report, then it appears, but then I don't know which course has
been selected :-(
Click to expand...

AFAIK that only applies to a ReportView display (new in
A2007), which severely limits what you can do (e.g. VBA code
is not executed) so I don't see how you can get anything
like the appearance you want in that view.

OTOH I have not used A2007 all that much so there may be
nuances that I am unaware of.
I'm beginning to think that I'm going to have to use code to create the
whole screen (on a blank form) dynamically, but I had hoped to avoid this.
Click to expand...

You probably will have to do that, but remember that you are
limited to 754 controls on a form. And don't fall into the
trap of using code to create/manipulate a form in design
view. Everything must be done in form view using precreated
controls and manipulating their properties.

If the course times and durations are all regular so
everything fits nicely into a grid, I think I would first
explore using a datasheet or continuous form based on a
crosstab query. Conditional Formatting can get you a few
different colors
 
W

Wierdbeard65

Thanks again!

I have gone down the route of creating the form on the fly and so far so
good. You say "And don't fall into the trap..." what is the issue with doing
this? It's working so far (although I can't modify on the fly) so I'm gonna
have to put a few "spares" on the form and hide them.
 
M

Marshall Barton

Wierdbeard65 said:
I have gone down the route of creating the form on the fly and so far so
good. You say "And don't fall into the trap..." what is the issue with doing
this? It's working so far (although I can't modify on the fly) so I'm gonna
have to put a few "spares" on the form and hide them.
Click to expand...


Operations like CreateForm and CreateControl are only part
of Access so you can create design time wizard like
procedures. They are not intended as a runtime solution to
anything.

Making design changes at runtime is fraught with dangers
because any persistent change requires the modified object
to be saved to the front end database file. This is a heavy
duty operation that has a significant chance of corrupting
the file. Even if you are willing to live with that risk,
it will always cause bloat that in turn will require more
frequent Compact operations. Compact should never be done
lightly (Compact on Close is a BAD thing), because it
rearranges every byte in the file and can destroy it if some
odd event should occur. To deal with all those risks, you
have to have an extremely robust backup procedure that is
often more trouble than you think you will save by making
the design changes in the first place.

Another serious consideration is that your approach will not
be allowed if/when you decide to distribute your application
as an MDE file.

Of relatively small importance, using a pre created form
with a bunch of pre created invisible controls is actually a
little easier than creating the form and controls on the
fly.
 
C

ChrisO

What you are asking for can be done on an Access Form but it requires a lot
of code.

As a starter you can refer to

this link which also has a link to what I think was the original idea.
 
C

ChrisO

What you are asking for can be done on an Access Form but it requires a lot
of code.

As a starter you can refer to

this link which also has a link to what I think was the original idea.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook meeting location auto prompts with a suggestion list - need to disable 0
Teams Rooms Basic Licence 0
Office JS api to handle redo/undo , delete event for content control class 0
Linking Child Timelines to Multiple Parents 0
Work Budget Salary vs Real Time Salary 0
Paste Link and Link Text From Browser to Word 0
Gantt chart duration not showing 0
Dumb question alert! What program to use? 0

Top