Re: Halt code execution until requery is finished.

Tech-Archive recommends: Fix windows errors by optimizing your registry

Al Camp wrote:
"Marshall Barton" wrote
Al Camp wrote:
"Marshall Barton" wrote
Al Camp wrote:

Is there some code, or technique that I can use that will force a
subform
requery to finish completely, before subsequent code runs?

Sequence of code (aircode)
First button click = increment a qty by +1
Make a decision about (qty +1)
Requery subform to reflect new qty

Second Button click = increment a qty by +1
**Make a decision about (qty + 1) (**Problem: the subform has not
finished
the requery,
so my decision coding returns erroneous values from the requeried
subform.)

Tried disabling/enabling the button, but it enables the button before
the
Requery is complete.


You can do a MoveLast on the subform's RecordsetClone to
force the subform's record source to completely load.
However, that may not force all controls on the subform to
complete their recalculation. So a total using an aggregate
function may still be out of sync.


Thanks for taking a shot at this problem. A real poser. I tried...
Forms!frmParts!frmPartsDetail.Form.RecordSetClone.MoveLast
after the Requery, but it still allows the button to overrun the requery.
I tried the requery before the "decision" code and after it, with the
same results. I think I'll try to find another event to hang that Requery
on... rather than have it in this pseudo loop.


What is this qty that you are incrementing? It looks like a
field in the subform's record source, but which record?

I don't know, I'm just guessing, but maybe there is some
detail in your real code that's important??


I posted this question on 2/7 on .forms, but got responses, so I tried again
here,
but, with much less detail.
Here's more details...
I have two subforms (single form Sub1 and continuous Sub2) on one main
form.
There is a Qty field on Sub1 that uses a Sum value (TotalOnHand) from the
footer of Sub2 to make a certain decision. (actually On Hand Qty)

Sub1 (a Disbursement form) is used in a production enviornment, so I've
set up
the form to be filled out without the need for keyboard entry. Since most
transactions
are small quantities, the form can be completely "mouse" entered 90% of the
time.

I have a [+] button that increments the Disbursement Qty field on Sub1 by
1 each time.
and each time I increment the Qty, I check the TotalOnHand from Sub2 to
determine if I've
met a certain criteria. (OnHand = MinOrderPoint or OnHand = 1 or OnHand
=0). After
this criteria checking, I requery Sub2 to reflect the newly incremented Sub1
Qty.

This works fine, but, if I re-click the [+] button too quickly, I can
outpace the Sub2 Requery, and receive erroneous messages pertaining to the
TotalOnHand "criteria" logic. (thinks TotalOnHand = 0)

Of course, normal manual data entry for Qty works fine...


That's what I was afraid of. Your code is trying to use a
calculated value. Unfortunately, the calculation task runs
at a lower priority than the VBA code execution task so your
code and user interactions can easily get ahead of control
calculations. I used to say that this is impossible to
synchronize, but Ken figured something out. I didn't fully
understand it's convoluted contortions, but he did say it
seemed to work in his situation.

My approach to situations like this has always been to make
sure the tables were up to date with whatever saves needed
to be done and then running a query to calculate the Sum.

--
Marsh
MVP [MS Access]
.

Quantcast