Complex Conditional Formatting (1 Viewer)

[RossWindows]

I am currently trying to apply conditional formatting to a continuous form that typically has thousands of records. Unfortunately it is a lot more complicated than you may think and requires a little background before I can address my real problem. I'll do my best!

My idea is very similar to the way Outlook shows emails as 'Read' and 'Unread'... and the way an email is automatically marked as 'Read' when the user opens the email. So, think of an Incident like an email.

My idea basically consists of three tables.
tblIncidents, tblUserRev and tblIncidentRev

The later two tables hold revision information.
The idea is, when a user is viewing incidents and their User Revision Sum of a particular incident is less than the Incident Revision Sum, the user is said to be 'out of date' and thus that record appears highlighted.
Conversely, if the User Revision Sum is equal to the Incident Revision Sum, the user is up to date and the incident does not appear highlighted.

In both revision tables, the Revision Sum is a combination of the Notes Revision Number, Status Revision Number and Data Revision Number. Having the Revision Sum broken into these three numbers not only allows us to see if the user up to date or not, but it also allows the user to see 'at a glance' exactly what aspect of the incident has been updated since they last viewed.

There is one more thing in each of the revision tables; Latest Revision Date. This allows the user to how much time has passed since they last visited an Incident that has since been updated.

Still with me?

Ok, now here's my real problem. With such intensive calculations being carried out for each and every incident on the continuous form, it seems to be going sllllllooooowwww once all the incidents are loaded into the databae. I think the total count is 20,684

So, I need some expert advise. I think I need to revamp the process... make it more streamlined... more efficient but I am at a creative loss.

 

[pbaldy]

Since I don't fully understand your situation, let's accept for the moment that the overall formatting process is sound. I would question presenting 20k records in a form. It would be highly impractical for the user to scroll through them. I'd look for some way of filtering the data being presented. Again, I don't know your situation, but perhaps the incidents can be restricted to a date range, employee, etc. Is that feasible?

 

[RossWindows]

Yes, sorry I forgot to mention. tblIncidents has 20k records, but right now I am only applying this functionality to a form that limits the records to incidents that the user created themselves and incidents that they currently own. I don't think any user would have more than 500 records in this view. It's still running quite slowly even in this view though.

 

[pbaldy]

Okay, then something you might try is to format one textbox instead of the 11 you are now. Put one big textbox behind all the others, set the back style on the others to transparent, and just format the big textbox.

 

[RossWindows]

AH, thats genius. I'll give it a shot.

 

[RossWindows]

Okay, here's a revised example with the changes above. It'll take some time to apply these changes to the full FE. (The example is just a VERY stripped down version). I'll let you know how it goes.

 

[RossWindows]

Just wanted to follow up for anyone that comes across this.
The latest version is working fine for a variety of PC and terminal server users here.
I think some of my issues regarding speed were due to my system pulling data across the network. Since 99% of the users are terminal server users, (and the file share resides near the term server) speed is not an issue for them. (Our shared DB system is pretty unique in that it is split into a FE and BE, but each user's FE, including PC users, is still on the network drive to fascilitate faster access for all the terminal server users and we only have to manage one deployment/update method.)