Trouble with conditional formatting in calculated controls 1

[Bresart]

Hi, i have a form with a continuous subform based on a sowing calendar table which has 6 fields: FromDay, FromMonth, ToDay and ToMonth, ExactDay, ExactMonth (that is, the sowing can be an exact day or in a short period of time). In the subform I want to represent where is the sowing moment into the year, through an horizontal band marked with 'ene', 'feb', all along it until 'dec'.

For representing where is in that band the sowing moment I want the band shows a different color in this period or day: for that, I have put 365 textboxes over the band all them with transparent background, and with conditional formatting: if its value is 1 its background is red, if its value is 0 there's no format condition. For stablishing its value, in its Control source property there's a calling to a function: for example, in the first textbox corresponding to the day 1 ene it is:

= FillingCalendarBand(1;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth])

This function calculates if the day 1 ene is into the sowing period, and return the value 0 in negative case and the value 1 in positive case. And so on for the 365 days (365 textboxes). I haven't found a better way of going around the matter of representing sowing period.

The problem could seem what the link

http://allenbrowne.com/bug-05.html

said about conditional formatting in calculated controls: indeed it's an endless loop that recalculates time and time again the day 1 ene case (I have put a MsgBox in the function that gives the first argument received -day number from 1 to 365- and MsgBox with number 1 appears without stopping). But I have removed the conditional formatting and exactly the same problem keeps being: the MsgBox with number 1 keeps appearing without stopping. It was only a test because I really need the conditional formatting to implement the calendar band in this way. I have also tested the conditional formatting programmatically, with the same result.

This problem only occurs in Access 2002 in Windows XP without SP3, i haven't yet tested in Access 2003. The working is correct in Access 2000 (Windows XP with SP2), Access 2002 (Windows XP with SP3), Access 2003 (Windows XP with SP2, Windows Vista) and Access 2007 (Windows XP with SP2, Windows Vista).

Can anybody say where is the problem? Or if there can be a better way of doing?

Thanks in advance for any help.

 

[MajP]

This is a lot of overhead. You are running 365 functions * the number of iterations per function * number of records. I would try to think of a better way. If the subform was not continous you could us a Microsoft Progress Bar 6.0. I have a hard time picturing your layout. Could you post a picture or a link to a picture.

 

[MajP]

I think if this was me and I needed a highlighted band on a continous form, I would use an unbounded sub form. Maybe show ten records in the subform at a time. But now I can position a highlight where I want it, and the size I want it. This will require a little more coding but make managing it easier.

 

[Bresart]

Thanks MajP.

What is a highlighted band? Can you say me how to create it?

This is the capture of my form (spanish):

http://www.flickr.com/photos/_rufus_/3131344247/sizes/o/

from Access 2000. In newer Access versions the red zone (sowing period) is a continuous band instead of segments one, i think because the textboxes transparent borders become with the same color of the textboxes background.

 

[MajP]

I have found that this sometimes will work. Instead of having a calculated control with a control source of
= FillingCalendarBand(1;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth])

Move this into the conditional formatting and get rid of the calculated control. In the conditional formatting

Expression is:
FillingCalendarBand(1;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth])= -1

 

[Bresart]

Thanks MajP.

What you suggest would involve to change the function FillingCalendarBand, because it always gives a value so the conditional formatting would be applied always, for the 365 textboxes.

However, i have tested that in one of the textboxes and no formatting is ever applied.

 

[MajP]

I assumed you were using a boolean (-1 true, 0 false). It looks like you are returning an integer so
FillingCalendarBand(1;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth])= 1

 

[Bresart]

I have observed that the highlighted band works for the 4 first days (removing all the rest of textboxes). I don't know what that means.




I also have tested putting the function

= FillingCalendarBand(XXX;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth])

in another textbox without conditional formatting, and in the band textbox (with conditional formatting)

=[ReaderXXX]

in the Control source property, where XXX is each value between 1 and 365. But it neither works, perhaps because anything preceded by '=' makes a calculated control. Can a control catch the value of another one without being a calculated control?





There can be another possibility: adding to the query which the form is based in 365 fields more, each one with the function

= FillingCalendarBand(XXX;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth])


but i see i can't add an indefined number of fields to a query. Is that right? Or is there a way of avoiding this limitation?

The solution would be to put the half of 365 days instead all the 365 days, and then to highlight the days in groups of two.

 

[MajP]

You do not seem to understand what I am saying.

YOU DO NOT NEED A CALCULATED CONTROL. PUT NOTHING IN THE CONTROL SOURCE.

In the conditional formatting for the first control

Expression is:

FillingCalendarBand(1;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth]) = 1

For the second
Expression is:
= FillingCalendarBand(2;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth]) = 1

THE FUNCTION IS ONLY IN THE CONDITIONAL FORMATTING. IT IS NOT IN THE CONTROL. IF IT EQUALS 1 THEN THE CONTROL CHANGES COLOR.

 

[MajP]

Change this to read

For the second
Expression is:
FillingCalendarBand(2;[FromDay];[FromMonth];[ToDay];[ToMonth];[ExactDay];[ExactMonth]) = 1

 

[MajP]

Here is a working demo

http://www.4shared.com/file/78045652/3d7a7a22/Date_Band.html?dirPwdVerified=7024d9a4

 

[Bresart]

Thanks MajP. It works.