I have a form with the fields "DateShipped" and "Location" on it. Location will (soon) be a list box, so that only one item can be in each location. I would like to change the requirements so that Location is required only if there is nothing in the DateShipped field. AKA, if something has already left the warehouse it doesn't need a location anymore, that location is free for something else to come in. But I need to make sure that items always have a location on them if they're in the warehouse.
Is there a way to make Location conditionally required? And is there a better way of making sure that each location is used only by one item at a time rather than just making a list box and indexing the field?
This rule can (and should) be applied at the table level (EDIT - not contradicting Paul - I think if he had known it could be done there he would have recommended it so).
1) Table Design view -> Properties
2) Paste the below in "Validation Rule":
[DateShipped] Is Null And [Location] Is Not Null Or [DateShipped] Is Not Null
This means that Location can be NULL or a value if there is a DateShipped. If there is no dateshipped then it must not be NULL.
Originally Posted by mwhcrew
And is there a better way of making sure that each location is used only by one item at a time rather than just making a list box and indexing the field?
The Listbox won't ensure the location is only used once, but it will help the UI. The best way to ensure that a column value is unique is to use a unique index (index with No Duplicates in Access terminology).
This is great, thanks so much! One more question - is it possible to delete the value in the location field when the date shipped field is updated? I tried putting this code in the AfterUpdate property:
Me.Location = Null
But don't think I have the right wording or syntax. Thanks!