Re: conditional formatting in form slows down calculations
- From: "Susan" <Susan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 29 Apr 2005 11:44:07 -0700
Allen,
Thanks for the further input. I will give your suggestions a try & see
which works best. I really appreciate your help!
Sisam
"Allen Browne" wrote:
> 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.
> 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:FF256000-A868-4709-BC60-68DD264AA3AB@xxxxxxxxxxxxxxxx
> > 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 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.
> >> 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
>
>
>
.
- References:
- conditional formatting in form slows down calculations
- From: Susan
- Re: conditional formatting in form slows down calculations
- From: Allen Browne
- Re: conditional formatting in form slows down calculations
- From: Susan
- Re: conditional formatting in form slows down calculations
- From: Allen Browne
- conditional formatting in form slows down calculations
- Prev by Date: Re: Count Code Help Needed, Please
- Next by Date: RE: test - Not a Response
- Previous by thread: Re: conditional formatting in form slows down calculations
- Next by thread: Delete message
- Index(es):
Relevant Pages
|
