After Update - using subform values

[FranD]

I have a form (frmBatchInfo) which captures Charge Batch total information (ie, total $ per batch). This form contains a subform (frmSubFormVisitDetail) which captures the $ per visit. The sum of the $ per visit should equal the total amount entered in the main form. Therefore, I want to enter an After Update event procedure to check for inaccuracies - and cancel the update if they exist. I've tried several methods and nothing seems to work. Can anyone help - and please be a SPECIFIC as possible.... I'm somewhat of a novice!

 

[WayneRyan]

Fran,

You can put a textbox in the footer of your subform and have
it =Sum([YourTotalField]) and let Access take care of it. It
won't take any action, just display it.

Or you can use your AfterUpdate event to do a DSum:

If DSum("[YourTotalField]", "YourTable", "[YourKey] = '" & Me.YourKey & "'") > Forms![YourMainForm]![TotalDollars]) Then
   MsgBox("You overran the money.")
End If

That assumes that your primary key is a string (single-quotes).

Wayne

 

[FranD]

Using SubForm Values in Code

Thanks Wayne,

I do have a text field in the form footer of the subform which sums the $ value of each visit. But the problem is, not all data entry people will always check to verify that that text field matches the total amount entered on the Batch form - if the totals don't match, we have no way of knowing if the detail is inaccurate or if the batch header info is wrong.

I think I probably miss communicated my dilema. What I really need to do, is put some code in the "before update" event that will flag users that the two totals don't match and then cancel the record save event. I hope this makes sense. Thanks again....