Two AutoNumber fields in same Table/Form

Question

Hi, I am pretty sure I can not have two AutoNumbers in the same form but is there another way to have say a 'short text' field auto fill when I open a new form or are they both the same thing!
Years ago, I named the AutoNumber field the wrong name and now I am trying to change it, if possible.
i.e. the primary key field was Social Security # and I no longer ask for or is it required by Insurance companies (I'm in insurance and years ago all the companies wanted the customers SS#, (perfect primary key, no duplicates field)

Thanks for any suggestions.
Frank

Answer 1

First, you are right. One AutoNumber per table is supported.

Second, adding one now is not hard to do. The hard part will be refactoring all of the related tables to use it, instead of the SS#. That's just a matter of discipline and careful processes.

Finally, I disagree that SS# was ever a good choice for a primary key on a few counts.

One it never was truly unique. At the very least, identity thieves were never shy about using other people's SS#s even for insurance scams. I remember a case of fraudulent workers comp being exposed, as a matter of fact, when an analyst identified more than one person filing claims under the same SS#.

It suffers from the very problem that is promoting your current dilemma. Relying on it makes you vulnerable to changes in legal requirements, sooner or later.

And finally, it's stored as a short text field which is much less efficient than the Long Integer used for AutoNumbers.

Start by creating a new Primary Key field in the one side table where you used SS# for the PK. Use the AutoNumber datatype.

Saving the table will generate new, unique, values for each current record.

Add a corresponding Foreign Key field to all related many side tables; use the Long Integer datatype.

Update the FKs in all records in related tables by joining on the current SS# field to ensure data integrity.

Redesignate all of the relationships to use the new PK and FK fields.

Finally, you can then remove the old, now obsolete SSN# Foreign Key fields from the many side tables.

Do all of this first on a back up copy of the accdb; things could go wrong.

Answer 2

I'm not sure I understand the issue. You have a form with an autonumber, but that field is not the primary key? You want to make it the PK, but you have the wrong name?

The name of a PK is immaterial. You can call it Crap if you want to.

It might help us help you if you describe the table and exatly what you are trying to do.