Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Validation problems...

    Hi

    I am having some difficulty adding some validation rules to a few fields in a form and would be very grateful for any help/advice.

    Here is what I am trying to achieve:-

    Test Start Date


    >= Project Start Date
    AND <= Project Finish Date
    AND Cannot be Null when Test Status (list box) equals either "In Progress", "Incident" or "Complete"

    Planned Completion Date
    >= Test Start Date
    AND <= Project Finish Date
    AND Cannot be Null when Test Start Date Is Not Null

    Completion Date
    >= Test Start Date
    AND Cannot be Null when Test Status (list box) equals "Complete"


    I'm ok with ensuring the dates fall within the correct ranges, but I get lost when trying to add the remaining rules. I'm working in Access 2000.

    Thanks in advance.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Does this need to be implemented in a query? In code? How is it being used?

    Also, have a look at the IsNull() function to get you started.
    Me.Geek = True

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Is this form unbound or bound back to data via a query?

    If it's unbound, it might be easier to implement due to the fact that there are a number of conditions which need to be checked.

    Are you able to post a .zip of your database perhaps?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My bet is that he's trying to do it via tables and needs to hear this:

    You cannot make references to other fields in the Validation Rule property at the table level. You would have to validate the data in a form to do those validations.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2009
    Posts
    3
    No I am doing it via a form.

    My first problem was that the 'project' controls were from a sub form within the main form, and I was having trouble referencing them in VB. The solution was creating unbound controls in the form, then linking them to the controls within the sub form.

    The second problem was that I wasn't using the Case clause to define the values from my list box, ie

    Select case Me.LstTestStatus
    Case "In Progress", "Incident", "Complete"
    If IsNull(Me.TestStartDate) Then

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "The solution was creating unbound controls in the form"...

    Here is a word doc which describes how to reference controls on a subform;

    http://www.access-programmers.co.uk/...1&d=1140795587

    As ST says, you have to do this at 'form' level - Given the number of conditions you may have to prohibit the user from 'leaving' the form (or the record they are on) if the conditions are not met (I'm guessing the form is bound back to a query as it's recordsource?)
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •