AfterUpdate Code on form (1 Viewer)

Local time
Today, 22:45
Joined
Feb 27, 2022
Messages
49
Hi all,

I have a form with two subforms on it. Both subforms have a "total" summation in the footer section of each form. On the second form, when a user updates the quantity for a line item, I currently have some code on the 'AfterUpdate' property of the 'Quantity' control, which should check to see that the user doesn't over-allocate stock that doesn't exist.

For example, if subform A is showing that 10 Widgets are available, subform B allows the user to distribute those 10 Widgets to many different distribution centers. So let's say 4 was allocated to distribution center 1, then you have the balance of 6 to allocated to other distribution centers. The code is just there to stop someone from accidently allocating say 8 to distribution center 2, after allocated 4 to distribution center 1 (therefore 12 required in this example) when only 10 are available.

I have a 'Total Quantity Allocated' in the footer section of Subform B. This is adding up the line item Quantities. "MaxPackagesAvail" is from subform A. Here's the code under the AfterUpdate Property of "Quantities":

Code:
Private Sub Quantity_AfterUpdate(Cancel As Integer)
Dim MaxPackage As Integer
MaxPackage = Forms!frmCenterOrders!frmNetPurchasedInventory.Form!MaxPackagesAvail
If Me.AllocatedTotal > MaxPackage Then
  MsgBox "You cannot allocate more items than what is available"
  Me.Quantity = Null
End If
End Sub

When happens when running this, if the user allocates stock to different distribution centers, the "AllocatedTotal" isn't recalculating immediately and therefore the IF statement and Messagebox won't fire- allows users to allocated any amount to the distribution centers.

Am I using the wrong Form Property to update the AllocatedTotal field immediately so the code captures overallocated stock or have I done something else wrong?

Thank you for your help.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:15
Joined
May 7, 2009
Messages
19,175
you can Add the Quantity + AllocatedTotal and compare it to MaxPackage.
If Quantity is Not the last control on your subform, you can put the code to it's AfterUpdate.
Code:
Private Sub Quantity_AfterUpdate()
Dim MaxPackage As Integer
MaxPackage = Forms!frmCenterOrders!frmNetPurchasedInventory.Form!MaxPackagesAvail
If (Nz(Me.AllocatedTotal,0) + Nz(Me!Quantity, 0))> MaxPackage Then
  MsgBox "You cannot allocate more items than what is available"
  Me.Quantity = Null
End If
End Sub
Otherwise, use it's Before_Update event:
Code:
Private Sub Quantity_BeforeUpdate(Cancel As Integer)
Dim MaxPackage As Integer
MaxPackage = Forms!frmCenterOrders!frmNetPurchasedInventory.Form!MaxPackagesAvail
Cancel = (Nz(Me.AllocatedTotal,0) + Nz(Me!Quantity, 0))> MaxPackage 
If Cancel Then
  MsgBox "You cannot allocate more items than what is available"
End If
End Sub
 
Local time
Today, 22:45
Joined
Feb 27, 2022
Messages
49
you can Add the Quantity + AllocatedTotal and compare it to MaxPackage.
If Quantity is Not the last control on your subform, you can put the code to it's AfterUpdate.
Code:
Private Sub Quantity_AfterUpdate()
Dim MaxPackage As Integer
MaxPackage = Forms!frmCenterOrders!frmNetPurchasedInventory.Form!MaxPackagesAvail
If (Nz(Me.AllocatedTotal,0) + Nz(Me!Quantity, 0))> MaxPackage Then
  MsgBox "You cannot allocate more items than what is available"
  Me.Quantity = Null
End If
End Sub
Otherwise, use it's Before_Update event:
Code:
Private Sub Quantity_BeforeUpdate(Cancel As Integer)
Dim MaxPackage As Integer
MaxPackage = Forms!frmCenterOrders!frmNetPurchasedInventory.Form!MaxPackagesAvail
Cancel = (Nz(Me.AllocatedTotal,0) + Nz(Me!Quantity, 0))> MaxPackage
If Cancel Then
  MsgBox "You cannot allocate more items than what is available"
End If
End Sub
Super! That works perfectly- I didn't think of adding them and wouldn't have known to add the Nz. Thanks a lot for your help arnelgp and for teaching me something too. Very much appreciated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 19, 2002
Messages
42,981
Rather than using arnelgp's code suggestion for the AfterUpdate event, use the code suggested for the BeforeUpdate event. You CANNOT stop a record from being saved from the AfterUpdate event since there is no cancel argument. ALWAYS use the BeforeUpdate so you can set the cancel property. In this particular case, you will probably get away with using the wrong event. But when you review old code to help you develop new code, you won't remember this advice so best to just use the correct event now and then you'll have good code to rely on in the future.

And for the future, please don't create multiple threads with the same question.
 
Local time
Today, 22:45
Joined
Feb 27, 2022
Messages
49
Rather than using arnelgp's code suggestion for the AfterUpdate event, use the code suggested for the BeforeUpdate event. You CANNOT stop a record from being saved from the AfterUpdate event since there is no cancel argument. ALWAYS use the BeforeUpdate so you can set the cancel property. In this particular case, you will probably get away with using the wrong event. But when you review old code to help you develop new code, you won't remember this advice so best to just use the correct event now and then you'll have good code to rely on in the future.

And for the future, please don't create multiple threads with the same question.
Thanks for clarifying Pat. I have to admit I get confused of which property to use but this makes it clearer.

RE Multiple Threads- I did start to put this question originally in my first thread to save time and then I deleted it thinking that that was a question/thread around how to identify controls of a subform using VBA and this question was completely different and around the Before/After Update property of a form- so I deleted it from there thinking someone will tell me off and thought I was doing the right thing by putting it in a place that I thought was more applicable.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 28, 2001
Messages
27,001
Understood. For future reference, if you wish to move a thread because you realize it is in the wrong place, just include a comment in that thread asking for any moderator to move it to where you think it belongs. That isn't a difficult proposition for any of the mods.
 

Users who are viewing this thread

Top Bottom