ACCESS 2003 - Can't get a sum in VBA

In a procedure I want something like the following - only that works.

    Dim InvAmt As Currency, SumAmt As Currency
   
    InvAmt = Forms!frmPurchaseOrder!txtInvoiceAmount
    SumAmt = Sum(Forms!frmPurchaseOrder![AP_Job SubForm]![amount])
    Forms!frmPurchaseOrder![AP_Job SubForm]!txtRemainder = InvAmt - SumAmt

However, the compiler does not like the middle statement.  It objects to  = Sum(Forms!...  etc.  Specifically it does not like the  Sum,  which it feels should be a Sub or Function.  I thought it was an intrinsic function.  Wrong again!

What do you suggest I do to get to give SumAmt the total of the [amount] in a line item set.

ljcorAsked:
Who is Participating?
 
puppydogbuddyConnect With a Mentor Commented:
Jack,
Patience, please. I am off to ZZZZZZZZZZZZZZZZZZZZZZZZZ now, but I will pick it up tomorrow.
I looked up some alternate generic syntaxes for the recalc method:and it looks like I had it wrong.
Forms![Mainfrm]![Subfrm].Form.Recalc
Forms("Mainfrm").Subfrm.Form.Recalc
Me.subfrm.Form.Recalc
_______________________________
 so change my  previous to thiis:
 Me.[AP_Job SubForm].Form.Recalc

If that does not work.
Questions:
1. did you verify that [AP_Job SubForm]. is the name showing for the subform control on the main form?
2. when your code breaks for the 438 error, did you hover over the objects and see what values  are showing for them......for example does it show the correct name for subform control or does it show something   else.
0
 
puppydogbuddyCommented:
Try this:
   
    SumAmt = Sum(Forms!frmPurchaseOrder![AP_Job SubForm].Form![amount])
0
 
ljcorAuthor Commented:
PDog,

The compiler still didn't like  SUM.

jack
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
puppydogbuddyCommented:
hi ljcor:
ok, here's the scoop!  The way to reference a subform total from the main form is to place a hidden textbox in the subform footer that does the actual summation. Then the main form will reference the textbox in the subform footer to get the total ......

                  se this linK for the details
                            http://www.databasedev.co.uk/subform-total.html
0
 
ljcorAuthor Commented:
Let me see if I can explain what is happening on my form.

I do have a textbox on the subform footer, that is txtRemainder (as noted above.)  The Control Source for this control is  =Forms!frmPurchaseOrder!txtInvoiceAmount-Sum([amount]).

In the header if I enter txtInvoiceAmount to say $4000, it does not chage txtRemainder whether if I have not entered any line items at that point - but I do want it so show the difference to zero line item [amount].

If I enter $1000 in [amount] in the line item, it will show a positive $3000 balance.  That's good.

If I then go back and adjust the ItxtnvoiceAmount to $1000,  txtRemainder will show $0 balance.  That's good.

If I now change the txtInvoiceAmount to $500 txtRemainder nothing happens unless I now tab through the line item to the end - then  txtRemainder will again adjust to the correct amount.

So the problem is that depending on where the tabbing is located,  txtRemainder may or may not show the actual balance, negative or positive.  And that's not good.

And I don't know what to do about it.  So I tried to put the calculation code (in the opening of this question) in the LostFocus of txtInvoiceAmount since that is where my anomolies occur.

So I am still baffled.  And I am not sure that a calculation and assignment to txtRemainder will work since there is an algorithim in the Control Source of txtRemainder.

Now I leave it to the gods.  That's you.
0
 
puppydogbuddyCommented:
try placing this code behind your actual subform (not the subform  control)
Private Sub txtnvoiceAmount_AfterUpdate()
      Me!txtRemainder.Recalc
End Sub
0
 
ljcorAuthor Commented:
txtInvoiceAmount is actually in the header.  So I wrote:

Private Sub txtInvoiceAmount_AfterUpdate()
    Forms!frmPurchaseOrder![AP_Job SubForm]!txtRemainder.Recalc
End Sub

In runtime the debugger essentially said it could not transfer control to the subform.  But I liked the idea.  I had earlier done something similar with Requery but (I'm sure you know) that didn't go either.  I had not recalled the Recalc function.

0
 
ljcorAuthor Commented:
Specifically it said it could not move the FOCUS to the subform.
0
 
puppydogbuddyCommented:
ljcor:
Let me make sure I have this straight.  When you say that txtInvoiceAmt is in the header, I thought you were telling me that it was in the header of the subform,,,,,,is that correct?? And txtRemainder is in the footer of the subform.....correct?  If they are in the actual subform and you placed the code behind the <<<subform>>>>> like I suggested, then the syntax I gave you before should work

Let me know.
0
 
ljcorAuthor Commented:
Here is the layout:

txtInvoiceAmount is in the mainform header.

txtRemainder is in the subform footer.

txtAmount is part of the multi-line line item set in the subform detail section.

Sorry about the confusion.

0
 
ljcorAuthor Commented:
Here is the layout:

txtInvoiceAmount is in the mainform header.

txtRemainder is in the subform footer.

txtAmount is part of the multi-line line item set in the subform detail section.

Sorry about the confusion.

0
 
puppydogbuddyCommented:
Jack,
try this and let me know what happens. In this reference, the reference to the subform should be to the subform control (not the actual subform), so please verify that [AP_Job SubForm] is the name of the subform control.

Private Sub txtInvoiceAmount_AfterUpdate()
    Me![AP_Job SubForm].Form!txtRemainder.Recalc
End Sub
0
 
ljcorAuthor Commented:
We didn't get far with that one either.

Error 438  Object doesn't support this property or method.

I am thinking that I should just write a batch of code for the places that need it for this exercise.

0
 
ljcorAuthor Commented:
Actually, PDog, I had disappeared for hours twice in the last 12.  This time I was out for dinner at a very favorite and highly acclaimed - yet still reasonably priced - restaurant here in Honolulu.  Now I am also about ready for zzzzzz time.

Before i checked out for the night, I tried your last solution - and no matter what I entered, it worked perfectly.  And I tried a whole bunch of stuff; it all worked.

So I thank you greatly for all of your help and sticktoitiveness (is that a word?)  You are not only teriffic but you are easily deserving of all the high accolades thrust upon you by our friend MX who told me how highly he thought of you in a private email.  Deserving, for sure!

All the best to you.
Jack
0
 
puppydogbuddyCommented:
Jack,
Thanks for the kind words and the points. Whew....that is one thing with Access, not only do you have to know what approach to take, you also have to get the syntax down perfectly..or no cigar.  Talk to you later.

PDog
0
 
ljcorAuthor Commented:
PD Buddy,

I am going to post another question in about 5 minutes and would definitely appreciate if you could take a look at it.

The title is:  ACCESS 2003 - Need a query to verify a data entry as valid'

If you are around and have a chance to look at it, that would be great.


Jack
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.