Slow Conditional Formatting (Datasheet) (1 Viewer)

[Steve R.]

We recently "upgraded" from ACCESS2000 to ACCESS2007. The good news is that I have not had any real problems. The bad news is having to do some tweaks. Currently, I have a form that uses a subform in datasheet view. The subform uses conditional formatting to highlight incomplete projects. It is based on a filter.

Under ACCESS2000 the subform was slow to display. I have some dlookup statements. Under ACCESS2007 the subform basically "freezes". The actual symptom is that one of the fields flickers. After you move the mouse cursor (doesn't really matter where) the flickering stops and the data displays itself. (Could this imply that the program for whatever reason is trying to communicate with the mouse?).

When I open the subform directly, I don't have the slow/flickering problem either.

I did one test where I removed the conditional formatting, and that resolved the problem of the subform datasheet "freezing".

So far, I have "rebuilt" the forms entirely within ACCSESS 2007, but that has not solve the problem. I am currently experimenting with "repaint" and "requery". So far that has not yet worked. I may be pursuing a "wrong" course of action here.

I am also contemplating doing a stand alone query and referencing the subform to get the data from the query to see if that solves the problem. Which actually leads me to a side question that raises a "bigger" programing issue. Is it better (faster/efficient) for a subform to get its data from a stand alone query or by using a filter? Currently, the subform is based on a filter.

The ultimate "solution" of course would be to remove the conditional formatting if there is no other solution. Any additional advice?

 

[evanscamman]

The dLookup function is notoriously slow when used this way.
Instead, try to achieve the same results by adding fields/criteria or even a sub-query to your subform's underlying criteria.

Then in your conditional formatting your expression could be something like: [NewField] = True --- rather than the dlookup statement.
I think you will find this to be much faster.

 

[Steve R.]

Thanks very much. Now that my database has "grown" in size, I ran some tests and confirmed that data retrieval is faster with a query.

 

[Galaxiom]

A wide record source with a filter returns all the records and shows a subset.
A query designed for limited results will always be faster on the first load.

Use the Master and Child Link fields to control the subform records. This seems the simplest and fastest way to me. Be sure to index the link fields in the source table.

 

[Steve R.]

When I initially created my form, there were less than 1,000 records so it was "difficult" to tell which approach was faster. We also moved the data to Microsoft's SQL Server. Now we are getting ready to go over 5,000 records on a networked environment. The more efficient solution, using SQL, demonstrates a definite speed advantage. Thanks.