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.

It is seeming that the only option will be VBA.

Thanks in anticipation for your help.

All the best,


Roly

Have the AfterUpdate event of the container number field open a recordset.
You can create a recordset of only the container number that was just submitted.
Then loop through the recordset and check the state of the two check boxes for each transaction.

If chkComplete = 0 AND chkClientInvoiced = 0 Then
   MsgBox "A previous transaction with this container is not complete." & vbCrLf & _
          '"Add text to tell the user what to do."
   'code to roll back transaction, or cancel transaction.
End If

HTH



mmcdonal