Access Validation Check

I have a form called [Order Entry Control]. One of the fields on this form is the [DeliveryDate] which is pulled from the contract.

I have another form called [Order Form]. One of the fields on this form is called [OrderDate] and this field defaults to the [DeliveryDate]. Another field [CheckLate] is a check box, which the user selects if the order is past the [DeliveryDate]. Both of these can be modified by the user, but if the [OrderDate] is different than the [DeliveryDate] than the [CheckLate] box must be -1.

Both of these forms are open at the same time. The spaces in the form names are correct and I know it is bad naming practice but I am picking up the ball from someone else.

I want to do a afterupdate VBA on [OrderDate] that checks to see if the user has changed the date [so check to see if [DeliveryDate]<>[OrderDate). If they don't equal I want to ask user, "Is this a late order?" If yes then I want to check the [CheckLate] box in the VBA script. If no I want to tell user "Order date can only be changed for Late Orders" and then undo their date change.

I have researched and I just can't figure it out! Way to complex for my level
Who is Participating?
This is nothing to do with validation checks.  Validation checks PREVENT you entering certain data values.  That is not what you are doing.  You want to DETECT the value.

I can't tell you how to do this in a macro - you won't find many people here who can.
And if you require a macro then you must post your Access version number as things have changed dramatically in recent years.

However, whether you use a a macro or VBA code you must attach it to the afterupdate event of the OrderDate textbox.

In a code solution you would set the textbox's afterupdate event property to [Event Procedure] and then click the Build button (...) to the right, to get into the relevant code procedure.
The code would be..

if me.[Orderdate] > forms![Order Entry Control]![DeliveryDate] then
me.CheckLate= true
me.CheckLate = false
end if

It ia possible that it may be necessary to format the date values , but check th this code first.  Obviously, you must change any form and field names to your own names, if necessary.
We need a sample database demonstrating the issue with enough records to get the results. Supply the instructions to process the record and the expected output.
If the order date is set to a value later than the delivery date, is it still possible for it NOT to be a Late order?
If it must be a late order then I don't see the value of asking the user the question.  Just set the checkbox.

It seems to me though that you would also need to unset the checkbox if the order date was found to be same as the delivery date.
mbbriceAuthor Commented:

You logic makes sense. How do I make a validation statement that compares two dates on separate forms to see if they are equal? If they match is runs a set value macro to make sure late box is unchecked. if false it runs a set value macro to check the box?

Form one value is [Order Entry Control].[DelieveryDate]
Form two value is [Order Form].[OrderDate]

Macro if true [checkboxtrue]
macro if false [checkboxfalse]
mbbriceAuthor Commented:

Thanks for educating me on the terminology. The code wasn't as complicated as I thought it would be.

You did a great job explaining the steps to set it up also. I will have to keep your name in mind for future questions.

You seem like you would be good teacher.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.