Conditional Formatting crippling form performance

[VNVfan]

Hello everyone. This is my first post after being a lurker for a number of years - you guys have taught me a huge amount, so huge thanks everyone

Now I have an issue that I can't seem to find a solution on here for and would like some advice - apologies if this question has already been answered/solved and I just didn't find it, if so if someone would be so kind as to point me at the thread I'd be grateful.

I'm trying to build a calendar-like form to show where staff are in the current month (it's a personnel database) - I.E if they're on holiday or off sick. I have a continuous form that contains 32 text boxes in the "Detail" section, and the maximum number of rows is around 10 (with the current data). The first text box is a bound name column, and the next 31 text boxes are unbound with a function I wrote to figure out where a person is for every day in the current month, which updates each text box to one of 4 values for each "day"; Null if the date is invalid (such as the text box for the 31st in November) or a weekend day, 1 if a record is found for the staff member in the holiday table, 2 if a record is found for the staff member in the sickness table, and 0 if no record is found in either table.

When I load the form, it takes between 21 and 23 seconds to load and calculate all the values (not amazing performance, but acceptable).

What I want is for all of the boxes to be blank where the values are 0, blue where the value is 1, and red where the value is 2. (Ignore weekend and invalid dates for now, I'm handling them separately - though I don't think this is causing my problem because I haven't written it in yet). To do this, I added simple conditional formatting to the 31 text boxes to change the font and fill colour to the relevant colours based on the text box value, then set the default value to a white fore colour and back colour.

My problem is that when I do this, the form takes unacceptably long to load (I stopped timing after 3 and a half minutes and crashed it out). Stranger still, after the first 20-30 seconds what I can see on the form is that it has already calculated all the values and coloured them appropriately but the VB code window still appears to be running through the code to calculate the day values - but all I've changed is to add conditional formatting! If I remove the conditional formatting, the form reverts back to only taking 20-ish seconds to load.

If anyone could help me figure out why adding CF to the form seems to be causing this I'd be very grateful.

Oh, I'm using Access 2003 by the way.

 

[vbaInet]

20 to 30 seconds is unacceptable. You should be looking to solve this problem first.

I'm guessing you've used DLookups all over your form?

 

[VNVfan]

I agree that 20-30 seconds is pretty awful, but for the amount of use this form will get (probably around once or twice a month), it's acceptable for it's purpose. Though I won't refuse any help for improving that!

I've not used any DLookups, but my code might function in the same way (forgive me for my ignorance, I've learned everything I know of Access and VBA as I've gone along).

I pass the function I wrote 3 numerical values, the first is the unique reference number for the staff member, the second is the month number and the third is the day of the month to look for. It uses the latter two values to construct what date it's looking for, tests if it's valid or a weekend date, and if not it searches through the holiday and sickness tables using the unique ref no and returns whatever value is appropriate to its findings.

 

[vbaInet]

It's a domino effect. If it's taking long to load it will take even longer for the CF to take effect.

So every textbox is filled in through code? And this is done in the Load and Current events?

 

[VNVfan]

The form is bound to the staff table, and filtered by team number.

There are 3 text boxes that are filled in using the OnLoad event which have the current month number (I did it this way as the eventual plan was to have it current month on load, but having the option to move back and forth through months by changing this value), the team number for use in the filter (brought forward from another form) and the team name (ditto, though not used at the moment).

In the Form Detail section, the first box is a combination of the first and last name fields from the table, and the other 31 text boxes call the function I mentioned previously with the values set for each box - I.E. the first "day" box has "=fWhereAmI([ID_AN],[txtCurMonth],1)" and the 31st has "=fWhereAmI([ID_AN],[txtCurMonth],31)"

I found if I tried to set the text box values from VBA it would set all the boxes in a "column" to the same value. This is my first time using continuous forms, and I didn't (and still don't) know how to refer to each row/record's text box in VBA.

 

[vbaInet]

But you are still using VBA to set the values of the textbox. fWhereAmI() is a function which I believe opens a recordset, performs a search for the record and returns the respective value.

It would be easier to advise if I can see a stripped down version of your db.

 

[VNVfan]

Apologies, even though I edited the last post I still missed that I forgot to say that the function is in the Control Source property of the text box - not set via VBA... if that was even what you were referring to.

I'm just stripping out data and adding the relevant stuff to a new database. Bear with me - and thank you for trying to help.

 

[VNVfan]

Alright, this should work stand-alone (at least it does for me).

Also, please don't get mad but when I said there were only 3 text boxes set in VB I totally forgot about the "label" text boxes in the form header.

I'm totally expecting you to rip this apart, as like I alluded to I'm pretty much a noob at this! But please don't shout at me

 

[vbaInet]

The value is set via VBA, not the Control Source. A function is VBA, that's what I mean.

Oki doki!

 

[vbaInet]

Alright, this should work stand-alone (at least it does for me).

Also, please don't get mad but when I said there were only 3 text boxes set in VB I totally forgot about the "label" text boxes in the form header.

I'm totally expecting you to rip this apart, as like I alluded to I'm pretty much a noob at this! But please don't shout at me

I'm advising lots of other people to so it will take a while before I get time to look at it. Hopefully later today.

Be prepared for my virtual shouting Just kidding!

 

[VNVfan]

I get what you mean now.

I have no problem waiting, I don't really have much of a deadline for this myself anyway. One of my users requested it, but I'm mostly doing it just to figure out how - it's the only way I learn!

Thanks again!

 

[VNVfan]

Hi vbaInet, I've been doing some thinking/messing around in the stripped-down database that I uploaded and have found a kinda workaround.

What I've done is create a query that does the calculations and dumps the data into a local table and bound the form to that instead. The query only takes a few seconds to run, but because the conditional formatting is based on "flat" data rather than a calculation, the form opens instantly. I also have a delete query that clears the table before it runs the append query.

The downside to this is that each time I open the form it obviously increases the database size. It's only about 4KB each time though, would you still see that as a problem?

Regardless of all of the above, I would still like to hear your suggestions (assuming you have any) about the way I was originally tackling this - when you get around to it.

 

[Galaxiom]

Why not bind the form to the query?

Is there some particular reason for the temp table?

 

[VNVfan]

My very first attempt at doing this was exactly that, I had everything being calculated in a query and the form bound to that but I found that also performed badly, though not quite as terrible as described in my first post.

Edit: I've just tried that again, just to be sure and yeah, the above is true. Another reason having the data in a temp table seems to be better is that when bound to a query, if any other window obscures the calendar form from view it seems to recalculate everything before it redraws the form. Unless that's because of some setting I have neglected to turn off?

 

[smig]

I suggest you to change your design from scratch.
I made a full week schedule that work much faster http://www.access-programmers.co.uk/forums/showthread.php?t=187707

agter you create your temp table fill in it all with 1, next fill all the Sick days with 1, next fill all the DaysOff with 2...
Doing so with an update query should be very fast.
Next you have your temp table filled with numbers. puting CF on this should be easy.

 

[VNVfan]

Hi smig, I downloaded the sample in the linked thread but I'm getting the same errors as the others who were trying to view it - but as far as I can tell from your description here this is pretty much what my workaround is doing; I.E. calculating the numbers first and dumping them into a table, which has indeed allowed CF to format the form almost instantly.

 

[smig]

I'm not sure if you need to calculate anything at all. don't you have a tables of SickDays, OffDays..... ?

 

[VNVfan]

Yeah the holiday and sickness data are stored in their separate tables, but they're stored in a "date from," "date to" format rather than listing every day separately - so when I say calculating, I just mean figuring out if a specific day is between any of the date ranges stored in either of those tables against a specific staff member's ID.

Here's my workaround version that I've been playing with, I had to change the function a bit as I realised that it wouldn't support if clicking a "next month" button was going to move it into the next year - my requirements are a view of a team's data for a month.