Validation Rule

[Haytham]

Hi All,
My question is bit easy.
In a form,I have a field named: Quantity, that is to be sold.
I want to sell <= the quantity I have in my store. I put the Validation rule as follows:
<=[FormNameContaining Quantity].[Form]![QuantityField], but it's giving me error that the form name is mislabelled or not exist...
Any solution to this problem
Thanks in advance

 

[The_Doc_Man]

The question is, where did you put this validation rule?

The approach I would take is that I would put some code in the Form's BeforeUpdate routine (which includes a Cancel parameter.)

The code would do a DLookup of the quantity on-hand and do the test in the event routine. If I am trying to sell more than I have, issue an appropriate message using something like a message box.

I'm going to be a bit shorthand with this. It is bare-bones, makes lots of assumptions, has no bells and whistles. (Those are your problems, not mine, after all...)

Sub Form_BeforeUpdate(Cancel as Integer)

Dim QOH as Long

QOH = DLookup( "[OnHand]", "Inventory", "[Item] = """ & Me![ItemNumber] & """" )

if QOH < Me![Quantity] then

MSGBOX "Quantity on hand is " & CStr(QOH) & " which is less than the ordered amount.", vbOKOnly
Cancel = -1

Else

Cancel = 0

End If

End Sub

 

[Haytham]

You are not The Doc Man but the Great Man
Your idea helped me a lot. Very thankful to you. But I was trying to do it through
Properties: Data : Validation Rule
but my form was not loaded and therefore it was giving me error.
Well, may I ask another question please...
If I want to modify a quantity e.g. I have in the ItemNo Field 10 and the quantity in Inventory is 15 , I want to modify my result from 10 to 20 so only 10 is more which is available, but it gives the same MsgBox,
I think I have to play with Refresh Or Requery... Any idea please
Again I'm very thankful to your help