VBA problem in access

[ALESI]

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.

[ADezii]

Does the [PartNumber] Fieled play any Role in the Logic, or is it just the Consecutive Totalling of the Amounts?

[ALESI]

@ADezii
Well, the partnumber is linked to the amounts.And has to be kept linked. Maybe i can do something with subtotals i guess.

[ADezii]

@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.

[ALESI]

@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.

[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:
   

   Public glngSubTotal As Long

2. Execute Code that RESETS the Public variable, then Opens a Query:
   

   'RESET for each Query Execution
   glngSubTotal = 0
    
   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:
   

   SELECT tblInventory.partnumber, tblInventory.amount, fCalcSubTotals([amount]) AS subtotal
   FROM tblInventory
   ORDER BY tblInventory.amount DESC;

4. Function Definition:
   

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

5. View the Results:
   

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

[ALESI]

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.

[ADezii]

I see no Declaration in a Standard Code Module for the Public Variable:

Public glngSubTotal As Long

[ALESI]

Sorry,

But what should i declare between the brackets ()

Public glngSubTotal() As Long

Should it be public or public function?

[ADezii]

The Declaration is exactly as what was depicted in Post #8.

[ALESI]

@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.

[ADezii]

When I get the chance, I'll send you a Demo showing how it should work.

[ALESI]

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.

[Rabbit]

If this is for reporting purposes, why not use a running sum?

[ALESI]

I am really a novice in Access... this is a query....

how do i use a runnning sum?

[ADezii]

In the Text Box of a Report.

[ALESI]

@ 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...

[ADezii]

Looks like a Typo:

glngSubTotal = 0