Use 'On Change' or 'On Update' or other?

Here's two relevant quotes for you from the help file for the Change event:

--------------------------------------------
Setting the value of a control by using a macro or Visual Basic doesn't trigger this event for the control. You must type the data directly into the control, or set the control's Text property.
[ ... ]
The Change event doesn't occur when a value changes in a calculated control or when you select an item from the combo box list.
--------------------------------------------

Most of the other events for a text control are similar.  You may be able to work-around this by attaching your code to the Change event of Text10.  But that's a calculated control too....maybe the subform's (or a control on the subform) AfterUpdate property.  Essentially, you will not be able to hook to events on the textbox because it is calculated.  Just go to where the root of the change is (where is the nearest point requiring user intervention) and hook to that.

Okay...

If I put my event code on the After Insert and After Delete events of the subform, will this code run before or after Text10 is calculated, and before or after the TotalPaid control on the main form updates itself with Text10's new value?

--Galisteo8

(Remember, Text10 is where the payments are totalled up, and then the TotalPaid control automatically updates itself with that value by way of having its Control Source set to =[AuctionPmt Subform].[Form]![Text10].[Value].)

It will run before the values are updated. What are you trying to achieve in the code that you are running?

Sorry for delay. Am only working part-time...

Okay, here's what I'm trying to do.

There is a text control on the main form called WinBid. (This is manually entered.)
There is a text control on the main form called TotalPaid. (This value reflects the value of Text10 in the subform.)
There is a text control on the main form called GrossProfit. (This value calcs WinBid minus auction fees.)

I am trying to trigger the GrossProfit calculation when the TotalPaid control equals the value in the WinBid control. For certain reasons that I won't bother to go into here, I cannot have the GrossProfit calculate until the auction winner has paid in full.

Note: TotalPaid is already properly reflecting the value of Text10 as payments are added and deleted. And, the GrossProfit calculation works just fine. My problem is making GrossProfit remain 0 until TotalPaid = WinBid.

So basically, in chronological order, what I'm needing is:

1) The user enters a new payment record into the subform
2) The total of all payments is re-calculated in the subform's Text10 control (this works fine)
3) The TotalPaid control on the main form changes to reflect the new value in Text10 (this works fine)
4) An event/trigger runs if the new value of TotalPaid is >= WinBid (this is what I still need)
5) The event causes the GrossProfit calculation on the main form to be updated

--Galisteo8

The last check you need should go in the same place you are doing the calculation for GrossProfit.  It will probably look something like this:

If TotalPaid < WinBid Then
    GrossProfit = 0
Else
    GrossProfit = WinBid - [Auction Fees]
End If

Since TotalPaid has a control source from the sub-form, that means you really need to hook into an event from that section.  It can't be Text10, since that is a calculated control, and you cannot guarantee the event will fire properly.  Perhaps hooking into the subform's OnCurrent or OnAfterUpdate event might do the trick, but you will have to experiment to figure out which event will best suit your needs.  Is GrossProfit attached to a control source for its calculation?

The GrossProfit control source is the GrossProfit column of the AUCTION table. At the moment, Its value is determined by an OnChange event in the WinBid control, such that when a WinBid amount is entered, a value is calculated ( If WinBid is > 0, then GrossProfit = WinBid - [AuctionFees] ) and put in the GrossProfit control.  In lieu f that I now need to set up an event so that the calculation of GrossProfit doesn't occur until a) WinBid > 0, and b) TotalPaid = WinBid.  Since the WinBid value is entered first (before any payments are received and TotalPaid determined), I need to somehow transfer the event/trigger to when TotalPaid is changed -- because THAT's when I now need GrossProfit to finally be other than zero. And that led me to starting this thread. :)

Whoops - Now that I'm back in the office, I see that the value of GrossProfit is determined by an AfterUpdate event on the WinBid control, not an OnChange event.

OK, as before, you will have to hook into the event that originally initiates the change.  You cannot use the TotalPaid control, since it is calculated.  TotalPaid's information comes from Text10, which is ALSO a calculated control.  Text10's information comes directly from the population of the subform, yes?  So attach your check to one of the subform's events (possibilities are OnCurrent, OnChange, OnAfterUpdate, etc.) and experiment with which event will give you the timing you need.  

To rephrase, you are going to use an event from the subform to trigger a change in the control on the main form.

Okay, so far everything makes sense. However, I have tried the following code for both the OnCurrent and AfterUpdate events of the subform, and each time I got an error. Here's the code:

Private Sub Form_AfterUpdate()
Dim Gross As Currency
If [AuctionForm].[Form]![WinBid] > 0 Then
   If [AuctionForm].[Form]![TotalPaid] >= [AuctionForm].[Form]![WinBid] Then
      Gross = Nz([AuctionForm].[Form]![TotalPaid], 0) - Nz([AuctionForm].[Form]![EbayListFee], 0) - Nz([AuctionForm].[Form]![EbayComm], 0) - Nz([AuctionForm].[Form]![CCFee], 0)
      [AuctionForm].[Form]![AuctionGross] = Gross
   Else: [AuctionForm].[Form]![AuctionGross] = ""
   End If
Else: [AuctionForm].[Form]![AuctionGross] = ""
End If
End Sub

And here's the error:

"Run-time error '2465': Microsoft Access can't find the field '|' referred to in your expression."

There is no '|' in my expression!

Try this change, and tell me which line is causing the error should one reappear.  If you get the same error, do a text search in the code by pressing Ctrl-F.  Don't rely on just your eyes to determine if there's a look-alike typo.  They are easy to miss, especially if you are already familiar with what the code should be.

Private Sub Form_AfterUpdate()
Dim Gross As Currency

Gross = 0

If (([AuctionForm].[Form]![WinBid] > 0) AND ([AuctionForm].[Form]![TotalPaid] >= [AuctionForm].[Form]![WinBid])) Then
      Gross = Nz([AuctionForm].[Form]![TotalPaid], 0) - Nz([AuctionForm].[Form]![EbayListFee], 0)
      Gross = Gross - Nz([AuctionForm].[Form]![EbayComm], 0)
      Gross = Gross - Nz([AuctionForm].[Form]![CCFee], 0)
End If

[AuctionForm].[Form]![AuctionGross] = Gross

End Sub

Tried the different code (thanks for the improved structure of that), but got the same error. The debugger highlighted this line:

If (([AuctionForm].[Form]![WinBid] > 0) And ([AuctionForm].[Form]![TotalPaid] >= [AuctionForm].[Form]![WinBid])) Then

I am having a similar issue with another event that I am using elsewhere in this same database, and it seems the problem always occurs the first time that the [ bracket appears.

(Idle thought: Does it make a difference that my database back-end is SQL Server and not Access?)

--Galisteo8

P.S. I did a Ctrl-F search for the | character, but found none.

Routinet, do you have any ideas on the cause of this error? It makes absolutely no sense to me.

I'll give this a shot and let you know...

routinet,

Your "Attempt 2" solved the '|' error that I was getting. Thanks you! New problem, however, and this has to do with the timing of this event...

I am using this as the After_Update event on the subform. When a payment is added/deleted/changed in the subform, the code, as follows, is supposed to run.

Private Sub Form_AfterUpdate()
Dim Gross As Currency
Gross = 0
If (([Forms]![AuctionForm]![WinBid] > 0) And ([Forms]![AuctionForm]![TotalPaid] >= [Forms]![AuctionForm]![WinBid])) Then
      Gross = Nz([Forms]![AuctionForm]![TotalPaid], 0) - Nz([Forms]![AuctionForm]![EbayListFee], 0)
      Gross = Gross - Nz([Forms]![AuctionForm]![EbayComm], 0)
      Gross = Gross - Nz([Forms]![AuctionForm]![CCFee], 0)
      [Forms]![AuctionForm]![PaidStatus] = True
End If
[Forms]![AuctionForm]![AuctionGross] = Gross
[Forms]![AuctionForm]![PaidStatus] = False
End Sub

And it *does* run... but it doesn't catch the changes until the NEXT TIME it is run. Here's what happens:

1)   Let's say WinBid is $1,000. I add a payment to the subform of $800, so the TotalPaid control becomes $800. TotalPaid < WinBid, so PaidStatus remains False, and Gross remains 0. This is correct.
2)   BUT... I then add a $200 payment, and as soon as soon as I click out of that row of the subform, the TotalPaid becomes $1000, so that TotalPaid = WinBid, but the Gross and PaidStatus do not change.
3)   AND THEN... If I go back and edit a payment in the subform, THEN the PaidStatus becomes True and the Gross amount gets calculated.

Apparently, the code is executing prior to the subform record being applied...? Which means that any payment eidts are not being taken into account before the code starts comparing the TotalPaid and WinBid values.

Oh, wait... When payments are entered into the subform, they are being entered into the "Amount" control. There is another control in the footer of the subform which totals up all the amounts from the subform, i.e. its data source is =Sum([Amount]). That total is what is then carried forward to the TotalPaid control on the main AuctionForm.

So, when the subform's After_Update event is run, it is AFTER the payment is entered into the subform, but BEFORE the subform's total gets carried out to the TotalPaid control... so the After_Update is using the "old" TotalPaid value.

What can I do to resolve this issue? I can't put the After_Update event on the total control in the subform footer, since it is updated automatically, not manually...

Remember that the .Value property of a text control is the default property.  By referencing "[Forms]![AuctionForm]![WinBid]", you are implicitly referencing WinBid.Value.  Here's a small excerpt from the help file:

Text box [The value of the control's Text property] The Text property returns the formatted string. The Text property may be different than the Value property for a text box control. The Text property is the current contents of the control. The Value property is the saved value of the text box control. The Text property is always current while the control has the focus.  

In other words, since you are referencing the .Value property, you are referencing the SAVED values of the control.  For unbound controls, the help was unclear whether or not .Value is updated on a calculation, but since your calculation is also based on the implicit reference to the .Value properties of the controls involved, it logically follows it is receiving old data until the next iteration of updates.  Solutions might include using a reference to the .Text property of the controls, or forcing a refresh of the forms (through the .Refresh or .Requery methods), then all calculated controls from the OnAfterUpdate event.  You can also use a breakpoint from within the OnAfterUpdate event to determine if this is the case.  Highlight the first line of code for the event and press F9.  Now run the form to the point where the code should trigger and the debugger should take you straight to that line.  Highlight the entire [Forms...Winbid] reference, and add it as a watch.  Is the value updated, or does it still hold the amount of the previous iteration?  You can do the same with the other references to find out which one is going wrong.  By adding watches for .Value, .Text, and .OldValue, you should be able to see exactly how Access handle the updates of bound and calculated controls.  Understand?

Once the code hits the breakpoint, is there a way to step through the code?

I tried using the .text designation, but got an error saying that I could not reference those controls unless they had focus. Since the code is an AfterUpdate event on the subform, then it is certain that WinBid and TotalPaid, both of which are on the MAIN form, do not have focus. So, I've proceeded with your test using the "default" .value property...

I put a breakpoint on the Gross=0 line.

Interesting -- I see that the 'watch' values are different than the what I see when I simply roll over each control's name in the code itself.

According to the Watch, Text10 still has the "old" value (and so, therefore, does TotalPaid). That is, if there was already a $500 payment in the subform, and I add a $100 payment, when the code hits the breakpoint Text10 and TotalPaid are still only $500.

If I roll my mouse over those controls in the code, the "mouse help" caption (or whatever you call it) says that they are both already $600... But obviously they are not.

Okay, here we go... Forget my prior post. I just tried inserting a Requery into the code and that did the trick.

So the code for the subform's AfterUpdate event is now:
********************************
Private Sub Form_AfterUpdate()
Dim Gross As Currency
Gross = 0

'   Force a requery of AuctionPmt_Subform so that Text10 updates and
'   promulgates the new value to the TotalPaid control on the main
'   AuctionForm *before* comparing TotalPaid to WinBid
Me.Requery

If (([Forms]![auctionform]![WinBid] > 0) And ([Forms]![auctionform]![TotalPaid] >= [Forms]![auctionform]![WinBid])) Then
      Gross = Nz([Forms]![auctionform]![TotalPaid], 0) - Nz([Forms]![auctionform]![EbayListFee], 0)
      Gross = Gross - Nz([Forms]![auctionform]![EbayComm], 0)
      Gross = Gross - Nz([Forms]![auctionform]![CCFee], 0)
      [Forms]![auctionform]![AuctionGross] = Gross
      [Forms]![auctionform]![PaidStatus] = True
Else:
      [Forms]![auctionform]![PaidStatus] = False
      [Forms]![auctionform]![AuctionGross] = 0
End If
End Sub
********************************
Works great as far as I can tell! When I enter and modify payments into the subform, the TotalPaid control flickers blank for just a moment... SO I flagged the code with a bunch of msgbox markers and it appears that the TotalPaid control remains blank throughout the IF statement, but it finishes with the proper value, AND thet proper value is being used in the IF statement as well, so... perhaps this flicker is a non-issue...?

I'll wait for any final comments on that flicker thing.... otherwise, I think this thread is generally wrapped up.

yupyup, the Requery took care of it.  The focus thing is an issue with referencing properties for those controls, but there are work-arounds.  We will not have to worry about them since the issue is resolved, though.  :)

Good luck with the rest of your project.

Okay, thanks to all: Flavo, from whom the Requery idea originated; shanesuebsahakarn who originally noted that the solution would need to be on the subform itself; and especially to routinet for providing a lot of syntax support and sticking with me during implementation. :)