Control requery vs recalc

[Guest]

My reading of Access Help on Requery has me confused. Help seems to say that
control.Requery should work in my application but I've found form.Recalc is
the only solution.

I have a simple continuous form where the user can update a Salary field.

In the form header is a calculated control, a textbox

Text6 ControlSource =Format(DSum("Salary","Employees"),"$0.00")

which will show the user the current total of the salaries on file. Text6
is re-updated, via the Salary field AfterUpdate event, each time the user
enters a new salary to the form .

Private Sub Salary_AfterUpdate()
' update the sum
If Me.Dirty Then
Me.Dirty = False
End If
Me![Text6].Requery
End Sub

There are a few other calculated controls on the form (total employees etc)
which are set at the form's OnOpen event and don't change with the user's
salary input. So, I thought it would be a bit more efficient to requery
only the control that needs update, but it doesn't work (when the form opens
Text6 correctly shows the total at form open time but after each salary entry
it remains unchanged). In place of Me.Text6.Requery I'm using Me.Recalc
which works fine.

Help info on Requery says:
"Reruns the query on which the form or control is based.

Controls based on a query or table include:

Controls for which the ControlSource property setting includes domain
aggregate functions or SQL aggregate function.

If you specify any other type of control for the object argument, the record
source for the form is requeried.
If the control specified by the object argument isn't bound to a field in a
table or query, the Requery method forces a recalculation of the control."

- - - -

Since Text6 is based on domain aggregate funtion it seems it should update
with Requery.
While I'm happy with the Recalc approach I am puzzled why the Requery
doesn't work.

Any thoughts appreciated.

 

[Allen Browne]

When you Requery a form, it goes back to the table and fetches the data
again. You might need to do that if records have been deleted or added or
modified by another user or process (such as an action query.) After the
Requery, your form will be at the first record feteched from the table, just
as if you had loaded the form again.

When you Requery a combo or list box, it fetches the data from the RowSource
table again. You might need to do that if another user or process has
added/deleted/modified records in the RowSource, and you want the combo to
have the new choices available.

When you Recalc a form, any calculated controls (those that have a Control
Source that starts with =) are calculated again. Normally, Access treats
calculated controls as a low priority task, and does other things first
(such as selecting records or running event procedures). So, you might
Recalc if you need to force a calculation to be completed so the result is
available to your code. In practice, it is generally safer and easier to
work out the calcuation in your code (e.g. perform the DSum() or DLookup())
than it is to rely on the timing of the Recalc.

In your example, Text6 has a calculated expression in its ControlSource, so
it makes sense to force the recalculation of the calculated controls on the
form if you need to force the update.

(You cannot merely Recalc one control, because a form may have several
calculated controls, some dependent on others.)

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

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

My reading of Access Help on Requery has me confused. Help seems to say
that
control.Requery should work in my application but I've found form.Recalc
is
the only solution.

I have a simple continuous form where the user can update a Salary field.

In the form header is a calculated control, a textbox

Text6 ControlSource =Format(DSum("Salary","Employees"),"$0.00")

which will show the user the current total of the salaries on file. Text6
is re-updated, via the Salary field AfterUpdate event, each time the user
enters a new salary to the form .

Private Sub Salary_AfterUpdate()
' update the sum
If Me.Dirty Then
Me.Dirty = False
End If
Me![Text6].Requery
End Sub

There are a few other calculated controls on the form (total employees
etc)
which are set at the form's OnOpen event and don't change with the user's
salary input. So, I thought it would be a bit more efficient to requery
only the control that needs update, but it doesn't work (when the form
opens
Text6 correctly shows the total at form open time but after each salary
entry
it remains unchanged). In place of Me.Text6.Requery I'm using
Me.Recalc
which works fine.

Help info on Requery says:
"Reruns the query on which the form or control is based.

Controls based on a query or table include:

Controls for which the ControlSource property setting includes domain
aggregate functions or SQL aggregate function.

If you specify any other type of control for the object argument, the
record
source for the form is requeried.
If the control specified by the object argument isn't bound to a field in
a
table or query, the Requery method forces a recalculation of the control."

- - - -

Since Text6 is based on domain aggregate funtion it seems it should update
with Requery.
While I'm happy with the Recalc approach I am puzzled why the Requery
doesn't work.

Any thoughts appreciated.

 

[sebt]

Hi Frank

My guess is that, while your DSUm is based on the Salary _field_ in the
Employees table, the recalculation you're performing is only in the
Salary _control_'s After Update - at this point the Salary control will
have a new value, but this (and any other changes on the form) will not
have been saved to the table. Data only gets saved to the underlying
table between the Form's BeforeUpdate and After Update events - i.e.
when you move to another record, or explicitly save the record.

So, Requery on the text box wouldn't have any effect, because it would
just re-run the DSum function, on a table which doesn't yet contain the
new salary value just entered (Requery on the form should work, as this
would automatically save the new value before the requery). But then
why should Recalc work?

I've tried this out on a test form: a Recalc on the form in fact saves
the currently edited record (if any) and then does the Recalc. If you
move your code to the Form_AfterUpdate event, the TextBox.Requery
should work.

cheers


Seb

 

[Guest]

Hi Allen - thanks for the clarification on Requery and Recalc and the typical
uses - very informative, especially the information on Access priorities.

Since you (and Access Help) implied that contorl.Requery should work for me
I went back to basics and came up with a quite amazing finding (to me). I
hadn't mentioned in my example, that none of the fields on the form except
Salary are enabled. This was done so that the user could tab quickly through
the Salary fields of the (continuous) form and update as needed.

If I enable at least one other field on the form then Text6.Requery works
fine If Salary is the only enabled field then Text6.Requery is inert.

Actually the behaviour of the form is most peculiar. After updating
successive Salary fields, with no corresponding change in the Text6 control,
if I click in one of the updated Salary fields, Text6 will update and show a
new total taking into account the impact of the changed field only. As each
updated Salary filed is clicked its impact on the total is reflected in Text6
while the impact of the prevoulsy clicked field reverts. Then if I click
outside the Salary field, Text6 reverts to its value at formOpen.

I built a quick and dirty test database to verify this and it holds. Very
weird.

When you Requery a form, it goes back to the table and fetches the data
again. You might need to do that if records have been deleted or added or
modified by another user or process (such as an action query.) After the
Requery, your form will be at the first record feteched from the table, just
as if you had loaded the form again.

When you Requery a combo or list box, it fetches the data from the RowSource
table again. You might need to do that if another user or process has
added/deleted/modified records in the RowSource, and you want the combo to
have the new choices available.

When you Recalc a form, any calculated controls (those that have a Control
Source that starts with =) are calculated again. Normally, Access treats
calculated controls as a low priority task, and does other things first
(such as selecting records or running event procedures). So, you might
Recalc if you need to force a calculation to be completed so the result is
available to your code. In practice, it is generally safer and easier to
work out the calcuation in your code (e.g. perform the DSum() or DLookup())
than it is to rely on the timing of the Recalc.

In your example, Text6 has a calculated expression in its ControlSource, so
it makes sense to force the recalculation of the calculated controls on the
form if you need to force the update.

(You cannot merely Recalc one control, because a form may have several
calculated controls, some dependent on others.)

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

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

My reading of Access Help on Requery has me confused. Help seems to say
that
control.Requery should work in my application but I've found form.Recalc
is
the only solution.

I have a simple continuous form where the user can update a Salary field.

In the form header is a calculated control, a textbox

Text6 ControlSource =Format(DSum("Salary","Employees"),"$0.00")

which will show the user the current total of the salaries on file. Text6
is re-updated, via the Salary field AfterUpdate event, each time the user
enters a new salary to the form .

Private Sub Salary_AfterUpdate()
' update the sum
If Me.Dirty Then
Me.Dirty = False
End If
Me![Text6].Requery
End Sub

There are a few other calculated controls on the form (total employees
etc)
which are set at the form's OnOpen event and don't change with the user's
salary input. So, I thought it would be a bit more efficient to requery
only the control that needs update, but it doesn't work (when the form
opens
Text6 correctly shows the total at form open time but after each salary
entry
it remains unchanged). In place of Me.Text6.Requery I'm using
Me.Recalc
which works fine.

Help info on Requery says:
"Reruns the query on which the form or control is based.

Controls based on a query or table include:

Controls for which the ControlSource property setting includes domain
aggregate functions or SQL aggregate function.

If you specify any other type of control for the object argument, the
record
source for the form is requeried.
If the control specified by the object argument isn't bound to a field in
a
table or query, the Requery method forces a recalculation of the control."

- - - -

Since Text6 is based on domain aggregate funtion it seems it should update
with Requery.
While I'm happy with the Recalc approach I am puzzled why the Requery
doesn't work.

Any thoughts appreciated.

 

[Guest]

Hi Seb - yes, if the data hasn't been saved to the underlying table then the
DSum can't pick up the new value. But my control.Requery problem persists
through multiple updates - I would expect at worst to lag by one record
update if the save isn't forced. The Me.Dirty code in the AfterUpdate event
should take care of this . . . in fact I can open the table and see the
values updating as I update the form.

Actually it now seem the contol.Requery isn't working because on my form the
only enabled field is the Salary field. Once I enable at least another field
control.Requery works fine, as it should. Why Access has a problem in the
case where only one field is enabled is a mystery to me . . . I posted a bit
more in a reply to Allen Browne elsewhere in this thread. Another Access
mystery.

Thanks

 

[Allen Browne]

If Requery does work, it is only as a side effect.

My long-winded reply was trying to explain that Recalc is the appropriate,
logical choice to get Access to force the update of a calculated control.

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

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

Hi Allen - thanks for the clarification on Requery and Recalc and the
typical
uses - very informative, especially the information on Access priorities.

Since you (and Access Help) implied that contorl.Requery should work for
me
I went back to basics and came up with a quite amazing finding (to me). I
hadn't mentioned in my example, that none of the fields on the form except
Salary are enabled. This was done so that the user could tab quickly
through
the Salary fields of the (continuous) form and update as needed.

If I enable at least one other field on the form then Text6.Requery works
fine If Salary is the only enabled field then Text6.Requery is inert.

Actually the behaviour of the form is most peculiar. After updating
successive Salary fields, with no corresponding change in the Text6
control,
if I click in one of the updated Salary fields, Text6 will update and show
a
new total taking into account the impact of the changed field only. As
each
updated Salary filed is clicked its impact on the total is reflected in
Text6
while the impact of the prevoulsy clicked field reverts. Then if I click
outside the Salary field, Text6 reverts to its value at formOpen.

I built a quick and dirty test database to verify this and it holds. Very
weird.

When you Requery a form, it goes back to the table and fetches the data
again. You might need to do that if records have been deleted or added or
modified by another user or process (such as an action query.) After the
Requery, your form will be at the first record feteched from the table,
just
as if you had loaded the form again.

When you Requery a combo or list box, it fetches the data from the
RowSource
table again. You might need to do that if another user or process has
added/deleted/modified records in the RowSource, and you want the combo
to
have the new choices available.

When you Recalc a form, any calculated controls (those that have a
Control
Source that starts with =) are calculated again. Normally, Access treats
calculated controls as a low priority task, and does other things first
(such as selecting records or running event procedures). So, you might
Recalc if you need to force a calculation to be completed so the result
is
available to your code. In practice, it is generally safer and easier to
work out the calcuation in your code (e.g. perform the DSum() or
DLookup())
than it is to rely on the timing of the Recalc.

In your example, Text6 has a calculated expression in its ControlSource,
so
it makes sense to force the recalculation of the calculated controls on
the
form if you need to force the update.

(You cannot merely Recalc one control, because a form may have several
calculated controls, some dependent on others.)

My reading of Access Help on Requery has me confused. Help seems to
say
that
control.Requery should work in my application but I've found
form.Recalc
is
the only solution.

I have a simple continuous form where the user can update a Salary
field.

In the form header is a calculated control, a textbox

Text6 ControlSource =Format(DSum("Salary","Employees"),"$0.00")

which will show the user the current total of the salaries on file.
Text6
is re-updated, via the Salary field AfterUpdate event, each time the
user
enters a new salary to the form .

Private Sub Salary_AfterUpdate()
' update the sum
If Me.Dirty Then
Me.Dirty = False
End If
Me![Text6].Requery
End Sub

There are a few other calculated controls on the form (total employees
etc)
which are set at the form's OnOpen event and don't change with the
user's
salary input. So, I thought it would be a bit more efficient to
requery
only the control that needs update, but it doesn't work (when the form
opens
Text6 correctly shows the total at form open time but after each salary
entry
it remains unchanged). In place of Me.Text6.Requery I'm using
Me.Recalc
which works fine.

Help info on Requery says:
"Reruns the query on which the form or control is based.

Controls based on a query or table include:

Controls for which the ControlSource property setting includes domain
aggregate functions or SQL aggregate function.

If you specify any other type of control for the object argument, the
record
source for the form is requeried.
If the control specified by the object argument isn't bound to a field
in
a
table or query, the Requery method forces a recalculation of the
control."

- - - -

Since Text6 is based on domain aggregate funtion it seems it should
update
with Requery.
While I'm happy with the Recalc approach I am puzzled why the Requery
doesn't work.

 

[sebt]

Hi Frank

definitely a mystery. I noticed some weirdness on my test form as
well, of exactly the kind you saw: the total text box would change its
value when I just clicked in the detail control it's related to - I
also got a "feeling" which I couldn't quite make sense of (or pin down
precisely) that the total box was updating a few rows "behind" my
input. My form had more than one control enabled.

as you say, yet another one of those spooky Access things.



Seb