Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: Multiple Validation Rules in a form

    Thanks in advance for the assistance.
    I have Access 2003, 2007 and 2010. I am comfortable with 2003, but this is my first attempt with 2010.

    I am working on an inventory system for a vehicle storage lot. Cars are delivered by Carrier, and offloaded to a Parking Location. Simple enough, right?

    VehicleMaster table:
    VIN (17 characters)
    Model
    ManifestDate
    InboundCarrier
    DateReceived
    ParkingLocation

    There is a spreadsheet imported ahead of time with Carrier, Manifest Date, Model and VIN (17 characters).

    The yard personnel write down the last 6 characters of the VIN (SVIN) and the Parking Location out on the lot, then bring the list in to receive the cars.

    I want a data entry form where they key the SVIN (last 6 of VIN) and ParkingLocation.

    Upon entering the SVIN, I need the form to immediately check that the car has not already been received, and say so if it has. (Avoid duplicate keying).

    I need to verify that the SVIN actually matches one of the VINs already imported from the manifest.

    In very limited cases, there may be a duplicate SVIN, in which case I need a pop up that lets them choose the right one.

    After the entry validates, it should update the appropriate record in the VehicleMaster table with the DateReceived and ParkingLocation.

    I have made several false starts on this. I finally pulled a list of SVINs and used that in a temp table as a Lookup with Limit to List set to Yes. That solved the "must exist from manifest" problem, but not the other two.

    It seemed very simple when I first started looking at it, but I have lost my vision. I need a fresh perspective. Any help will be much appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Use a dcount() to count the number of SVIN in the database
    If there are more than one get it to open a forum with a list box in showing what you want them when the use click it it would close and populate the missing data
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Thanks, StePhan. I tried your dcount recommendation, but I could never get the syntax right. I brought in a local programmer and he is going to fix that part for me.

    I really appreciate the help!

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    here
    =Dcount("*","TableName","feildname ='" & me.screenfeildname & "'")

    or in english

    =Dcount(countwhat,whattable,wherethisfeild=bla)
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Tags for this Thread

Posting Permissions

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