Solved

Running total on Main from from Value on Subform

Posted on 2014-03-05
7
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Balance After Payment 12 62
Handle Apostrophes in SQL Parameter 16 68
Microsoft Access 32-bit or 64-bit? 11 60
ms/access follow hiperlink and generate image 5 19
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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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