Using another field as a validation rule

[wisgary]

I have an Order-items many-to-many relationship, and my Order-Items
table has a "quantity" field which specifies how many of X item in each
order was ordered. Also, my Items table has an "inventoryQuantity" I
was wondering if there is any way to validate it so that the quantity
field can never be larger than the inventory quantity (otherwise I'd
get a negative inventoryQuantity there as soon as enough stock was
sold)




Also another little question, is it possible to stop forms from auto
saving and just make a save button so that you have the option of
canceling out of a form without saving?

 

[Phil Hunt]

To your little question, you can program a OnUpdate event on the form. You
can use the wizard to find out how the code works.

 

[John Vinson]

I have an Order-items many-to-many relationship, and my Order-Items
table has a "quantity" field which specifies how many of X item in each
order was ordered. Also, my Items table has an "inventoryQuantity" I
was wondering if there is any way to validate it so that the quantity
field can never be larger than the inventory quantity (otherwise I'd
get a negative inventoryQuantity there as soon as enough stock was
sold)

You cannot reference other fields with field validation, so the direct
answer is No. The better way to handle this is to have the Form which
enters the orders do the checking, say in the BeforeUpdate event of
the quantity textbox; warn the user and cancel if the order would
deplete your inventory:

Private Sub txtQuantity_BeforeUpdate(Cancel as Integer)
If DLookUp("[InventoryQuantity]", "[Inventory]", "[ItemID] = " & _
[ItemID]) < Me!txtQuantity THEN
MsgBox "Insufficient items on inventory", vbOKOnly
Cancel = True
End If
End Sub

Also another little question, is it possible to stop forms from auto
saving and just make a save button so that you have the option of
canceling out of a form without saving?

With a bit of code, yes; for instance, have a global variable by
putting

Dim bOKToClose As Boolean

in the Form's Module above any of the Sub lines. Set it to FALSE in
the form's Current event, to True in the close button's Click event,
and check its value in the Form's BeforeUpdate event.

John W. Vinson[MVP]