my autonumber is malfunctioning

[Guest]

thanks to the knowledge i have gained by lurking on this forum, i have
managed to untangle a database now formerly consisting of one giant table and
have made it into something reasonably normalized. However, just recently
one of the tables has started assigning new records autonumbers from the
middle of the deck -- those being, numbers it's already assigned, which sets
you up to get that "duplicate record" error.

surgically altering it in the table does not work either as it tells me it's
bound to autonumber ID field and i can't alter it (SOL)

Short of creating and obliterating new record after new record until i run
it out to the next truly new number, how can i fix this?

Querent

 

[John Vinson]

Short of creating and obliterating new record after new record until i run
it out to the next truly new number, how can i fix this?

Be sure you have the most recent service packs installed for your
version of Access. This was a pretty common bug in at least two
versions of Access as originally released.

See http://support.microsoft.com/kb/884185/en-us for one of the bug
descriptions.

John W. Vinson[MVP]

 

[Guest]

Thanks for your response, Mr. Vinson. I'm printing the KB article out now
and will test it (after lunch) -- will post back w/ results.

This misbehavior only started AFTER i'd managed to get a click-for-query
button working that filtered a query of model# and serial# for history of a
given piece of equipment. (This is a database that tracks equipment repair)
It started assigning these duplicate numbers with the previous instance of
the unit in the particular entry that i was using to test the function
(because i knew we'd seen it before).

Arrrgh... you see, with this project, i'm becoming an Access developer /
VBA programmer "the hard way"!

Querent

 

[Guest]

update:
my Access 2003 installation is up to date per the office update site.
compacted database again, then typed in the VBA script from the KB article
but when i try to run it i get Error 3292, SQuirreLy Syntax Error.

checked the sqlFixID line (and it does give me the correct next number when
i hover the mouse pointer over iMaxID) but if there's something wrong, it's
very subtle.

Debugger highlights the next line, DoCmd.RunSQL

And the table still deals from the middle of the deck. There are 700
records, and where it should be issuing 701, it gives 362...

Querent

 

[Guest]

Fixed -- decoupled the relationship between that field and another table,
and ran it -- everything is okay.

Thanks again to this forum, which is mentioned in my code comment headers as
the source of my Code Fu.

Q