Running total on Main from from Value on Subform

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
I have tried various methods without success.

I have a field MainForm.txtMCFPaidUS ( this should be the running total and automatically update if the value in the subform.NetAmount changes.

I have tried txtMCFPaidUS.value = subform.NetAmount

also
     strSQL = "SELECT Sum(NetAmount) AS NetTotalUS" & _
           " FROM tblinvoice" & _
            " GROUP BY tblinvoice.ContractNumber" & _
            " HAVING (((tblinvoice.ContractNumber)=GetgContract()))"

    Set rs = curDB.OpenRecordset(strSQL)
    Me.txtMCFPaidUS = rs("NetTotalUS")

get error value enter is not valid for this field.

what is the best method to have an up to date value of the total of NetAmount displayed on the Main form.

K
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

Commented:
Are you looking for the total of the subform records for each main record?
Upload a sample database showing the required output.

You may have the total of subrecords in the subform.
Set that value to the totalField in the main form.
Most Valuable Expert 2012
Top Expert 2014

Commented:
If this didn't work:

     strSQL = "SELECT Sum(NetAmount) AS NetTotalUS" & _
           " FROM tblinvoice" & _
            " GROUP BY tblinvoice.ContractNumber" & _
            " HAVING (((tblinvoice.ContractNumber)=GetgContract()))"

    Set rs = curDB.OpenRecordset(strSQL)
    Me.txtMCFPaidUS = rs("NetTotalUS")

Then your SQL is not right. That SQL statement essentially does this:

Give me the SUM of tblInvoices.NetAmount for each ContractNumber, where the ContractNumber is <whatever is returned from GetgContract>

So I'd suspect the culprit is the GetgContract method - can you show us that code?
Karen SchaeferBI ANALYST

Author

Commented:
Scott,

the code works fine, the problem is timing, unless the user hits f5, the value does not update authomatically.

I have tried me.refresh on afterupdate of the subform, on current of the mainform or on the afterupdate of the subform.field(NetAmount)  none of these will automaticaly update the running total automatically.

What am I missing?

K
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2014

Commented:
automatically update if the value in the subform.NetAmount changes.
If this is the trouble, then you should run the code when (a) the mainform record changes (i.e. the mainform's current event) and (b) when any control that feed that SQL statement change.

You can't just do a Refresh. You have to actually call the code.
BI ANALYST
Commented:
I need it to change on change of the subform, hence the need for a running total.  The user may have to enter multiple invoices for the same contract (mainform info) so I need the net total to update accordingly.

this is driving me nuts, this should be a no brainer.

snapshot
Code on before insert of the subform.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim nAmtTotal As Long
Dim nNetAmount As Long

Forms![frmCodingSlip]![txtMCFRemainingUS].SetFocus
Me.Refresh
Forms![frmCodingSlip]!Sub1.SetFocus
    'nAmtTotal = DLookup(DSum(NetAmount), "TblInvoice", "ContractNumber = " & Chr(34) & gContractID & Chr(34))
    nNetAmount = DSum("NetAmount", "TblInvoice", ContractNumber = " & getgcontract()")
     If nNetAmount <= 0 Then
        Select Case _
            MsgBox("Unable to proceed due to lack of avalable funds." _
             , vbCritical Or vbDefaultButton1, "Funds Discrepancy")
            Case vbOK
            Forms![frmCodingSlip]![txtMCFRemainingUS].value = nAmtTotal
                Cancel = True
            Exit Sub
        End Select
    End If
End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Requerying the Parent form will cause you to lose your "place" in the records. If you're on record 15, for example, when you requery the form you'll be placed back at record 1.

Honestly, it's like sometimes you ask these questions just so we can be a sounding board. You often don't even try to implement the fixes we suggest, and go off on tangents that are unrelated to the subject at hand. it gets quite frustrating trying to help you.

Good luck with your projects. Hopefully the other experts will be able to assist.
Karen SchaeferBI ANALYST

Author

Commented:
figured it out

             Me.Parent.Requery
on the after update of the subform and on beforeinsert of subform.  seems to do the trick.  thanks for the input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial