Question about Requery with If-Then statement

[RC]

I have a subform where budget amounts and purchase order amounts are entered
for accounts. This subform was created from a query named BGTs_and_POs. I
have added three controls into the footer of this subform, one for
calculating the sum of the budget amounts, one for the sum of the purchase
order amounts, and the third, named ACCOUNT_BALANCE_SUBTOTAL, for
calculating the difference between the sums of the budget and purchase order
amounts for the account balance.
After entering a purchase order amount I need for a message box to appear
with a warning only if the balance calculates as less than or equal to zero.
I have the following as Event Code in the AfterUpdate Event for the control
where the purchase order amounts are entered. The problem seems to be that
the "If - Then" statement runs before the control named
ACCOUNT_BALANCE_SUBTOTAL has been updated, resulting with the message box
either appearing when it shouldn't, or not appearing when it should.

Private Sub PO_AMT_AfterUpdate()
Dim VarID As Variant
VarID = Me.BGTs_and_POs_ID
Me.Requery
Me.Recordset.FindFirst "BGTs_and_POs_ID =" & VarID
If Me.ACCOUNT_BALANCE_SUBTOTAL <= 0 Then
MsgBox "WARNING! THIS ACCOUNT HAS A ZERO BALANCE or IS OVERDRAWN!"
End If
End Sub

I've tried replacing the Me.Requery with
Me.ACCOUNT_BALANCE_SUBTOTAL.Requery, but that didn't work either. Also, when
using Me.Recalc instead of Me.Requery the focus is always be sent to the
first record instead of returning to the "current record". I've tried out of
the "trial and error" ideas I know and need help from the experts here.

Thanks,
RC

 

[SteveM]

Your best bet would be to do the calculation in your procedure and inspect
the result. You can use this result as the criteria that determines whether
your warning displays or not.

If you want to prevent users from entering values that result in 0 or less,
you can use the BeforeUpdate event and use 'Cancel = True' when the input
results in a calculated value that is not valid. This will return the user to
that field and require them to enter another value.

Steve

 

[RC]

Thanks Steve, the users said that they would like to be able to enter the
values and have the warning display rather than be prevented from entering
values. I'm not sure how to go about setting up the calculation in the
procedure and inspecting the result, and am hoping you would provide an
example.
Here is additional information about that subform. The control names in the
subform where the values are entered are AMT_BUDGETED for the budget amount,
and PO_AMT for the purchase order amounts. In the footer of this subform is a
calculated control named AMT_BUDGETED_SUBTOTAL that calculates the sum of all
the values entered into AMT_BUDGETED for an account, and another calculated
control named PO_AMT_SUBTOTAL that calculates the sum of all the values
entered into PO_AMT for and account. The Record Source for that third
control, ACCOUNT_BALANCE_SUBTOTAL, is =
[AMT_BUDGETED_SUBTOTAL]-[PO_AMT_SUBTOTAL].

Your help with this is greatly appreciated?
RC

 

[RC]

Steve, here is somewhat of a different approach I've been trying that, so
far, looks like it's going to work. There is a control named PO_NO that is
already conveniently located between the AMT_BUDGETED and PO_AMT controls on
the same subform in question. The Event Codes listed below are now set up as
the After Update event for both, the AMT_BUDGETED control, and the PO_AMT
control.

Private Sub AMT_BUDGETED_AfterUpdate()

Dim VarID As Variant
VarID = Me.BGTs_and_POs_ID
Me.Requery
Me.Recordset.FindFirst "BGTs_and_POs_ID = " & VarID
Me.PO_NO.SetFocus

End Sub

Private Sub PO_AMT_AfterUpdate()

Dim VarID As Variant
VarID = Me.BGTs_and_POs_ID
Me.Requery
Me.Recordset.FindFirst "BGTs_and_POs_ID = " & VarID
Me.PO_NO.SetFocus

End Sub

The following Event Code is now setup as the On Lost Focus event for the
PO_NO control.

Private Sub PO_NO_LostFocus()

If Me.ACCOUNT_BALANCE_SUBTOTAL <= 0 Then
MsgBox "WARNING! THIS ACCOUNT HAS A ZERO or NEGATIVE BALANCE! "
End If

End Sub

Now it appears that, unless a user is super fast with a "mouse click", the
ACCOUNT_BALANCE_SUBTOTAL control has plenty of time to be "refreshed with the
results from the calculation of the latest value entry" before the
"If---Then" statement is triggered.
What are your thoughts on all of this?

Steve, thanks to you and the others "out there" for all the solutions,
information, and insights you all provide, as they certainly help the rest of
us get by the "obstacles".

RC