Conditional format takes too long

[cpampas]

Hello,
I wonder what I could do to speed up this code :

DoCmd.OpenQuery "qryMatchPosibleSCNB_P"
[frmSContNBanco_P].Form.Requery
'pause the code for 1 second

DoCmd.OpenQuery "qryMatchPosibleSBNC_P"
[frmSBancoNCont_P].Form.Requery
'pause the code for 1 second

After running each query, I have to requery the subforms. the reason is that the queries change one text field, and after that the conditional Format changes the color of some textboxes.
Some times it seems that the conditional formatting takes to o long to apply, so i have to pause the code running during 1 or 2 seconds after requerying


after the 2 first queries I have two more queries that change colors of controls, and for the same reason I have to requery the forms again

DoCmd.OpenQuery "qry7000topunt_P1"
[frmSBancoNCont_P].Form.Requery
'pause the code for 1 second

DoCmd.OpenQuery "qry7000topunt_P2"
[frmSContNBanco_P].Form.Requery
'pause the code for 1 second

Is there a ways to speed the process ?

 

[Cotswold]

Have you not tried just doing one .ReQuery after running all of your Update Queries?

 

[cpampas]

One Requery only wont work form me, because in between requeries the user edits data based in the control colors.

 

[CJ_London]

Depends on what the fields are, what the conditional format rules are and makes your queries more complicated, but you could consider using richtext.

 

[cpampas]

the conditional format rule is : [bcor]=7000, wich is a text field, if true it sets the color of another text box in the table to a yellow color
bcor is bound to an indexed field
How would rich text increase speed ?
i suspect thaat this is so slow because I have a continous form, and the format has to render individualy each row

 

[CJ_London]

i suspect thaat this is so slow because I have a continous form, and the format has to render individualy each row

yes

How would rich text increase speed ?

because the formatting is done within the query. Whether it would work for you, I don't know since it is not at all clear to me what you are trying to do.

Have you considered just filtering the records where bcor=7000

 

[The_Doc_Man]

I believe the REAL question hasn't been asked yet. What is your DB configuration? As in, SPLIT? If so, how is the back-end connected? And in that continuous form, how many records are implied?

 

[MajP]

Conditional formatting can be tricky in causing performance issue. It is not just the requery, but every time the form has to repaint. This happens a lot. Almost every time you move or scroll.

As suggested there may be some ways to redesign your UI to present this same information in another way that is equally useable. The biggest key is to reduce the size of the return records requiring conditional formatting. If you are return 20k records, you need to provide a method to provide say 100. Kind of similar approach to working with a SQL server back end. You never want to bind to an entire table.

 

[cpampas]

the database is not splitted, would that increase performance ?

I am doing an accounting bank reconciliation of data, so the records where bcor=7000 have a debt sum equal to a credit value in another table
Typically i would have between 60 to 150 records in this table, and one record in the other one.
i want to have the background color to be changed., as a suggestion to the user to check or not to check those records as a specific match

so my query finds matches of values between the 2 tables, and after that prompts the to accept the suggestion or not.
in the image attached the highlighted values have the bcor=7000, and they match one other value in the other subform.
this particular case does not take long, but if the match was 6 to 1, or 7 to 1, it takes longer to render the colors

 

[MajP]

image attached the highlighted values have the bcor=7000

Can you reattach the screen shot? Can you take a screen shot of the Format Condition rule/s?

 

[The_Doc_Man]

Is this a database shared across a network or is it strictly local to your desktop?

How many records are in the largest table that participates in this form that gives you trouble?

(I'm trying to figure out what is taking so long. So far, no indicators.)

 

[cpampas]

Attached rules of conditional formatting, and result with delay and without delay

this is the syntax of the query qryMatchPosibleSCNB_P , the other one is similar:

UPDATE tblSBNC_P SET tblSBNC_P.cor = 7000
WHERE (((tblSBNC_P.valor) In (SELECT qryMatchPosible.valor
FROM qryMatchPosible
)) AND ((tblSBNC_P.Punt)=False));


button click in my form :

DoCmd.OpenQuery "qryMatchPosibleSCNB_P"
[frmSContNBanco_P].Form.Requery
DoEvents
DoCmd.OpenQuery "qryMatchPosibleSBNC_P"
[frmSBancoNCont_P].Form.Requery
DoEvents


Call esperaSec(1)   'this a delay of 1 second

if i remove the one second delay (last line of code), i get the result as in attached pic, with only two records matching, 276,75 to 276,75
if i keep the one second delay , the conditional formating returns all the updated fields where bcor=7000, with four matching records 276,75 (2 times) to 276,75 (2 times), wich is the correct one

i wouldnt mind 1 second delay, but throughout my code i have multiple requerys and delays , and in this case only matching 4 records, if there were more, it would lag even more

 

[Pat Hartman]

There is no reason for using a subselect in this query. Just use an inner join instead. Access does not optimize subselects well so you should avoid them unless there is no alternative. If a simple join works, use it instead of the more complex subselect. Your query will also display better in the QBE since the QBE can't show subselects neatly either. Also, as has already been suggested, you need to severely limit the number of records displayed on a form at any one time so the query should include a where clause to do that.

You posted an update query but your form can't be bound to that. The form must be bound to a select query so you should probably post that one instead.