Primary Key reverted to earlier number

[Barbara Shapiro]

My database has a table which had the primary key approaching 10,000, when
suddening I could no longer add new records. It would say I had a duplicate
record.

I noticed that the primary key it was trying to use was in the 3000's. I
don't know how it reverted down.

Anyone have a suggestion for getting the primary key back up to the higher
number without trying to enter records one by one and hitting escape 7
thousand times?

 

[Jerry Whittle]

What version of Access and what service pack? Also is the primary key field
an autonumber? There is a bug or two with autonumbers and some versions of
Access. However these bugs should be squashed IF you have the latest
Office/Access service release.

 

[Barbara Shapiro]

I have Access 2007. I can't find where to see what service pack it is.

 

[Allen Browne]

If a compact/repair doesn't fix it, see:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

To determine the service pack in A2007, click the Office button, Access
options, and Resources. Service Pack 1 for Office 2007 does not address this
issue, but it is certainly worth having
http://office.microsoft.com/search/redir.aspx?AssetID=DC102486291033&Origin=HH102511011033&CTT=5

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Barbara Shapiro]

I do have Service Pack 1.

I checked the "Fixing AutoNumbers when Access assigns negatives or
duplicates" article. In the section for Other causes, you indicate that the
subform should NOT have a text box for the AutoNumber field. It seems that
you are referring to data entry forms.

My problem table does not have any forms with subforms for data entry, only
for reviewing data.

Thanks for suggesting this fix.

 

[Allen Browne]

Hmm. Maybe that was confusing. The article is saying that you can
demonstrate the problem by creating the subform without the text box. It is
not advising creating the form without the text box.

In any case, this only applies if you are entering or modify data as you
said.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message