By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,480 Members | 1,676 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,480 IT Pros & Developers. It's quick & easy.

VBA problem in access

P: 9
I am starting to use access and i have reached the maximum of my knowledge of access 2003.
The problem is that i need a custom VBA to build a custom function for my access application.

I need to do a calculation, i have a query which contains partnumbers and amounts on stock.
Now i have these amounts sort by highest value to lowest value.

What i need in the next field is:

in the first row of the the table; the sum of the first highest value
in the second row of the table ; the sum of the first 2 highest values
in the third row of the table ; the sum of the first 3 highest values
and so on and so on

Now i was thinking, maybe i can put an id next to the amounts and than do something like sumif in excell.
But there must be an easier way.

I hope you can help me.
Nov 5 '11 #1

✓ answered by ADezii

There definitely has to be a more efficient, SQL-based approach, but for now it is alluding me. The following Logic will work until the SQL Gang comes through with a better solution.
  1. Declare a Public Variable in a Standard Code Module to keep track of the Cumulative Sub-Totals:
    Expand|Select|Wrap|Line Numbers
    1. Public glngSubTotal As Long
  2. Execute Code that RESETS the Public variable, then Opens a Query:
    Expand|Select|Wrap|Line Numbers
    1. 'RESET for each Query Execution
    2. glngSubTotal = 0
    3.  
    4. DoCmd.OpenQuery "qryInventory", acViewNormal, acReadOnly
  3. Generate the Sub-Totals in a Calculated Field in a Query that calls a Function to do the dirty work:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblInventory.partnumber, tblInventory.amount, fCalcSubTotals([amount]) AS subtotal
    2. FROM tblInventory
    3. ORDER BY tblInventory.amount DESC;
  4. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcSubtotals(lngAmount As Long) As Long
    2. glngSubTotal = glngSubTotal + lngAmount
    3.  
    4. fCalcSubtotals = glngSubTotal
    5. End Function
  5. View the Results:
    Expand|Select|Wrap|Line Numbers
    1. partnumber    amount    subtotal
    2. 0000098491628    455     455
    3. 0000016508467    405     860
    4. 0000004861936    342    1202
    5. 0000018118690    342    1544
    6. 0000500326896    339    1883
    7. 0000500339745    333    2216
    8. 0000002992300    307    2523
    9. 0000042099076    300    2823
    10. 0000002994057    277    3100
    11.  

Share this Question
Share on Google+
17 Replies


ADezii
Expert 5K+
P: 8,679
Does the [PartNumber] Fieled play any Role in the Logic, or is it just the Consecutive Totalling of the Amounts?
Nov 5 '11 #2

P: 9
@ADezii
Well, the partnumber is linked to the amounts.And has to be kept linked. Maybe i can do something with subtotals i guess.
Nov 5 '11 #3

ADezii
Expert 5K+
P: 8,679
@ALESI:
You are going to have to Post some Data as well as the Desired results, so that we can fully comprehend what you are requesting.
Nov 5 '11 #4

P: 9
@ADezii
partnumber amount subtotal
0000098491628---455---455
0000016508467---405---860
0000018118690---342---1202
0000004861936---342---1544
0000500326896---339---1883
0000500339745---333---2216
0000002992300---307---2523
0000042099076---300---2823
0000002994057---277---3100

These first 2 columns i have, now the calculation i need is for the subtotals.
The amounts are sorted descending.
Nov 5 '11 #5

ADezii
Expert 5K+
P: 8,679
There definitely has to be a more efficient, SQL-based approach, but for now it is alluding me. The following Logic will work until the SQL Gang comes through with a better solution.
  1. Declare a Public Variable in a Standard Code Module to keep track of the Cumulative Sub-Totals:
    Expand|Select|Wrap|Line Numbers
    1. Public glngSubTotal As Long
  2. Execute Code that RESETS the Public variable, then Opens a Query:
    Expand|Select|Wrap|Line Numbers
    1. 'RESET for each Query Execution
    2. glngSubTotal = 0
    3.  
    4. DoCmd.OpenQuery "qryInventory", acViewNormal, acReadOnly
  3. Generate the Sub-Totals in a Calculated Field in a Query that calls a Function to do the dirty work:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblInventory.partnumber, tblInventory.amount, fCalcSubTotals([amount]) AS subtotal
    2. FROM tblInventory
    3. ORDER BY tblInventory.amount DESC;
  4. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcSubtotals(lngAmount As Long) As Long
    2. glngSubTotal = glngSubTotal + lngAmount
    3.  
    4. fCalcSubtotals = glngSubTotal
    5. End Function
  5. View the Results:
    Expand|Select|Wrap|Line Numbers
    1. partnumber    amount    subtotal
    2. 0000098491628    455     455
    3. 0000016508467    405     860
    4. 0000004861936    342    1202
    5. 0000018118690    342    1544
    6. 0000500326896    339    1883
    7. 0000500339745    333    2216
    8. 0000002992300    307    2523
    9. 0000042099076    300    2823
    10. 0000002994057    277    3100
    11.  
Nov 5 '11 #6

P: 9
Thank you ADezii,

But i can't get it to work...:(

This is what i have put in 1 module in VBA:

Public Function glngSubTotal() As Long

glngSubTotal = 0

DoCmd.OpenQuery "abc bepaling calculatie", acViewNormal, acReadOnly
End Function

Public Function fCalcSubtotals(lngAmount As Long) As Long
glngSubTotal = glngSubTotal + lngAmount

fCalcSubtotals = glngSubTotal
End Function

And this is in my SQL:

SELECT [abc bepaling aantallen].abondn, [abc bepaling aantallen].abantl, fCalcSubTotals([abc bepaling aantallen].[abantl]) AS subtotal
FROM [abc bepaling aantallen]
ORDER BY [abc bepaling aantallen].abantl DESC;

But than it gives a compile error in VBA:

Public Function fCalcSubtotals(lngAmount As Long) As Long
glngSubTotal = glngSubTotal + lngAmount

glngSubTotal is marked blue and it says:
function sign on the left side of the commitment must give a variant or object as result.

Am i doing something wrong?

Thanks for your help so far.I am getting in the right direction.
Nov 5 '11 #7

ADezii
Expert 5K+
P: 8,679
I see no Declaration in a Standard Code Module for the Public Variable:
Expand|Select|Wrap|Line Numbers
  1. Public glngSubTotal As Long
Nov 5 '11 #8

P: 9
Sorry,

But what should i declare between the brackets ()

Public glngSubTotal() As Long

Should it be public or public function?
Nov 6 '11 #9

ADezii
Expert 5K+
P: 8,679
The Declaration is exactly as what was depicted in Post #8.
Nov 6 '11 #10

P: 9
@ADezii

I choose this as the best help from this site, but untill now it does not work yet. My query gives as result exactly the same as what is in the column amount.
Nov 7 '11 #11

ADezii
Expert 5K+
P: 8,679
When I get the chance, I'll send you a Demo showing how it should work.
Nov 7 '11 #12

P: 9
Ok, great!

Well i have got it to work now, sort of. the glng doesnt seem to work.
I have it working now with lng in the function.
The only thing now is that it doesnt reset to zero.
Nov 7 '11 #13

Rabbit
Expert Mod 10K+
P: 12,421
If this is for reporting purposes, why not use a running sum?
Nov 7 '11 #14

P: 9
I am really a novice in Access... this is a query....

how do i use a runnning sum?
Nov 7 '11 #15

ADezii
Expert 5K+
P: 8,679
In the Text Box of a Report.
Nov 7 '11 #16

P: 9
@ ADezii
No i really need this in a Query... I have got it to work now.
The only thing it does not is the reset:

Public Function ResetSubtotals()

'RESET for each Query Execution
lngSubTotal = 0
DoCmd.OpenQuery "abcaantcalc", acViewNormal, acReadOnly

End Function

What is wrong in this code...
Nov 8 '11 #17

ADezii
Expert 5K+
P: 8,679
Looks like a Typo:
Expand|Select|Wrap|Line Numbers
  1. glngSubTotal = 0
Nov 8 '11 #18

Post your reply

Sign in to post your reply or Sign up for a free account.