Microsoft Visual Basic Run-time error

2
votes

I'm using MS Access 2007 and VBA. I have a block of code which triggers when the quantity is greater than stock or the stock is zero.

Private Sub Quantity_BeforeUpdate(Cancel As Integer)
    If Me.Quantity > Me.Stock Or Me.Stock = 0 Then
        MsgBox "Not enough stocks left."
        Me.Quantity = ""
    End If
End Sub

The message box appears but after that an error message followed:

Run-time error '2147352567 (80020009)':

The macro or function set to BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field.

How to stop this error?

2
ms-accessvba

2 Answers

2
votes

A control's Before Update event does not allow you to change it to just any value. You have 2 options at that point:

  1. call Cancel and then call Undo to revert its value to whatever was there before the attempted update
  2. call Cancel only, leaving the new (unacceptable) value in place, but forcing the user to replace it with an acceptable value before continuing

Here is tested sample code for the first option. If you prefer the second option, discard the Me.Quantity.Undo line.

Private Sub Quantity_BeforeUpdate(Cancel As Integer)
    If Me.Quantity > Me.Stock Or Me.Stock = 0 Then
        MsgBox "Not enough stocks left."
        'Me.Quantity = "" ' <-- NOT ALLOWED
        Cancel = True
        Me.Quantity.Undo
    End If
End Sub

When Me.Quantity and Me.Stock are text values which contain numbers, the situation is more complicated. If it were me, I would prefer to change their data types to numeric. But if that change is impractical, you can use Val() to transform the text values to actual numbers for your comparison.

Private Sub Quantity_BeforeUpdate(Cancel As Integer)
    If Val(Me.Quantity) > Val(Me.Stock) Or Val(Me.Stock) = 0 Then
        MsgBox "Not enough stocks left."
        'Me.Quantity = "" ' <-- NOT ALLOWED
        Cancel = True
        Me.Quantity.Undo
    End If
End Sub
1
votes

In MS Access you can't set control values in the "BeforeUpdate" event. So this line: 

Me.Quantity = ""

Is causing your error message. So you will need to look for an alternative way to do what you are attempting to do. I think that the "AfterUpdate" event will work for what you are looking for.