[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ACCESS 2003 - Can't get a sum in VBA

Posted on 2007-10-13
16
Medium Priority
?
1,359 Views
Last Modified: 2008-01-09
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.

0
Comment
Question by:ljcor
  • 9
  • 7
16 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20071684
Try this:
   
    SumAmt = Sum(Forms!frmPurchaseOrder![AP_Job SubForm].Form![amount])
0
 

Author Comment

by:ljcor
ID: 20071745
PDog,

The compiler still didn't like  SUM.

jack
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20071773
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:ljcor
ID: 20071832
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20071881
try placing this code behind your actual subform (not the subform  control)
Private Sub txtnvoiceAmount_AfterUpdate()
      Me!txtRemainder.Recalc
End Sub
0
 

Author Comment

by:ljcor
ID: 20071931
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
 

Author Comment

by:ljcor
ID: 20071941
Specifically it said it could not move the FOCUS to the subform.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20072357
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
 

Author Comment

by:ljcor
ID: 20072394
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
 

Author Comment

by:ljcor
ID: 20072706
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20072784
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
 

Author Comment

by:ljcor
ID: 20072990
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
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 2000 total points
ID: 20073068
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
 

Author Comment

by:ljcor
ID: 20073160
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20073700
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
 

Author Comment

by:ljcor
ID: 20074017
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 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