Solved

Running total on Main from from Value on Subform

Posted on 2014-03-05
7
370 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now