access form continuous (1 Viewer)

[sekoma]

i have access continuous form with 2 fields i want calculate sum like this ((first record when i type data1 field1 its =data1 field2 and second record data2 field2 =data1 field2 +data2 field1 ,third record data3 field2 =data2 field2 +data3 field1 and it courteous automatically how to do this in access form

regards

 

[strive4peace]

hi @sekoma

Reports have a Running Sum property so it's easy there ...

Choices are

• No
• Over Group
• Over All

To show a running sum on a form, you need to do a calculation. Easiest would be to use DSum. The problem though, is what if the form is sorted differently? Or there is criteria to consider? Showing a running sum on a form is not so easy.

 

[Pat Hartman]

Calculations like this are far more efficient when performed by a report which is why Crystal recommended a report. When you do it in a query, it is very expensive (read slow) if the recordset is large. Plus, the recordset will not be updateable and so the form will not be updateable.

So, how important is this to you and do you really need it in your form?

There are samples here that show how to create a running sum in a query.

 

[sekoma]

Thanks all
i need this calculation in form because i have data base it contains combo box it linked with subform the subform its view as worksheet ,so in sub form i have amount field and balance when i type a mount it be calculate in balance like excel sheet

 

[GPGeorge]

Thanks all
i need this calculation in form because i have data base it contains combo box it linked with subform the subform its view as worksheet ,so in sub form i have amount field and balance when i type a mount it be calculate in balance like excel sheet

Did you know you can use a report in a subform control?

 

[CJ_London]

access does not work like excel

so for the second row you would be adding the first and second row, for the third row you would be adding all the records between first and third rows, the next first and fourth, etc

if your subform is not being edited and is just for display you can use a report as a subform

 

[Pat Hartman]

There is a difference between a balance and a running sum.

If you want a balance, add a control to the footer of the subform and calculate the balance there:

=Sum(YourAmtField)

 

[sekoma]

thanks for replay
i know create reporting for sub form, and the subform it be add or edit data. so i want record by record give me balance foe exam: for first record i add amount 5 $ it equal balance for second record when i add amount 10 $ the balance field =5+10=15 and third add amount 2$ the balance =15+2=17 thats like the attach photo
regards

 

[sekoma]

I mean like this attach photo I taken a pic from other data base

 

[Pat Hartman]

If you want a running sum, here are two options.

Create a running totals query - Office | Microsoft Docs

These will be fine as long as your query doesn't return a lot of rows. Using domain functions in queries or loops is very time consuming since each domain function runs a separate query. So, if you have 1000 rows in the query, each domain function will run 1000 times.

 

[CJ_London]

why not look at how the other database has done it?

 

[Pat Hartman]

That would be too easy

 

[sekoma]

Thx all for replay

pat hartman how is it too easy can you explain how i can do this with whitch way



regards

 

[Gasman]

Now crossposted at https://www.accessforums.net/showthread.php?t=86215

 

[Pat Hartman]

Open the other form and see what the running sum control is bound to and follow that thread. Or, read the articles I linked to and try to implement on of those solutions.

 

[sekoma]

Open the other form and see what the running sum control is bound to and follow that thread. Or, read the articles I linked to and try to implement on of those solutions.

im using the dsum in vba
BALANCE = DSum("[Amount]", "[QDRDE]", "[INVONO]=" & Me.INVONO)
but i see all balance calculated for all account , so thats I want when i change Unbound cbo.MRC in the main form the subform updated and the balance must calculate only for this account that's show in cbo.mrc the attach photo that explained what i want and thats access i created
regards

 

[arnelgp]

add an Autonumber field to your Table.
now add this autonumber field to your continuous form,
you can then get the Balance (I used ID as autonumber)

=DSum("[Amount]", "[QDRDE]", "[INVONO]=" & Me.INVONO & " And ID <= " Me!ID)

 

[CJ_London]

think the code you have posted is not the code you are using

you posted "[INVONO]=" & Me.INVONO

think you are actually using "[INVONO]<=" & Me.INVONO

which is correct

but looks like you need another criteria

"[INVONO]<=" & Me.INVONO & " AND [Account]=" & Me!Account

 

[sekoma]

think the code you have posted is not the code you are using

you posted "[INVONO]=" & Me.INVONO

think you are actually using "[INVONO]<=" & Me.INVONO

which is correct

but looks like you need another criteria

"[INVONO]<=" & Me.INVONO & " AND [Account]=" & Me!Account

thanks
i understand at your information and it solved correctly Thank you