Access 2007 Conditional Formating on Datasheet View takes several seconds to load

0
votes

This is my second question here, so I hope I ask the right way this time.

Background: I'm designing an Access Database to do some calculations. I got that part. My problem is the visual design. I hate forms. I'm an Excel man and I like to work on Raw data normally, so I suck designing forms. This questions is 100% to improve performance about a conditional formating rule.

I applied a simple conditiona rule in a form (style is datasheet View) where if the value of a Field is True, then it colours ALL the fields of the record. The rule works perfect. It does exactly what I want.

PROBLEM: When the list contains several records, it takes some time for Access to colour all rows according to my Conditional Formating rule. We are talking about 5 or even 10 seconds.

Some Images:

enter image description here

But with more records...

enter image description here

And I have to wait around 5-10 seconds untill all of them are colored, and If i scroll down to see more of the list, some of them are not colored and I have to wait again some seconds. enter image description here

QUESTION: ¿Is there any way I can delay the load until the Conditional Formating is applied to all records? ¿Or a way to do it faster?

WHAT I HAVE TRIED UNTIL KNOW:

I have tried playing with Application.Echo and also Repaint. I don't know anything else to try. Tried both optionsin different events of the form and nothing helped.

I do not post any code or data because is not really about bad coding or data manage. Is just if anybody knows something that can accelerate this procces or delay the load of this form until all Conditional Formating has been applied.

P.S: There are no macros running in background when user checks this form, so is not a problem of performance of computer or other macros interfering. Also, this database must be done in Access 2007, no other option is available for final user.

Thanks for anyone that can provide a solution. I hope this time I asked the right way.

ANSWERING COMMENTS: Nope, all data is a query from tables inside same database. No custom functions in the query. Not a single calculation, it just takes data stored in tables. Is't located in local drive. We are talking about 635 records right now, but it will have more records in the future. And yes, I have activated "Compact & Repair". Database size is actually around 5 Megabytes.

1
ms-accessms-access-2007
Are there references to external files or data sources? Custom functions? Is this stored on the local C: or on a network? How many records are we talking about? Do you "Compact & Repair" and reboot regularly? – ashleedawg
Nope, all data is a query from tables inside same database. No custom functions in the query. Not a single calculation, it just takes data stored in tables. Is't located in local drive. We are talking about 635 records right now, but it will have more records in the future. And yes, I have activated "Compact & Repair". Database size is actually around 5 Megabytes. – Foxfire And Burns And Burns
how many fields? 5mb seems like a lot for only 600 records – ashleedawg
Because i got a lot of tables and queries, but this form just 1 query, and 13 fields. – Foxfire And Burns And Burns
Except that FC can be dead-slow, perhaps your expression used is "less than optimal"? – Gustav

1 Answers

2
votes

You can force Access to process all records:

Do Until Me.Recordset.EOF
   Me.Recordset.MoveNext
Loop
Me.Recordset.MoveFirst

A large difference is that normally, conditional formatting is applied without locking the application, but this will lock the application.