Validate Quantity AfterUpdate not working

[cjman]

The following code does not work. I am using Access 2007 Fe to SQL Server 2005 backend. What am I doing wrong? The message boxes don't work and it allows quantities to be entered greater than the quantiy on hand.

Private Sub Qty_AfterUpdate()
If Me.Qty > [dbo_AssetDescriptionQtyVWDetail].Form![QtyonHand] Then
MsgBox "Insufficient Qty on Hand"
Me.Qty.SetFocus
Else
If Me.Qty = 0 Then
MsgBox "Qty Can Not Equal Zero"
Me.Qty.SetFocus
Else
Me.TotalAmount.SetFocus
End If
End If
End Sub

 

[pbaldy]

Personally I'd use the before update event for validation:

http://www.baldyweb.com/BeforeUpdate.htm

I'd use the form's event rather than the controls, just in case the user mouses past your field.

 

[cjman]

I will move my code to the before update event. Was there a problem in the code?

 

[pbaldy]

I expect you got the message boxes? You test for the quantity being greater than allowed or zero, but just warn the user. There's nothing there that would stop or undo the entry. The before update event is the place to validate data, because the Cancel = True will stop the update. It's already occurred in the after update event.

 

[cjman]

Thank You, That worked.

 

[cjman]

This is the code that worked. Thanks again pbaldy.


Private Sub Qty_BeforeUpdate(Cancel As Integer)
If Me.Qty > [dbo_AssetDescriptionQtyVWDetail].Form![QtyonHand] + 0 Then
MsgBox "Insufficient Qty on Hand"
Cancel = True
Else
If Me.Qty = 0 Or IsNull(Me.Qty) Then
MsgBox "Qty Can Not Equal Zero"
Cancel = True
Else
End If
End If
End Sub

 

[pbaldy]

No problem; glad we got it working for you.