Auto Number starting at a particular number

S

supportt

Registered User.
Local time
Today, 00:22
Joined
Nov 21, 2001
Messages
70
Hi, I have a form with some fields on it, there is one called Pro Number. what I would like to happen is when a new record is created, it starts at a certain number and continues to increment by one. In other words, the first record would be 5600 and the next new record would be 5601 etc.

I did have this working in an previous database, by creating a append query to start the number, but that does not seem to be working now...

Any help would be very much apprecited.

David
 
That's the method. Append a record with an autonumber value 1 less than where you want your number to start. After you have appended a real record, you can delete the dummy record. If you delete the dummy record too early and compact your db before any records are added to the table, the autonumber will be reset to 0.
 
Thanks

I will give it a try...

thanks again
 
Well, maybe I missed something, that did not work

This is what I have, I have a query called auto_number_append and it is suppose to append one record to a table called auto_number, but when I run it, it is not appending a record. The auto_number table is empty before I run the auto_number_append query. So I added a record in the auto_number table and the pro # was 5500, then I ran my append query again and it said one record was appended. Then I went to my form and the first new record started with 5500, that is fine, but when I went to create the second record, it left the pro # field blank, I was wanting it to go to the next number. Am I missing something here?

Thanks
 
The autonumber does not show up until you are actually adding new data into the field. A workaround can be to create a boolean field that remains hidden from the user. When the user inputs new data (say, with autonumber field 55), and you want the form to show 56, then in the events that processes record 55, create an append query that selects the hidden boolean field of record 56. That should do the trick.
 
that seems to be a little much

my process was working fine, but it has been a while since I messed with the database. So, I really don't want to re-envent wheel at this point....but I do appreciate the additional options. I think my original was just real simple, but I know I am just missing some step to get this to work.

Really all the Pro # is, is a refrenence number for the Work Order

Thanks

David
 
Suggestion

Any chance I can attach the access db? So you could have a look, it is only 2mb zipped up...

thanks

David
 
2 megs zipped is kind of large... can you just copy the tables, query and form in a test db, zip that and post it? Bandwidth costs, you know... ;)
 
wondering if you were able to look at the db

just wanted to follow-up
 
wondering if you were able to look at the db

just wanted to follow-up
 
thanks for the advise :(

Well, the first link is a little advanced for me, I am a bit new to access. The second link sounds more like what I did the first go round and it worked then, but can't seem to get it to work now. Basically what I did was:

Enter a dummy record in my first table, it only has two columns, ID and PRO_NUMBER. I entered 5400 in the PRO_NUMBER table, then I proceeded to run my append query and it did say 1 record append, I then went into the dummy table and removed that record. Then proceeded to bring up my form, the PRO_NUMBER starts at 5400 by default on the first record, but after that, when i enter a new record it leaves the PRO_NUMBER field blank, so I have to enter it manually..... :(

Thanks for all the advise
 
So that you know, I've downloaded your db. Your AutoNumber field is the ID field, not the PRO_NUMBER field. It is the AutoNumber field that will increment. Also, you do not delete the Dummy record until after you have appended the rest of the table.

What needs to be done is you need to append a Long field with the beginning number -1 to the AutoNumber field that has not started yet. Then append the rest of your table. Then delete the dummy record.
 
The first Autonumber value should be 1 not 0. To start an autonumber at 1, remove all the records from the table and compact thedatabase. Or, remove the autonumber column, save the table, then put the autonumber column back.
 
I finally got it :)

Thanks Pat and RGUY, this was the problem, my destination table, column PRO_NUMBER was not set to AUTONUMBER and my DUMMY SOURCE TABLE, the PRO_NUMBER was set to AUTONUMBER, so before I ran my append query I was not able to edit the PRO_NUMBER in the DUMMY table to the starting number I wanted, gezzzzzzzzzzzzzzz....

Anyway, it is working great now, I will have to make a note of this, so next time I remember what I did...

thanks again for all the help

:D
 
The Easy Way....

Create temporary field (tempid)...data type to AutoNumber. Save. (1,2,3..)Change tempid datatype to number....Save. Use query to add up to what your number should be...ie Ex...You want key to start at 1000......Update tbl set tempid = tempid + 999. Set tempid back to autonumber :-))
 
You must log in or register to reply here.

Similar threads

D
Solved Starting Appended Data at 1
2 3 4
Replies
77
Views
2,207
Pat Hartman
P
Magger
My loop sometimes starts at 2 or 3
Replies
6
Views
123
Pat Hartman
P
A
Search box to find parts to add to job instead of a combo box
2 3
Replies
41
Views
782
Gasman
Gasman
S
Interesting puzzler
2 3
Replies
52
Views
1,432
Gasman
Gasman
S
Incremental Sequential Numbering by Year via Calculated Field
Replies
17
Views
757
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom