I have three tables in Access and each have their own primary ID. For example, tblA, tblB and tblC and each of them has their own Primary ID 1, 2, 3 etc etc I have three forms, that add records to each table.

Now as it may happen in most cases my spec has just changed. I need to add a common extra filed called the index number for all the three tables. It has to autoincrement earh time a new ecord is added and has to be in the range of 500,000 and 599,999. The index number has to be unique and no two tables can have the same index number. Hence something like this:


tblA
ID IndexNum
=============
1 500000
2 500001
3 500003
4 500006

tblB
ID IndexNum
==============
1 500002
2 500004

tblC
ID IndexNum
===============
1 500005



Hence as you can see IndexNum is incremented each time a record is added in either of the three tables and no two tables have the same index number.

How can I implement this?

Thanks for all the guidance,
Zub.....

Create a separate table which function is to provide you with a new autonumber id.
Use this id to add records in the other tables.
When you create three autonumber fields i can garantee that this will work at first but five minutes later you get a lot of mess because it is very hard to keep the autonumber for three tables in sync. Hence my solution.

When working in a multiuser environment create a GUID and insert it into the Autonumber table. Then select the new autonumber id based on the GUID. This never fails.

Enjoy!