Validate a field depending on value in another field

[techtamer]

Hello, I have a form that includes fields "Status" and "Date Out". "Status" is either IN or OUT and "Date Out" contains a DATE. I want the form to check that if the "Status" is set to IN that the 'Date Out" field is blank. If the "Date Out" field is not blank a message should pop up to remind the user to delete the date. I'm still learning Access and am not sure how to achieve this and hope someone is able to help. Thanks

 

[theDBguy]

Hi. Welcome to the forum. If you’re using a form for data entry, then you can use the AfterUpdate of the control to perform the validation.

 

[Galaxiom]

Hello, I have a form that includes fields "Status" and "Date Out". "Status" is either IN or OUT and "Date Out" contains a DATE. I want the form to check that if the "Status" is set to IN that the 'Date Out" field is blank. If the "Date Out" field is not blank a message should pop up to remind the user to delete the date. Thanks

The Status should not be stored in the table. The presence of a date in DateOut already determines the Status. Information that can be derived from other values in the record should not be stored.

Your form can have the Status if you want but it should quietly delete the DateOut value when set to In.

 

[June7]

So what field update should be used to trigger the message? An UNBOUND checkbox will reflect the same value for all records. Really, Status is not needed. If user is required to do any input to change status, then they should just delete the date. Can have a button for that if it would be friendlier for users.

Exactly what purpose is this db for?

 

[techtamer]

The DB tracks stored pallets and calculates monthly storage charges. I have a button to change status to In and delete out date, but users regularly change the status manually and don't delete the out date. This only has to be done if an incorrect pallet is dispatched and then returned. Was just looking for a way to stop them forgetting to delete the Out Date if they change the status manually. I think 'll just do a bit more training and hope they get it right. The status field is used to calculate fees and charges relating to receiving and dispatching pallets, i see this could be done differently but would be difficult to redesign at this stage.

 

[June7]

What does 'change manually' mean?
If they only interface via the form, the button seems to be all that is needed.
Don't even give them an In/Out check field.

 

[techtamer]

Thanks June7 for the reply. 'change manually' means they click on the status field in the form and change Out to In. I could delete the field from the form but they need to see what the status is in some cases. I hoped I could code something to check these fields but I think I'll just give them more training and encourage them to only use the button to change status