Continuous subform with conditional formatting (AC2007)

[AOB]

Hi guys,

I have a continuous subform with a fairly complex snapshot query as its record source. I have controls on the main form to allow criteria parameters for the query and a command button to requery the subform based on the selected criteria. I also have conditional formatting on a number of the continuous controls in the subform (the conditions are fairly straightforward, i.e. highlight if null, true/false etc)

When the subform is (re)queried, it seems to repaint several times (3 or 4 although it appears arbitrary to me) before it eventually settles. And it really louses up the aesthetics as controls flicker between colours; sometimes I even lose the background colour on parts of the detail section, or controls disappear altogether. And this continues as I scroll down the subform (or even click anywhere on it)

I don't mind that the query itself takes some time to run - of the order of a few seconds each time - but I'd prefer for the screen to wait until the query is completed, and all of the CF applied, before refreshing itself, instead of 'sweeping' top to bottom repeatedly. It's a snapshot query so once it's run, there should be no changes to the dataset.

I've tried Application.Echo but it has no effect whatsoever

Am I asking the impossible here or does anybody know a workaround for this problem?

Thanks

AOB

 

[burrina]

Are you requiring those controls individually? If it has to go thru and check each one, then yes you get the effect described.

 

[AOB]

I presume that was meant to be "requerying"?

If so, no, they are not requeried individually, each one is linked to a field in the recordset returned by the subforms query. So one main query returning multiple records across a dozen or so fields (continuous subform). The CF is applied individually though. Is that what you mean?

 

[burrina]

Yes, sorry for my Re query typo! I would try and incorporate them all into either a If or Case statement and see if that solves the problem.

HTH

 

[AOB]

Incorporate all of the CF into an If or Case statement? How is that possible? Each control has different CF conditions?

 

[burrina]

I'm not familiar with your db of course but if you simply put that CF into a vba If or Case statement MAYBE that would eliminate the issue.

Example:

If IsNull([ProductID]) Then
Me.ProductID.BackColor = colorTan_Encarnacion
Else
Me.ProductID.BackColor = vbWhite
End If

Hey, maybe it's not a viable solution for you. If not, I got nothing,sorry!

 

[AOB]

Thanks burrina

But I don't think you can apply such formatting via VBA. Doing it programmatically applies it to every instance of the recurring control (regardless of whether each individual value meets the criteria) Hence CF is handy because you can make an independent determination for each row of the continuous (sub)form.

That's what I need but the refresh / repaint flicker is dreadful (to the point of being confusing, which is worse than the aesthetic issue) so I'm hoping somebody else knows of a way to achieve the same result, even with a (reasonable) delay in the requery process?