How to enforce field inter-dependant validation rules?

[svdoerga]

I'm making an access database to track returned goods, Access 2007. one of the tables is like this (simplified):

Item(ItemID, SerialNr, Quantity, Description)

All kinds of items are returned, some have serial numbers and some don't (stuff like screws, plastic encasings, etc). When an item has a serial number it implies it is a unique item, so if "SerialNr" is filled in, quantity has to be 1.

This rule applies for SerialNr:
if Quantity>1 then SerialNr has to be empty

This rule applies for Quantity:
if SerialNr is filled in then Quantity has to be 1

Can anyone tell me if and how I can enforce these rules in table design?

I understand a proper solution would be to split it into 2 tables:

Item(ItemID, SerialNr, Description)
ItemRecieved(ItemID, Quantity)

This database is a quick 'n dirty solution to fill a hole in our ERP system. It should be very simple and contain as little data as possible. Thats why both quantity and serialnr are in the same table. Also that avoids having to maintain a table with item information.

 

[MajP]

Put the rule in the before update event of the data entry form and/or the before update of the serialNr control and the quantity cntrl. Pop up a small dialog form with the options to set the quantity to 1 or remove the serial number.

 

[svdoerga]

I got it working on the data entry form. Thnx for your reply MajP.