Form/Subform Running Total not Updating

In my Form/Subform relationship the subform contains a checkbox I would like that each time the checkbox is either set to true or false the running total on the the main form would requery.  It seems to only update after a second action has been performed.  When I check one box my total says $0.00 after I make a second it reflects the total amount from the first selection.

Here is what Im using:


Dim RunningTotal As String
If DCount("*", "[qryRunTotal]") > 0 Then
    RunningTotal = DLookup("[RunTotal]", "[qryRunTotal]")
    Forms!frmToBeAdjusted!Text57 = RunningTotal
Else
    RunningTotal = "$0.00"
    Forms!frmToBeAdjusted!Text57 = RunningTotal
End If

Open in new window

LVL 1
atreidiesAsked:
Who is Participating?
 
Bill RossConnect With a Mentor Commented:
Sample...

Pricvate sub_MyCheckBox_AfterUpdate()
  Dim RunningTotal As String
  DoCmd.RunCommand accmdSaveRecord
  If DCount("*", "[qryRunTotal]") > 0 Then
     RunningTotal = DLookup("[RunTotal]", "[qryRunTotal]")
     Forms!frmToBeAdjusted!Text57 = RunningTotal
  Else
     RunningTotal = "$0.00"
     Forms!frmToBeAdjusted!Text57 = RunningTotal
  End If

End sub
0
 
Bill RossCommented:
Hi,

You need to place the calculations in the AfterUpdate event of the checkbox.

Best regards,

Bill
0
 
atreidiesAuthor Commented:
Ive tried the After/Before events of the subform, form and even the check box they all seem to be one total behind.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Bill RossCommented:
Hi again,

In looking at your code if the query relies on data that may nave been updated then you need to save the record.  This is in addition to putting the code in the AfterUpdate event of the checkbox.

Add:

DoCmd.RunCommand accmdSaveRecerd

as the first line in the AfterUpdate event then run your process to get the subtotal.

Regards,

Bill
0
 
Bill RossCommented:
Hi,

In general if you are running a query that is using data on the form you need to push that data bak to the db so the query can find the current values.  You can put a running total on the form footer and get the result you are seeking without code if trhe values to be summed up are on the form.

Set the recordsource for Forms!frmToBeAdjusted!Text57

=Sum([FieldName])

That will always display the total.

Bill
0
 
Bill RossCommented:
Please post the SQL for qryRunTotal
0
 
atreidiesAuthor Commented:
Perfect once I changed Recerd to Record...thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.