Re: conditional formatting in form slows down calculations

Allen,

Thank you very much for your quick & thorough response!!

I made changes as you suggested and it helped the situation but did not
correct it completely.

Of the two controls I wish to be conditionally formatted, neither is
calculated. One is a date field, the other is calculated in the underlying
query. The problem arises when either or both of these fields have
conditional formatting applied.

The filter was set by an option group with two choices, the user can choose
to see all orders or see recent orders. I originally accomplished this by
setting a filter (at the form level) on the field shipdate (turning it on and
off w/ the option buttons). Working from your suggestion, I changed the
option group so that it now changes the record source between the actual
table to see all dates and a filtered query to see recent dates.

Now, if I set the form to open with the recordsource the main table, it
works. If I set it to open with the record source the query, I get the
endless calculating when the form opens. Once it finished the calculating, I
can scroll through the days with no problem.

If I set it to open with the record source the main table and then use the
option button to switch the record source to the query, the form opens
normally and works normally even when looking at the query.

Any further ideas, either to fix it or as a workaround? I could have the
form open showing all rows if I could figure out how to get it to open on
today’s date…

Thanks again,
Susan


"Allen Browne" wrote:

> 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
>
>
>
.