Access Validation Check

Posted on 2011-05-12
Last Modified: 2012-08-13
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
Question by:mbbrice
    LVL 30

    Expert Comment

    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.
    LVL 77

    Expert Comment

    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.

    Author Comment


    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]
    LVL 77

    Accepted Solution

    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.

    Author Closing Comment


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now