Validation to Prevent Duplicates

[Roly Reefer]

Hi,

I have created a database that monitors containers that are being hired. When a container is hired some details are entered and remains the same until we receive an invoice to state that the hire has stopped. Subsequently, the database is updated and an invoice sent. The container is then hired out again and some details are entered on the database and so on.
There are two check boxes to state whether the container hire has been completed and whether the client has been invoiced.

I am attempting to stop duplicate entries of container numbers being entered onto the database when the transactions are not complete and the client has not been invoiced.

I cannot use a primary key because the containers are reused and hence the container number will be on the field more than once. I have also tried a find duplicates query, but this found duplicates in the whole table and returned only the duplicates that were in the criteria I had specified.

Basically, I want the validation or whatever to prevent duplicates only when the two check boxes are 0.

Thanks in anticipation for your help.

All the best,


Roly

 

[ted.martin]

It seems to me that your problem arises because your Container Number is not a unique record. If it is not too comples to do, why not re-design the database with each container in a table where each reference is indeed a primary key and then have a one-to-many subform for all the transactions for each hiring. You can then have your two check boxes on the main form and as soon as anyone goes into that container record to add a new entry, if both check boxes are ticked, you can create a Message Box error. Hope this helps. Its all in the design really and forethought for error trapping.

 

[Len Boorman]

If you are using a form to register the hire of a container then you could think of using a combo box as the selection mode for the container serial number. The row source of this combo box would be those containers where the Hire Completed field was not null. Use distinct to avoid duplicates.

Or something similar

L