Multiple Validation Rules in a form

[GothardTech]

Thanks in advance for the assistance.


I have Access 2003 and 2007. I am comfortable with 2003, but this is my first attempt with 2007.

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.

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.

[NTC]

Not simple. will block it out for you though:

1. You have the input form.

2. Have a query of the table that uses the input form's field as it's criteria.

so if there was any duplicate already in the table - running this query returns that record.

3. Execute a DCount function of this query. If >=1 then you have a duplicate - - hold that thought....

4. Make a pop up form sourced on this query you made.....now go back to step 3 and if >=1 then trigger open of the pop up form....

hope this helps you get back on track.