Solved

Running total on Main from from Value on Subform

Posted on 2014-03-05
7
374 Views
Last Modified: 2014-03-11
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
0
Comment
Question by:Karen Schaefer
  • 3
  • 3
7 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39908257
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.
0
 
LVL 84
ID: 39908890
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?
0
 

Author Comment

by:Karen Schaefer
ID: 39909919
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84
ID: 39910430
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.
0
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 39910968
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

0
 
LVL 84
ID: 39914725
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.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39920000
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question