By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

automatically generated sequential number

P: n/a
New to Access here. I inherited a db that has the first column as an
automatically generated sequential number. I want to bump it up, but
for the life of me I can't figure out how to do it. Is this type of
generation a built in function of Access? I'm sure it can be written
manually, but I doubt someone took the trouble to do it...
Can someone point me in the right direction to figure out how to change
this number?
Thank you one and all!
Sam

Jan 17 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Check the properties of the field (right-click on the table and choose
"Design View"). If the field is set as an AutoNumber, it will
automatically generate a sequential number fo rthe next record.

Nooby wrote:
New to Access here. I inherited a db that has the first column as an
automatically generated sequential number. I want to bump it up, but
for the life of me I can't figure out how to do it. Is this type of
generation a built in function of Access? I'm sure it can be written
manually, but I doubt someone took the trouble to do it...
Can someone point me in the right direction to figure out how to change
this number?
Thank you one and all!
Sam
Jan 17 '07 #2

P: n/a
Thank you. It does show as AutoNumber. Now, how do I change the number
sequence? After a certain date I want to bump it up by 10,000. But
where?
Thanks!

ManningFan wrote:
Check the properties of the field (right-click on the table and choose
"Design View"). If the field is set as an AutoNumber, it will
automatically generate a sequential number fo rthe next record.

Nooby wrote:
New to Access here. I inherited a db that has the first column as an
automatically generated sequential number. I want to bump it up, but
for the life of me I can't figure out how to do it. Is this type of
generation a built in function of Access? I'm sure it can be written
manually, but I doubt someone took the trouble to do it...
Can someone point me in the right direction to figure out how to change
this number?
Thank you one and all!
Sam
Jan 18 '07 #3

P: n/a
You can't do that with an autonumber. You'll have to change it to a
generic number and increase it yourself through code.

Nooby wrote:
Thank you. It does show as AutoNumber. Now, how do I change the number
sequence? After a certain date I want to bump it up by 10,000. But
where?
Thanks!

ManningFan wrote:
Check the properties of the field (right-click on the table and choose
"Design View"). If the field is set as an AutoNumber, it will
automatically generate a sequential number fo rthe next record.

Nooby wrote:
New to Access here. I inherited a db that has the first column as an
automatically generated sequential number. I want to bump it up, but
for the life of me I can't figure out how to do it. Is this type of
generation a built in function of Access? I'm sure it can be written
manually, but I doubt someone took the trouble to do it...
Can someone point me in the right direction to figure out how to change
this number?
Thank you one and all!
Sam
Jan 18 '07 #4

P: n/a

"ManningFan" <ma********@gmail.comschreef in bericht news:11**********************@38g2000cwa.googlegro ups.com...
You can't do that with an autonumber. You'll have to change it to a
generic number and increase it yourself through code.
Well, you can actually!
Suppose you would like the autonumber to start with 100.000
Create and execute an appendquery to add the desired number -1, (99.999)
Your autonumber will be 100.000 the next record

Step by step:
Make a duplicate table with the same fields as your original table (use copy and paste on your original table)
In this table change your autonumber-field in long integer!
Add one record in this table with your desired autonumber.
Now use an appendquery to append the long integer-record from the dupe-table to the *original* table

Arno R



Jan 18 '07 #5

P: n/a
Yes, there are a bunch of poorly hacked ways to do it.

Arno R wrote:
"ManningFan" <ma********@gmail.comschreef in bericht news:11**********************@38g2000cwa.googlegro ups.com...
You can't do that with an autonumber. You'll have to change it to a
generic number and increase it yourself through code.

Well, you can actually!
Suppose you would like the autonumber to start with 100.000
Create and execute an appendquery to add the desired number -1, (99.999)
Your autonumber will be 100.000 the next record

Step by step:
Make a duplicate table with the same fields as your original table (use copy and paste on your original table)
In this table change your autonumber-field in long integer!
Add one record in this table with your desired autonumber.
Now use an appendquery to append the long integer-record from the dupe-table to the *original* table

Arno R
Jan 18 '07 #6

P: n/a

ManningFan wrote:
Yes, there are a bunch of poorly hacked ways to do it.
There's nothing "poorly hacked" about it. It's a simple matter of

INSERT INTO autonum_test ( ID )
SELECT 99999 AS ID;

DELETE autonum_test.*
FROM autonum_test
WHERE (((autonum_test.ID)=99999));

As long as you don't compact the database before the next row is added
(AutoNumber ID=100000) you're fine.
Arno R wrote:
"ManningFan" <ma********@gmail.comschreef in bericht news:11**********************@38g2000cwa.googlegro ups.com...
You can't do that with an autonumber. You'll have to change it to a
generic number and increase it yourself through code.
Well, you can actually!
Suppose you would like the autonumber to start with 100.000
Create and execute an appendquery to add the desired number -1, (99.999)
Your autonumber will be 100.000 the next record

Step by step:
Make a duplicate table with the same fields as your original table (use copy and paste on your original table)
In this table change your autonumber-field in long integer!
Add one record in this table with your desired autonumber.
Now use an appendquery to append the long integer-record from the dupe-table to the *original* table

Arno R
Jan 18 '07 #7

P: n/a
I am very greatful for the input. I'm afraid the solution may be above
my level of expertise.
I expected a vairable somewhere containing the most current number to
be updated.
But I will give it a try.
Thanks, all!
Sam

Gord wrote:
ManningFan wrote:
Yes, there are a bunch of poorly hacked ways to do it.

There's nothing "poorly hacked" about it. It's a simple matter of

INSERT INTO autonum_test ( ID )
SELECT 99999 AS ID;

DELETE autonum_test.*
FROM autonum_test
WHERE (((autonum_test.ID)=99999));

As long as you don't compact the database before the next row is added
(AutoNumber ID=100000) you're fine.
Arno R wrote:
"ManningFan" <ma********@gmail.comschreef in bericht news:11**********************@38g2000cwa.googlegro ups.com...
You can't do that with an autonumber. You'll have to change it to a
generic number and increase it yourself through code.
>
Well, you can actually!
Suppose you would like the autonumber to start with 100.000
Create and execute an appendquery to add the desired number -1, (99.999)
Your autonumber will be 100.000 the next record
>
Step by step:
Make a duplicate table with the same fields as your original table (use copy and paste on your original table)
In this table change your autonumber-field in long integer!
Add one record in this table with your desired autonumber.
Now use an appendquery to append the long integer-record from the dupe-table to the *original* table
>
Arno R
Jan 18 '07 #8

P: n/a

"Gord" <gd*@kingston.netschreef in bericht news:11**********************@m58g2000cwm.googlegr oups.com...

ManningFan wrote:
>Yes, there are a bunch of poorly hacked ways to do it.
There's nothing "poorly hacked" about it. It's a simple matter of

INSERT INTO autonum_test ( ID )
SELECT 99999 AS ID;

DELETE autonum_test.*
FROM autonum_test
WHERE (((autonum_test.ID)=99999));

As long as you don't compact the database before the next row is added
(AutoNumber ID=100000) you're fine.

Indeed, nothing 'hacked' here. (I learned this 'trick' from my first Access book)
I guess Manningfan just does not like to be corrected when he gives bad information ...

But it is a clumsy way indeed. There *should* be no need to correct an autonumber or to reset it.
We all must try not to use an autonumer as a 'meaningful' number, only as unique recordID.

Arno R
Jan 18 '07 #9

P: n/a
Oh Christ on a popsicle stick, it's Arno...

"Clumsy" = "Hacked". You just love to try to break my balls, don't you?

Jan 19 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.