Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2019, 12:56 PM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,633
Thanks: 102
Thanked 2,279 Times in 2,100 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Repeated Autonumber Gremlins

Here's a little puzzle for anyone interested

Some time ago I read an article by Allen Browne about autonumbers repeating:
Fixing AutoNumbers when Access assigns negatives or duplicates
However I'd never seen any of the behaviour he described until now

Attached is a single table database with an autonumber field. It has 6 records.
If you try and add a record it will restart the autonumber seed at 1


Of course it won't allow you to save the record as its a duplicate
Ditto for the next 5 records you try & append until you get to ID=7

I've attached the database for anyone interested to try for themselves.
I know exactly how I did it unintentionally & can replicate it easily
None of the explanations on Allen's site match what I did.

Lets see how long it takes anyone to work out what I did to cause this!

EDIT: 12/01/2019
Oops - I uploaded the wrong file yesterday - that one worked properly!
New version uploaded which does have the autonumber gremlins
Attached Images
File Type: png Repeated autonumber.PNG (77.7 KB, 205 views)
Attached Files
File Type: zip RepeatAutonumber_v2.zip (35.9 KB, 10 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-13-2019 at 02:23 PM. Reason: Replaced attached file
isladogs is offline   Reply With Quote
Old 01-11-2019, 01:02 PM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,149
Thanks: 349
Thanked 535 Times in 517 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Repeated Autonumber Gremlins

Did you reset the table to start at 1. I've done that after testing and before going live, but only after deleting all records.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 01-11-2019, 01:08 PM   #3
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Hi Colin,


I gave it a try and here's what I did to duplicate your scenario.


1. I created a table with two fields, one as Autonumber and Primary Key
2. I entered a few records to make sure the PK is automatically assigned a sequential number
3. Then, [spoiler]I deleted the first record[/spoiler]
4. ...I then re-inserted the first record back in using an APPEND query.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 01-11-2019 at 01:13 PM.
theDBguy is offline   Reply With Quote
Old 01-11-2019, 01:10 PM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Quote:
Originally Posted by Gasman View Post
Did you reset the table to start at 1. I've done that after testing and before going live, but only after deleting all records.?
Hi. Since Colin posted the link to Allen Browne's article and it's already mentioned in there how to reseed the Autonumber field, plus he (Colin) said he did this "accidentally," I figure he probably didn't reseed the table.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-11-2019, 01:20 PM   #5
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Okay, after re-reading the original scenario, I am revising my steps:


1. Create the table with the Autonumber PK field
2. Enter some data
3. Then, "accidentally" ....use an APPEND query to insert a row with a value of 0 for the Autonumber field.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-11-2019, 01:36 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,633
Thanks: 102
Thanked 2,279 Times in 2,100 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Repeated Autonumber Gremlins

Hi

I didn't do any of the following:
a) intend to create a table like this - that was done unintentionally
b) do anything in Allen's article to cause this effect
c) reseed before uploading
d) compact the database
e) delete any records before uploading the file

I did do something stupid when creating the table!
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-13-2019 at 02:22 PM. Reason: clarification
isladogs is offline   Reply With Quote
Old 01-11-2019, 01:43 PM   #7
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Hi Colin,

Please see my last post where I also did not "delete" any records. I realized (after looking at your posted image again) it's not necessary to delete any record. Although, what I ended up doing is not something I ever thought of doing before (I wasn't even sure it will work until I tried it).

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-11-2019, 01:47 PM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,633
Thanks: 102
Thanked 2,279 Times in 2,100 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Repeated Autonumber Gremlins

Ah .... but you haven't actually said what you did!

What I did was also something I'd never done before ... because it was a stupid thing to do ... but you haven't yet guessed what (or at least you haven't said how to replicate this)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-11-2019, 01:53 PM   #9
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Quote:
Originally Posted by isladogs View Post
Ah .... but you haven't actually said what you did!

What I did was also something I'd never done before ... because it was a stupid thing to do ... but you haven't yet guessed what (or at least you haven't said how to replicate this)
LOL. I actually said what I did in both my posts. I guess you didn't "look" close enough. At UA, we have a BB tag called [spoiler], but I guess AWF doesn't have it (I tried using it, and it didn't work), so I had to use another technique to "disguise" (hint, hint) my post (to avoid spoiling the potential answer for everybody else).
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-11-2019, 02:25 PM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,633
Thanks: 102
Thanked 2,279 Times in 2,100 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Repeated Autonumber Gremlins

Doh! I've used the same spoiler method myself many times.
Sorry to disappoint you but that's not it either .... but I can see that it would work
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-12-2019, 08:04 AM   #11
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Quote:
Originally Posted by isladogs View Post
Doh! I've used the same spoiler method myself many times.
Sorry to disappoint you but that's not it either .... but I can see that it would work
Okay, I have been trying it out without looking at your file first, so I decided to download it today to give it another shot. Funny thing though, when I opened your file after downloading it and tried to enter a new record to the table (I selected Today from the date picker for StartDate), Access assigned ID=7 to the new record (I was expecting to see a 1). Could you try your file and see if you get a 1? Thanks.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-12-2019, 09:30 AM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,633
Thanks: 102
Thanked 2,279 Times in 2,100 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Repeated Autonumber Gremlins

Oops - thanks for letting me know.
I should have checked the file before uploading it. That one worked as it's meant to do ... with no gremlins

I've now replaced the original version in post #1 with one that does show the strange behaviour.
Its also attached here!
Attached Files
File Type: zip RepeatAutonumber_v2.zip (35.9 KB, 9 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-12-2019 at 11:58 AM.
isladogs is offline   Reply With Quote
Old 01-12-2019, 09:50 AM   #13
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Thanks. This one does give me a 1 when I try to add a new record. Now, is what I did anywhere close to what you did? I was able to modify my step to eliminate having to delete a record, but if it's not the same as what you did, then I guess there are a few ways to mess up an Autonumber field. Did your "accident" involve using an APPEND query?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-12-2019, 11:52 AM   #14
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,633
Thanks: 102
Thanked 2,279 Times in 2,100 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Repeated Autonumber Gremlins

Hi DBG
I first noticed the problem when I tried to use an append query on the table and got a key violation error - 6 records could not be appended.

However no append queries were involved prior to that and so are not the cause of the effect
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-13-2019, 09:22 AM   #15
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,029
Thanks: 3
Thanked 441 Times in 434 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Repeated Autonumber Gremlins

Append queries are not the only way to add data and change the autonumber seed.


I did have a case some years ago with a corrupted record (memo field). As several days of data entry/amendment had taken place before it was noticed, I deleted the record and used an append query to insert the deleted record from a back up copy of the data.

Cronk is offline   Reply With Quote
Reply

Tags
repeated autonumbers

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Repeated code fat controller Modules & VBA 3 11-26-2014 03:04 AM
Gremlins in my form shift2076 Forms 2 04-12-2007 08:06 AM
Gremlins in the VBE editor Steven Deetz Modules & VBA 4 03-15-2005 11:14 AM
repeated prompting enigmajl Reports 1 08-11-2004 07:14 PM
Repeated value in combo box ohio Forms 2 01-17-2001 04:35 PM




All times are GMT -8. The time now is 02:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World