Updatiing a number field from a form.

[Robbyp2001]

Good Morning everyone

I wonder if anyone will be kind enough to help with this problem.

I have a form [AddNewStock] which is based on a table [ItemBalance]. [ItemBalance] holds information on each unique item, includimg [ItemID], [Name] and the numbers in stock [Total]. The form shows [ItemID], [Name], [Price] and [Total]. There is also a box called [NewStock] which is a number field. This box is for the user to enter the number of new delivery items for each Item.

What I am trying to achieve is, when the user enters a number in [AddNewStock]![NewStock] an AfterUpdate event will add this to the existing balance in [ItemBalance]![Total] and new total displayed in real time in [AddNewStock]![Total], so that the user can feel confident that the process has been done successfully.

I have tried a number of different ways to do this including appending the [NewStock] number to a temporary table where the new balance is calculated and updated back to [ItemBalance]![Total].

Although I can get it to work, it seems a rather clumsy way to simply add a number to another number in a different table. I'm sure some of the code wizards on here will have a simpler way of achieving this.

Any suggestions?

Thanks folks

 

[arnelgp]

on AfterUpdate Event of NewStock control:

Private Sub NewStock_AfterUpdate()
Me![Total] = Me![Total] + Me![NewStock]
End Sub

 

[Robbyp2001]

Fantastic Arnel. Works perfectly. Thank you so much. There is something that I forgot to mention and that is, once the process has finished and the number in [Total] has changed, the number in the [NewStock] should go blank. As it is the number remains there even after the form is closed then reopened.

 

[arnelgp]

you can blank it like this:

Private Sub NewStock_AfterUpdate()
Me![Total] = Nz(Me![Total],0) + Me![NewStock]
Me![NewStock]=Null
End Sub


..but, since the control is already blank, there
is no telling what amount coz the total to change.

so, i would like to keep it first:

Private Sub NewStock_AfterUpdate()
Me![Total] = Nz(Me![Total],0) + Me![NewStock]
End Sub

..then blank it on the Form's AfterUpdate:

Private Sub Form_AfterUpdate()
Me![NewStock]=Null
End Sub

..anyway, its your preference.

 

[Robbyp2001]

Both versions seem to work well. The [total] is updated successfully and the [NewStock] goes blank. Strangely though, I cannot move to another record (it is a continuous form) to repeat the process, unless I exit the form and reopen it. Is there a way around this as the user would in all probability wish to update several different items in one session. I should have mentioned that is is a continuous form. Sorry.

 

[arnelgp]

if this is continuous form, do it in the Form's AfterUpdate Event:


Private Sub Form_AfterUpdate()
Me![Total] = Nz(Me![Total],0) + Me![NewStock]
Me![NewStock]=Null
End Sub

 

[Robbyp2001]

Unfortunately, this sets the [total] to null and I'm still locked into the record on the form.

 

[arnelgp]

check your form property, specially AllowAdditions, AllowEdits, and set them to yes.

 

[Robbyp2001]

That's it! Well done Arnel. Many thanks.