Determine subform total before code runs

[BruceM]

I asked this question before, but it was in another thread. The only person
replying in that thread did not post in response to my follow-up question
after several days, so I am posting again.

I have a typical Purchase Order database (Access 2003) in which the main PO
information is in the PO record (and on the main PO form) and the line items
are in a related table (and appear on a continuous subform). There is more,
such as a Products table, but it is not relavant to the question at hand.

Every PO is approved by several departments. When the line items total for
a single purchase order exceeds $2000 an extra level of approval is needed.
In that case I want to show the appropriate text box for entering the
approval; otherwise that text box will be hidden. This is a simplified
version of a public function I am using. It is simplified in that there are
more controls involved in the actual code.

Select Case Forms!frmPO!fsubPO_Items.Form!txtTotal
Case Is < 2000
Forms!frmPO!txtApproval.Visible = False
Case Else
Forms!frmPO!txtApproval.Visible = True
End Select

txtTotal is the text box in the subform footer that contains the calculation
=Sum([Quantity]*[UnitPrice]). I can call the function from either the main
form's Current event or the subform's After Update event. It works properly
when run from the form's Current event (that is, txtApproval is either
visible or not depending on the line items total). However, I would like
the function to run in the Line Items subform as soon as the total exceeds
2000. More to the point, probably, is that a message will be generated as
soon as the total exceeds 2000, advising the user that the extra level of
approval is needed. The only user-editable controls/fields on the subform
are the Product (selected from a combo box based on the Products table) and
the Quantity. I have tried saving the record as soon as the Quantity text
box is updated, and calling the hide/unhide function in the subform's After
Update event, but it is working with the old value in txtTotal rather than
the updated value.

To summarize, I need the Sum([Quantity]*[UnitPrice]) value to be available
to the function as soon as Quantity is updated, but the code seems to be
using the previous value.

 

[Marshall Barton]

I asked this question before, but it was in another thread. The only person
replying in that thread did not post in response to my follow-up question
after several days, so I am posting again.

I have a typical Purchase Order database (Access 2003) in which the main PO
information is in the PO record (and on the main PO form) and the line items
are in a related table (and appear on a continuous subform). There is more,
such as a Products table, but it is not relavant to the question at hand.

Every PO is approved by several departments. When the line items total for
a single purchase order exceeds $2000 an extra level of approval is needed.
In that case I want to show the appropriate text box for entering the
approval; otherwise that text box will be hidden. This is a simplified
version of a public function I am using. It is simplified in that there are
more controls involved in the actual code.

Select Case Forms!frmPO!fsubPO_Items.Form!txtTotal
Case Is < 2000
Forms!frmPO!txtApproval.Visible = False
Case Else
Forms!frmPO!txtApproval.Visible = True
End Select

txtTotal is the text box in the subform footer that contains the calculation
=Sum([Quantity]*[UnitPrice]). I can call the function from either the main
form's Current event or the subform's After Update event. It works properly
when run from the form's Current event (that is, txtApproval is either
visible or not depending on the line items total). However, I would like
the function to run in the Line Items subform as soon as the total exceeds
2000. More to the point, probably, is that a message will be generated as
soon as the total exceeds 2000, advising the user that the extra level of
approval is needed. The only user-editable controls/fields on the subform
are the Product (selected from a combo box based on the Products table) and
the Quantity. I have tried saving the record as soon as the Quantity text
box is updated, and calling the hide/unhide function in the subform's After
Update event, but it is working with the old value in txtTotal rather than
the updated value.

To summarize, I need the Sum([Quantity]*[UnitPrice]) value to be available
to the function as soon as Quantity is updated, but the code seems to be
using the previous value.

Right, that's a common question. The "problem" is that text
box expression calculations are done in an asynchronous task
the runs at a lower priority than most other tasks,
especially VBA code execution.

In other words, if you want to use the result of a text box
calculation in a VBA procedure, then the calculation must
also be done in the VBA procedure.

Efficiency aside, this is not too difficult. Just save the
changed data back to the table and use DSum to get the new
total:

Me.Dirty = False 'save record
newtotal = DSum("Quantity*UnitPrice", "tblPOdetails", _
"PoID=" & Me.PoID)
Parent.txtApproval.Visible = (newtotal >= 2000)

 

[BruceM]

I've been away for the past few days, and I forgot to flag this message
before I left, so I did not discover your reply until just now. Thanks for
the tip. It works. I had tried DSum, but with a misguided approach, so it
did not work (I was multiplying the DSum of Quantity by the DSum of Unit
Price, which of course is mathematically untenable). Thanks for pointing me
in the right direction.

I asked this question before, but it was in another thread. The only
person
replying in that thread did not post in response to my follow-up question
after several days, so I am posting again.

I have a typical Purchase Order database (Access 2003) in which the main
PO
information is in the PO record (and on the main PO form) and the line
items
are in a related table (and appear on a continuous subform). There is
more,
such as a Products table, but it is not relavant to the question at hand.

Every PO is approved by several departments. When the line items total
for
a single purchase order exceeds $2000 an extra level of approval is
needed.
In that case I want to show the appropriate text box for entering the
approval; otherwise that text box will be hidden. This is a simplified
version of a public function I am using. It is simplified in that there
are
more controls involved in the actual code.

Select Case Forms!frmPO!fsubPO_Items.Form!txtTotal
Case Is < 2000
Forms!frmPO!txtApproval.Visible = False
Case Else
Forms!frmPO!txtApproval.Visible = True
End Select

txtTotal is the text box in the subform footer that contains the
calculation
=Sum([Quantity]*[UnitPrice]). I can call the function from either the main
form's Current event or the subform's After Update event. It works
properly
when run from the form's Current event (that is, txtApproval is either
visible or not depending on the line items total). However, I would like
the function to run in the Line Items subform as soon as the total exceeds
2000. More to the point, probably, is that a message will be generated as
soon as the total exceeds 2000, advising the user that the extra level of
approval is needed. The only user-editable controls/fields on the subform
are the Product (selected from a combo box based on the Products table)
and
the Quantity. I have tried saving the record as soon as the Quantity text
box is updated, and calling the hide/unhide function in the subform's
After
Update event, but it is working with the old value in txtTotal rather than
the updated value.

To summarize, I need the Sum([Quantity]*[UnitPrice]) value to be available
to the function as soon as Quantity is updated, but the code seems to be
using the previous value.

Right, that's a common question. The "problem" is that text
box expression calculations are done in an asynchronous task
the runs at a lower priority than most other tasks,
especially VBA code execution.

In other words, if you want to use the result of a text box
calculation in a VBA procedure, then the calculation must
also be done in the VBA procedure.

Efficiency aside, this is not too difficult. Just save the
changed data back to the table and use DSum to get the new
total:

Me.Dirty = False 'save record
newtotal = DSum("Quantity*UnitPrice", "tblPOdetails", _
"PoID=" & Me.PoID)
Parent.txtApproval.Visible = (newtotal >= 2000)