Avoiding duplication in a table.

[Frank Martin]

I have an append query "QryInvoiceGrouped" and I have set
its "top value" property to 1.

Therefore when I run this query it will append this 'top
value' into the destination table "LedgerTxns".

This works OK, but I need some setting in the table design
grid to ensure this procedure cannot be doubled up by
inadvertently running the append query twice.

A relevant unique field in the appended data is "OrderNo"
and I need some function to insert into the "OrderNo" field
property "ValidationRule" to ensure no duplicates are
possible.

Please help, Frank

 

[Klatuu]

The OrderNo field should by the primary key of the table. You cannot insert
a record with a duplicate value in the field that way. If you try, it will
throw an error.

I would then execute the query using the Execute method of the database
object with the dbFailOnError option which will pass the Jet error back.
Then capture the error you procedure's error handler.

 

[Frank Martin]

Thank you. The table already has a PrimaryKey.

I have set the properties of the "OrderNo" field to Indexed
No Duplicates, and this seems to be working.

Regards, Frank