Re: conditional formatting in form slows down calculations

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Susan

CF (Conditional Formatting) is a weird beast, and it can cause the strange
problems you describe.

Firstly, it would be good to eliminate other possible contributing factors.
Particularly, uncheck the Name AutoCorrect boxes under:
Tools | Options | General
and then compact the database to get completely rid of this stuff:
Tools | Database Utilities | Compact
More info on the Name AutoCorrect problems:
http://allenbrowne.com/bug-03.html

Secondly, turn off Subdata*** names for the tables invovled in the main
form and subform that give the problem, by opening each table in design
view, and setting this property to [None] in the Properties box in table
design view. Given that a similar form works fine, it is possible that the
NameAutoCorrect or Subdatasheets are a contributing factor.

Thirdly, make sure there is an index on the ShipDate field. Since it appears
that you always apply this filter, you might consider creating a query to
use as the RecordSource of the form instead of the filter.

Now that all those factors are out of the picture, try removing CF from any
*calculated* controls on the form. They tend to be the ones that give this
kind of problem. If that helps, try creating the calculated field in the
source query, rather than use an expression in the Control Source of the
text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Susan" <Susan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6DB3A0DB-86BE-42F3-B1EF-FB12D802AFBA@xxxxxxxxxxxxxxxx
> My application (in Access 2003) is a simple schedule, main form data
> source
> is a table with one column ShipDate. Subform (Continous)showing one row
> for
> each print job on that day.
>
> I have an option group in the main form footer which allows the user to
> apply a filter so he can see all dates or only recent dates "shipdate >
> date() - 7". The form defaults to filter on when it is opened:
>
> Private Sub Form_Load()
> DoCmd.ApplyFilter , "shipdate > date() - 7"
> End Sub
>
>
> I want to assign conditional formatting to 2 fields on the subform.
> (ArtInProdDate and RemProdtime) ArtInProdDate is a simple date field.
> RemProdTime is a calculated field (calculation is done in the data source
> query.)
>
> When I apply the conditional formatting to either one of these fields, the
> form slows down, (says 'Calculating.' in lower left). As I scroll through
> the days in the main form, it can take up to 5 minutes to calculate on
> each
> day.
> If I remove the conditional formatting, the calculation takes about 1-2
> seconds.
>
> Even more strange, I have another form which is virtually identical. Its
> data source (subform) is a different query based on a different table but
> they are for the most part identical, just a few different fields (and of
> course different data). The two fields with the conditional formatting
> are
> the same in every respect. This form works perfectly with the conditional
> formatting applied.
>
> Any ideas what is going on here? What would make it slow down like this?
> I'm not a complete newbie but mostly self-taught with lots of holes in my
> knowledge so please be kind.
>
> Thanks in advance,
> Susan


.

Quantcast