Solved

Running total on Main from from Value on Subform

Posted on 2014-03-05
7
371 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

22 Experts available now in Live!

Get 1:1 Help Now