conditional formatting in form slows down calculations

[Susan]

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

 

[Allen Browne]

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

 

[Susan]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

Hi Susan

No, I can't solve this problem completely. The problem is worse in some
versions than others, and we have it documented in this link:
http://allenbrowne.com/bug-05.html
IIRC, SP1 for Access 2003 may have helped reduce some of the issues.

The only workaround I am aware of to avoid CF in a continuous
form/datasheet, is to add a control that has a true/false expression as its
RecordSource, e.g.:
=([shipdate] > (Date() -7))
You can then manipulate the Format property of this control so that it
displays a symbol in a color for True, and no symbol for False, based on the
idea that True is -1 (so use the format for negative) and False is 0 (so use
the positive format). Then the symbol appears only on the rows you need to
flag, or you can have a different colored symbol on the flagged rows. This
is an old trick that works in Access 97 and earlier, before CF was
introduced.

The other option you raise is to load all the records, but have the form
scroll down so that today's record appears at the top of the list:

Private Sub Form_Load()
Dim strWhere As String
strWhere = "[shipdate] >= " & Format(Date, "\#mm\/dd\/yyyy\#")
With Me.RecordsetClone
.FindFirst strWhere
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Susan]

Allen,

Thanks for the further input. I will give your suggestions a try & see
which works best. I really appreciate your help!

Sisam

Hi Susan

No, I can't solve this problem completely. The problem is worse in some
versions than others, and we have it documented in this link:
http://allenbrowne.com/bug-05.html
IIRC, SP1 for Access 2003 may have helped reduce some of the issues.

The only workaround I am aware of to avoid CF in a continuous
form/datasheet, is to add a control that has a true/false expression as its
RecordSource, e.g.:
=([shipdate] > (Date() -7))
You can then manipulate the Format property of this control so that it
displays a symbol in a color for True, and no symbol for False, based on the
idea that True is -1 (so use the format for negative) and False is 0 (so use
the positive format). Then the symbol appears only on the rows you need to
flag, or you can have a different colored symbol on the flagged rows. This
is an old trick that works in Access 97 and earlier, before CF was
introduced.

The other option you raise is to load all the records, but have the form
scroll down so that today's record appears at the top of the list:

Private Sub Form_Load()
Dim strWhere As String
strWhere = "[shipdate] >= " & Format(Date, "\#mm\/dd\/yyyy\#")
With Me.RecordsetClone
.FindFirst strWhere
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Kernow Girl]

This may help me also with my problem of a Form opening VERY slowly - I use
lots of conditional formatting - mainly to alter the colour of the field when
the user is entering data. Thanks