Avoiding duplication in a table.

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

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.
--
Dave Hargis, Microsoft Access MVP


"Frank Martin" wrote:

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

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


"Klatuu" wrote in message
...
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.
--
Dave Hargis, Microsoft Access MVP


"Frank Martin" wrote:

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