Help! Autonumber messed up (1 Viewer)

Mackbear · Sep 17, 2019

Hi good day! I have several questions for the past few days and I may not have responded to it yet and now came across another problem. I have a table with several tables related to it through the autonumber field. Now this autonumber messed up. I was not able to add new record saying that the primary key may cause duplicate. It seems to increment a number that it says already existing. How do I reset this autonumber and the changes would also apply to the related tables? I hope there is a solution... :banghead::banghead::banghead:

theDBguy · Sep 17, 2019

Hi. I hope you have a backup. Take a look at Allen Browne's website for some suggestions on how to fix this. Here's the link. Good luck!

Mackbear · Sep 17, 2019

theDBguy said:
Hi. I hope you have a backup. Take a look at Allen Browne's website for some suggestions on how to fix this. Here's the link. Good luck!

I am looking at it right now, I am not sure, would you know if this would change the numbers as well on my related tables respectively like would it change the related table's autonumbers that corresponds to their number on the main table?

theDBguy · Sep 17, 2019

Mackbear said:
I am looking at it right now, I am not sure, would you know if this would change the numbers as well on my related tables respectively like would it change the related table's autonumbers that corresponds to their number on the main table?

It might if you had Cascade Update enabled.

AccessBlaster · Sep 17, 2019

Mackbear said:
saying that the primary key may cause duplicate. It seems to increment a number that it says already existing.

Does your criteria for your autonumber look like this?

Pat Hartman · Sep 17, 2019

Resetting the seed does not change any existing autonumbers. It changes the "seed" which is the last issued number so that Access knows what the next autonumber should be. When Access inserts a new record it uses seed +1 as the autonumber value. When the seed gets messed up, you start generating already existing autonumbers.

Resetting the seed tells Access to find the maximum existing autnumber value and make that the new seed.

Of course, back up before and after and compact as well once you're done.

Mackbear · Sep 17, 2019

AccessBlaster said:
Does your criteria for your autonumber look like this?

Yes it does....

Pat Hartman · Sep 19, 2019

So, did you run the reseed code yet?

Mackbear · Sep 19, 2019

It I didn't have time since I was in a rush to add data into it as some people needs to start working on those data, what I did so I can be able to add data was to switch it to Random and it's all messed up...='c

I am trying to fix it now, I am wondering why when I autonumber it does not start with 1, it started at 2 and I see the number 1 in the somewhere middle of the list, then number 4 at the last... why is it this way?

The_Doc_Man · Sep 19, 2019

Your image doesn't show an unusual set of autonumbers and they are in proper order. Were you expecting them to also be contiguous? Because they won't be. "Contiguous" ISN'T a property of autonumbers.

Pat Hartman · Sep 19, 2019

what I did so I can be able to add data was to switch it to Random and it's all messed up

That wasn't the suggested solution for a reason. You cannot change autonumbers once they are assigned. If this table has a child table, it will be a PITA to clean this up. If it doesn't have any child tables, then you can recreate the table and append the old records to the new table with the sequential autonumber. Just remember to omit the existing autonumber column from the append query and Access will generate new, sequential autonumbers.

Mackbear · Oct 10, 2019

Hi it'e been a while I apologize as I have been busy sorting out stuff... so the autonumber got messed up and I have child tables related, what I just did was to create another autonumber field, then i switched my pk field to number again, then copied the new autonumber field values and pasted them to the pk field that is now a number so now it cascade update every child tables. Then i removed the relationships, deleted the original pk field as i cannot switch it back to autonumber, then made the new autonumber field I created as the pk... and returned the relationsips. solved the problem! Thank you for the help everyone!

theDBguy · Oct 10, 2019

Mackbear said:
Hi it'e been a while I apologize as I have been busy sorting out stuff... so the autonumber got messed up and I have child tables related, what I just did was to create another autonumber field, then i switched my pk field to number again, then copied the new autonumber field values and pasted them to the pk field that is now a number so now it cascade update every child tables. Then i removed the relationships, deleted the original pk field as i cannot switch it back to autonumber, then made the new autonumber field I created as the pk... and returned the relationsips. solved the problem! Thank you for the help everyone!

Hi. Thanks for the update. Glad to hear you got it sorted out. Good luck with your project.

Help! Autonumber messed up (1 Viewer)

Mackbear

Registered User.

theDBguy

I’m here to help

Mackbear

Registered User.

theDBguy

I’m here to help

AccessBlaster

Registered User.

Attachments

Pat Hartman

Super Moderator

Mackbear

Registered User.

Pat Hartman

Super Moderator

Mackbear

Registered User.

Attachments

The_Doc_Man

Immoderate Moderator

Pat Hartman

Super Moderator

Mackbear

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread