CHANGING TO AUTO NUMBER ON TWO RELATED TABLES 1

[villica]

I have table A related to Table B by ID. A record on table A can have many records on table b. The Id field is a number field. The user wants me to change this to Autonumber. What is the best way of doing this. Any help, sugestions would be very much appreciated

 

[Terpsfan]

Unfortunately Access won't let you just change to an autonumber field once you have entered any data in the table. However, I have found a way to get around this. This is sort of an elaborate way to do it, but it works. This just underscores the importance of table design. Follow these steps:
1) Add an Autonumber field to your primary table with the name of AutoID. Once you do this all the records in the primary table will receive an autonumber.
2) Run an update query in which you have both the primary table and secondary table added. Update the ID field in the secondary table with the value of the AutoID field in the main table. This is will change the value of your ID field in the secondary field to match the AutoID field.
3) Change the primary key in the primary table from ID to AutoID.
4) Modify your relationships between the two tables by deleting the prior relationship between ID and ID. Then relate the AutoID in the primary table with the ID of the secondary table.
5) You might want to redesign your form/subform by creating it again from scratch to reflect the new relationship.

 

[villica]

Thank you very much it works. Can the second table be changed also to autonumber.

 

[Eradic8or]

Your table B is in direct relationship with table A.
It is not advisable to make the field in table B an autonumber becuase these are unique (no 2 the same) and as you have a one to many relationship between the two, you would cause confusion between the related fields because you may only have one I.D. number in table A but several references to the same number in table B.

eradic8or.