multi-table index or integrity

[Ron]

Custom autonumber inputs new ID# in Primary Key field for
each record in several main tables of same db. Same ID#
field properties in each table. How can I ensure new ID#
hasn't duplicate in whole db, before allowing save new
record, please? I know a bit of VBA if wise to resort to
that. Thanks, Ron

 

[Allen Browne]

Very strange request. The purpose of a primary key is to uniquely idenfity a
record in a table. If you are trying to assign a unique value across tables,
are you sure you have the relational design correct? Should these entries be
in the same table, with another field to distinguish them?

The simplest approach would be to to set these properties for your
AutoNumbers:
Field Size: Replication ID
New Values: Random

The alternative is to use a Number field (not AutoNumber), and assign the
value whenever a new record is added. To do this, you need another table
that holds the seed value. Before saving any new record in any table, lock
the seed table. Assign the value to your record. Save the record. Increment
the seed and save it. Then unlock the seed table.

The locking of the seed table is the only way to guarantee that multiple
users, or processes are not assigned the same value at the same time. Since
you are using a single seed table application wide, you could expect lots of
clashes if multiple data entry operators are operating at once, so you will
need to handle those as well (typically random delays).

 

[Timbuck2]

I do not understand why you are taking this approach
however to do what I think you asked.

Create a union Query on the Keys from all tables
involved . Search that to determine if the Key exists.


or

1. Make the PK be TableName.number you generate
or

2. Create a union Query that prefixes the TableName on the
Number

or
3. Make table_People with common fields details can hold
the other fields - they all have names don't they.

Maybe something in all that will help you.