DSum & Converting Query Codes to VBA

Question

Query

Refer link above for picture.

Hi. I want to have a Running_Total to calculate the Total_Good_Count by their product. I have successfully created it using Query. However that is not exactly I want.

For example, for RecordID 3, the Running_Total will be 684. RecordID 4, the Running_Total will be 684 + 964 = 1,648. Is it possible?

These are the current codes in the Query:

Running_Total:DSum("[Total_Good_Count]","2_WIP","[Product] = '"&[Product]&"'")

Total_WIP:[Running_Total]-[WIP_Cleared]

Additionally, how can I change these codes to VBA? I actually tried DSum in Query because most solutions online provided are in Query. The VBA Codes will then be in a button of a form.

Answer 1

Try with:

Running_Total: DSum("[Total_Good_Count]","2_WIP","[Product]='" & [Product] & "' And [RecordID]<=" & [RecordID] & "")

and:

Total_WIP: DSum("[Total_Good_Count]","2_WIP","[Product]='" & [Product] & "' And [RecordID]<=" & [RecordID] & "")-WIP_Cleared