getting data from form footer

  • Thread starter Thread starter Martin Cibulka
  • Start date Start date
M

Martin Cibulka

Greetings,

I've spend a couple hours banging my head against the wall and can't
figure
out why this isn't working.
I have a subform with a list and a form footer that has a text field
(txtSumAmount) showing the sum of one of the fields in the list
("=Sum([DepositLineAmount])"). In the Form_Current of the main form I
set
the RecordSource of the subform accordingly, and then I want to use
the new
value of txtSumAmount in some other calculations.
So I added the following function to my subform:
Public Sub GetTotal(dblPayments As Double)
dblPayments = Nz(Me.txtTotalPayments, 0)
End Sub

However - in Access 2000 this always returns 0, even if I explicitly
do a
Requery and Refresh on the subform, and even a
txtTotalPayments.Requery
before calling this function.
Now, the puzzling thing is that it works just fine if I set a
breakpoint on
the "dblPayments = ..." line and step over it - it then retrieves the
correct value! So it must be some kind of timing thing?
Am I doing something incorrectly? Is there a better way to get at the
sum
of a field in a list? I didn't want to use DSum() since it seems like
I'd
be running the same query twice.

Thanks!
Martin
 
Martin said:
I've spend a couple hours banging my head against the wall and can't
figure
out why this isn't working.
I have a subform with a list and a form footer that has a text field
(txtSumAmount) showing the sum of one of the fields in the list
("=Sum([DepositLineAmount])"). In the Form_Current of the main form I
set
the RecordSource of the subform accordingly, and then I want to use
the new
value of txtSumAmount in some other calculations.
So I added the following function to my subform:
Public Sub GetTotal(dblPayments As Double)
dblPayments = Nz(Me.txtTotalPayments, 0)
End Sub

However - in Access 2000 this always returns 0, even if I explicitly
do a
Requery and Refresh on the subform, and even a
txtTotalPayments.Requery
before calling this function.
Now, the puzzling thing is that it works just fine if I set a
breakpoint on
the "dblPayments = ..." line and step over it - it then retrieves the
correct value! So it must be some kind of timing thing?
Am I doing something incorrectly? Is there a better way to get at the
sum
of a field in a list? I didn't want to use DSum() since it seems like
I'd
be running the same query twice.
Click to expand...


Since the calculation of control expressions are done in a
lower priority thread than the execution of VBA code in
event procedures, you've definitely run into a race
condition with no good way to synchronize the VBA to the
control calculations. Here's some things you can try in
various combinations to see if it helps:

DoEvents (maybe more then one)
Me.Requery (unnecessary after setting recordsource)
Me.Recalc (probably not helpful in this case)

Since those things really can't guarantee the desired
result, you may want to try to find a way to do all the
dependent calculations using text box expressions
instead of VBA code.
 
try using an unbound control & code on the subform in the order of:
me.parent.form!conrol=...

hth
pieter
 
Thanks for everyone's replies.

I am using the number returned by my GetTotal() function to calculate
the values for a number of other text boxes on my form, which I update
in response to the "Current" event.

Thanks,
Martin

MacDermott said:
You haven't told us much about how you're using this function...

- Turtle

Martin Cibulka said:
Greetings,

I've spend a couple hours banging my head against the wall and can't
figure
out why this isn't working.
I have a subform with a list and a form footer that has a text field
(txtSumAmount) showing the sum of one of the fields in the list
("=Sum([DepositLineAmount])"). In the Form_Current of the main form I
set
the RecordSource of the subform accordingly, and then I want to use
the new
value of txtSumAmount in some other calculations.
So I added the following function to my subform:
Public Sub GetTotal(dblPayments As Double)
dblPayments = Nz(Me.txtTotalPayments, 0)
End Sub

However - in Access 2000 this always returns 0, even if I explicitly
do a
Requery and Refresh on the subform, and even a
txtTotalPayments.Requery
before calling this function.
Now, the puzzling thing is that it works just fine if I set a
breakpoint on
the "dblPayments = ..." line and step over it - it then retrieves the
correct value! So it must be some kind of timing thing?
Am I doing something incorrectly? Is there a better way to get at the
sum
of a field in a list? I didn't want to use DSum() since it seems like
I'd
be running the same query twice.

Thanks!
Martin
Click to expand...
Click to expand...
 
Back
Top