Calculated Control - Refreshes Too Slow For VBA Program

[Guest]

I have a main form (one side) and a sub form (many side) which are linked and
operating correctly. Both forms contain amount fields. The sub form also
contains a category field. The total of the sub form amount fields must
equal the amount field of the main form for each main form record. I am
determining whether they equal by a calculated control (Difference) on the
main form. The sub form data needs to be manually updated by a user since
the required information varies and is not programmable (they break the
amount up by different categories, both of which vary).

When the user clicks save and close I have validation that checks to see if
1) all sub form records have a category and 2) the total of the sub form
amount fields equal the amount field of the main form for each main form
record.

My issue is that when the VBA program runs and the main form loops through
records to validate the category fields and amount/differences on the sub
form (from record 1 to record 2 etc) that the calculated control (Difference)
that indicates whether there is a difference or not has not refreshed yet.
Therefore, the program thinks there is a difference (validation error) on
every single record from the 2nd record on (on main form). I do have the
main form refreshing on the OnCurrent event (Me.Refresh). However, the VBA
program runs too fast for it. If I step through the VBA program in break
mode everything works great so I know my logic is correct (I can’t hit the
“step into†button as fast as VBA). It also works great on the user side
when entering data. As soon as they move from one record to the next or
update the amount the difference field updates properly and quickly.

Does anybody know any way around this.

Any help is greatly appreciated.

Thanks,
Bill Horton

 

[RuralGuy]

Hi William,

Create a sub routine in the SubForm that will update whatever controls
you need on the MainForm (Parent). I call my routine UpdateParent. Call
this sub on the current event of the SubForm and the AfterUpdate event of
any SubForm control involved in the calculations. You are now in charge
of the timing.

HTH

 

[Dirk Goldgar]

I have a main form (one side) and a sub form (many side) which are
linked and operating correctly. Both forms contain amount fields.
The sub form also contains a category field. The total of the sub
form amount fields must equal the amount field of the main form for
each main form record. I am determining whether they equal by a
calculated control (Difference) on the main form. The sub form data
needs to be manually updated by a user since the required information
varies and is not programmable (they break the amount up by different
categories, both of which vary).

When the user clicks save and close I have validation that checks to
see if 1) all sub form records have a category and 2) the total of
the sub form amount fields equal the amount field of the main form
for each main form record.

My issue is that when the VBA program runs and the main form loops
through records to validate the category fields and
amount/differences on the sub form (from record 1 to record 2 etc)
that the calculated control (Difference) that indicates whether there
is a difference or not has not refreshed yet. Therefore, the program
thinks there is a difference (validation error) on every single
record from the 2nd record on (on main form). I do have the main
form refreshing on the OnCurrent event (Me.Refresh). However, the
VBA program runs too fast for it. If I step through the VBA program
in break mode everything works great so I know my logic is correct (I
can't hit the "step into" button as fast as VBA). It also works
great on the user side when entering data. As soon as they move from
one record to the next or update the amount the difference field
updates properly and quickly.

Does anybody know any way around this.

Any help is greatly appreciated.

Thanks,
Bill Horton

One possible alterative would be not to use the calculated control at
all for your validation. Instead, use a DSum expression to get the
total of the related subform records directly from the table where they
are stored, and compare that to the amount on the main form.

 

[Guest]

Thanks for the tip. Is the code you use to update your Parent form/controls
Forms!ParentFormName.Recalc or Forms!ParentFormName.Refresh? Also, how is
the response time on your Parent form after you apply this coding. When I
applied the coding it takes quite some time for my parent form to open and
quite some time for it to refresh when I move from record to record. The
dataset that I am working with is not very large at all.

 

[RuralGuy]

Thanks for the tip. Is the code you use to update your Parent
form/controls Forms!ParentFormName.Recalc or
Forms!ParentFormName.Refresh? Also, how is the response time on your
Parent form after you apply this coding. When I applied the coding it
takes quite some time for my parent form to open and quite some time
for it to refresh when I move from record to record. The dataset that
I am working with is not very large at all.

I actually do most of the work in the sub routine. ie:

Open a RecordsetClone and walk through it summing
whatever I need. Then update the parent.

Me.Parent.txtTotal = MyCalculatedTotal

The forms are almost instantaneous. Very snappy performance!

HTH

 

[Guest]

Dirk, I think I am going to go with your suggestion. However, I can't seem
to get the Dsum function to work properly. I am using in the control source
property of a control on the parent form footer the following code:

=DSum("[Actual]","T_NeedsAllocation","[Cost
Element]=[T_NeedsAllocation]![Cost Element] And [Cost Center] =
[T_NeedsAllocation]![Cost Center]")

This code is giving me the sum of all records on the T_NeedsAllocation table
and not just the records that are linked by CostCenter and CostElment to the
current record on the parent form. Any ideas on where I've gone wrong?

 

[Dirk Goldgar]

Dirk, I think I am going to go with your suggestion. However, I
can't seem to get the Dsum function to work properly. I am using in
the control source property of a control on the parent form footer
the following code:

=DSum("[Actual]","T_NeedsAllocation","[Cost
Element]=[T_NeedsAllocation]![Cost Element] And [Cost Center] =
[T_NeedsAllocation]![Cost Center]")

This code is giving me the sum of all records on the
T_NeedsAllocation table and not just the records that are linked by
CostCenter and CostElment to the current record on the parent form.
Any ideas on where I've gone wrong?

Well, I was talking about forgoing the calculated control altogether,
and just using the DSum() expression in the code that does the
validation. But your expression is also wrong in that its criteria
argument doesn't distinguish between the [Cost Element] and [Cost
Center] controls on the form and the like-named fields in the table.
How about something like this in your code:

If DSum("Actual", "T_NeedsAllocation", _
"[Cost Element]=" & Me![Cost Element] & _
" AND [Cost Center]=" & Me![Cost Center]) _
<> Me!Total _
Then
' do something because they aren't equal ...
Else
' (maybe) do something because they are equal ...
End If

Note: The above expression assumes that both [Cost Element] and [Cost
Center] are numeric fields. If they are text fields, then quotes have
to be built into the criteria expression surrounding the values it's
picking up from the form. For example, the following might be used if
both fields are text:

If DSum("Actual", "T_NeedsAllocation", _
"[Cost Element]='" & Me![Cost Element] & _
"' AND [Cost Center]='" & Me![Cost Center] & "'") _
<> Me!Total _
Then

Of course, I've just guessed at the name of the control, Me!Total, to
compare the result with. You should use your own control name.