Chee Hon said:
Hi all,
hope somebody can help me with this:
I have a table with 2 columns, Product and Qty Purchase.
I want to set a restriction on the Qty Purchase to a maximum of 5 for
one of the product, say "Football". I have no restrictions for other
type of balls such as basketball or volleyball, customers are free to
buy any number.
In short, I want to restrict Qty<5 for only one of the Product.
How do I do that? Do I need to use SQL? Input mask? or Lookup value?
Chee Hon
You can't do this in the table design itself. You have to restrict
users' data entry to a form, so that you can write code in a form event
to enforce this rule. You could hard-code the restriction, but the most
flexible approach would be to store each product's maximum quantity in a
field of the Products table. You could use a value of Null, or
maybe -1, in this MaxQty field to indicate that the product has no
maximum quantity, and you would let that be the default value for the
field. Then on the form, you could have a combo box to select the
product, and the MaxQty field would be included in the combo box's
RowSource to be a hidden column of the combo box.
Suppose the combo box is named cboProduct and it has columns 0 =
ProductID, 1 = ProductName, and 2 = MaxQty. Then you'd have code in the
BeforeUpdate event of Qty that would check against the third column of
the combo box to see if the maximum quantity has been exceeded. You'd
also want code in the *form's* BeforeUpdate event to make sure they
didn't change the product after setting the quantity. The code might
look like this:
'----- start of example code -----
Private Sub Qty_BeforeUpdate(Cancel As Integer)
Dim strMaxQty As String
If IsNull(Me.Qty) Then Exit Sub ' nothing to check
' Get maximum quantity for product (as a string).
strMaxQty = Me.cboProduct.Column(2) & vbNullString
' Check against maximum quantity.
If Len(strMaxQty) > 0 Then
If Me.Qty > Val(strMaxQty) Then
Cancel = True
MsgBox "You've chosen too many of this product. " & _
"Please enter no more than " & strMaxQty & "."
End If
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call Qty_BeforeUpdate(Cancel)
If Cancel = True Then
Me.Qty.SetFocus
End If
End Sub
'----- end of example code -----
