Set default value with a running total

[jacc14]

I have a main form with a quantity value and a continuous subform where i enter my line by line transactions.

I want to set my default value for the quantity in the subform so it looks at the quantity in the main form and reduces each time i add a transaction eg if my main form quantity is 100 and in the subform i enter that i have used 80 then the default value for my next line is 20 and so on. Hope this makes sense.

I tried....defaul tvalue =

 [forms]![frm_header]![quantity] - (sum([quantity])

but it doesnt accept this.

thanks
Christine.

[gnawoncents]

Try something like this:

I have two forms:
- [Form1] which is my main form, drawing its data from its own table
- [Form2] a subform on Form1 with continuous records view set

Form1 has two text boxes: [TotalItems] and [Used]
Form2 has two text boxes per record: [OrderQuantity] and [Remaining]
The "Remaining" text box displays the items remaining after a new value is placed in the "OrderQuant ity" box. In order to update the amount, use the code below in the AfterUpdate event field of the OrderQuantity box.

Private Sub OrderQuantity_AfterUpdate()

[Forms]![Form1]![Used] = [Forms]![Form1]![Used] + Me.OrderQuantity
Me.Remaining.DefaultValue = [Forms]![Form1]![TotalItems] - [Forms]![Form1]![Used]
Me.Remaining = [Forms]![Form1]![TotalItems] - [Forms]![Form1]![Used]

End Sub

the second line starting with Me.Remaining can be omitted if you don't care to keep a historical record of items remaining. If you chose this option you can also leave the "Remaining" box unbound.

[gnawoncents]

I forgot to mention, you'll also want to set the default value in the remaining box so it calculates when the form is opened. You could use the following:

=[Forms]![Form1]![TotalItems]-[Forms]![Form1]![Used]

[jacc14]

Try something like this:

I have two forms:
- [Form1] which is my main form, drawing its data from its own table
- [Form2] a subform on Form1 with continuous records view set

Form1 has two text boxes: [TotalItems] and [Used]
Form2 has two text boxes per record: [OrderQuantity] and [Remaining]
The "Remaining" text box displays the items remaining after a new value is placed in the "OrderQuant ity" box. In order to update the amount, use the code below in the AfterUpdate event field of the OrderQuantity box.

Private Sub OrderQuantity_AfterUpdate()

[Forms]![Form1]![Used] = [Forms]![Form1]![Used] + Me.OrderQuantity
Me.Remaining.DefaultValue = [Forms]![Form1]![TotalItems] - [Forms]![Form1]![Used]
Me.Remaining = [Forms]![Form1]![TotalItems] - [Forms]![Form1]![Used]

End Sub

the second line starting with Me.Remaining can be omitted if you don't care to keep a historical record of items remaining. If you chose this option you can also leave the "Remaining" box unbound.

Hi there.
thank you for taking the time to help me.

All works ok but i feel the user will hit problems if they start amending the quantities as it throws in strange figures as it is adding the figures as it goes along and i cant be sure the user wont go back and amend any of the values.

Christine.

[gnawoncents]

Yes, you're correct. If users can edit historical data this approach won't be the best. Perhaps you can give me a more complete picture of how the user will be using the value so I can offer a better solution.

[jacc14]

Yes, you're correct. If users can edit historical data this approach won't be the best. Perhaps you can give me a more complete picture of how the user will be using the value so I can offer a better solution.

Hi. I basically need it to recalculate the running total as it gets to a new line rather than store as I cant guarantee the users accuracy. I liked your idea though.

[gnawoncents]

If you want the running total to be the same no matter what record they are on, just input your version of this code in the "Remaining" Control Source box:

=[Forms]![Form1]![TotalItems]-nz(Sum([OrderQuantity]),0)

That will update all the records with the current quantity remaining no matter which record is modified. It will update once the user moves to a new record.

Please let me know if this still isn't quite what you're looking for.

[jacc14]

Thanks for this.