Graphical Representation on each Row

[treva26]

I have a table of projects with a start and end date for each.
I want to add a field to my form that shows a graphical representation of when in the year that each project is scheduled for.
It would be like a bar with a section coloured in.


For example:

project 1 is scheduled for Jan 1st - Feb 15th
so the bar would be red for the first part then blue for the rest

project 2 is scheduled for July 1st to Aug 5th
so only the middle part is red


I then make a form showing the bars lined up in a column so I can see which ones overlap.

How could I go about doing something like that?

 

[YevS]

If it is a single form then it is pretty easy.

Just convert cm into pixils and change the bar length based on % complete.

I found that on my setup 10cm = 5670 pixiles.

So if you treat 5670 as 100%.
Then convert your sheduled days to 100% (1 year = 100)

Then you need to do some playing with the bars. Basically create 3 bars (line objects with x size border.

2 blue bars on either side and 1 red bar in the middle

example 1

Set 1st (blue) bar to 0 px length code: ProgressLine.Width = 0
Set 2nd (red) bar to:

(5670 / 100) your 1% in pixils

(356 / 100) your 1% in days of the year

DateDiff("y", Jan 1st, Feb 15th) Your actual days for the project

Based on that calculate what those days mean in pixils and make the red bar that long.

Set 3rd (blue) bar for the remainder of the length.

Then all you need to do is set the .Left property for the bars and your done.

One downside is you cant do that for continuos forms.

 

[treva26]

thats a shame, cos i need it on a continous form...

is there another way?

can i use a chart?

can i somehow get vba to cycle thru all the rows and set each length value?

 

[treva26]

Would it help if i could get the width and left values calculated in a query and base the continous form on that query?

 

[YevS]

Well, thats tricky. The probelm is, once you start changing properties of an object on a continuos form it refreshes that object for every row. So the only way to do this would be to write an object that reacts to the data (control source). Unfortunately access does not have such an object as default. So you will either have to make one or look for one on the internet. Of course if you get it from the internet, you also have to install it for every user that you anticipate is going to use your system or make a runtime application (since they wont have this object with a default version of access).

You could make a rudamentary solution using conditional formatting, but you cant change the length of the textboxes with that method, only the background colour.

Say make 10 text boxes with the same control source (% complete or whatever) and change each ones back & foreground colour based on the source in the box using conditional formatting.

You have to bear in mind though, that conditional format only has 3 clauses. And if you go into code and give it more than 3 itll switch that control to the different sets of 3 for all your rows.

Think of it as LED lights lighting up based on the value in your source.

 

[treva26]

yea i could have 52 boxes, 1 for each week of the year.
any that are in the affected timescale will turn green.

thanks for your help!