How can you get autonumbering to start from a specific number 4

[handlebars]

Is is possible without vba code to get the autoumber field to carry on from a specified number, as opposed to 1?

Thx

Andrew

 

[adherennium]

Although I realise this might not be practical if a very high starting number were required, but you could always create a load of dummy records until you reach x-1, where x is the starting number that you want, then delete all the dummy records. This could be done manually, but VBA code would be worth writing, especially if you wanted a high starting number, (Say 1000).

dim dbs as database, rstTable as recordset
dim Acounter as variant

Set dbs = Currentdb()
Set rstTable = dbs.OpenRecordset("TableOne",dbOpenDynaset)

Acounter = 0

With rstTable
Do until Acounter = 999
.AddNew
![Field1]=1 ' Just to populate one field
' creating a valid record
.update
Acounter = Acounter +1
Loop

Acounter = 0

Do until Acounter = 999
.findfirst "[TableOne_ID] = " & Acounter
.Delete
Acounter = Acounter +1
Loop
.Close
End With

Hope this helps.

 

[JeremyNYC]

OK, there is a very big fundamental problem here. Autonumbers are not going to be sequential. They are not designed to be sequential. Using them with the expectation that they will be sequential _will_ get you in trouble.

Please do some searching either here or at groups.google.com in the newsgroup comp.database.ms-access on the topic of autonumbers. There are hundreds of threads in which people ask questions similar to this and the reply of professional developers is always the same: you are using autonumbers incorrectly.

If you add a record and delete it, that autonumber has already been used, and won't be reused. If you start to add a record and hit the escape key, the same is true.

If you need unique, sequential numbers, get code from the Access [version number] Developer's Handbook and use that.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Fubear]

To get an autonumber to count from a set value, do the following:

INSERT INTO yourTable (Autonumberfield) VALUES 999
DELETE * FROM yourTable WHERE Autonumberfield=999

change 999 to whatever you want to count from -1

If you have any other fields that cannot be null, then insert garbage values into them as well to getthe record created.

 

[TonyJollans]

Hi Fubear

This question has been asked many times before and I don’t understand why anyone would want to do it. Despite that, your solution is brilliant and well worth a star.

Jeremy’s points are very valid (and handlebars should take note of them) but most AutoNumbers are sequential and a bit more code along the same lines as yours could determine if the number you wanted to use was below the current setting.

Enjoy,
Tony

 

[Fubear]

One reason for doing it would be to keep sets of records seperate. I will give an example:

Lets say that two companies (A and B)merge, and they have two customer databses that need merging. You could rebuild new company ID numbers, and say that company A's customers are in the range 1000-3000, company B's are in the range of 4000-7000, and any new customers start their autonumbering from 8000+

It doesnt matter whether or not the numbers are ACTUALLY squential or not, you can tell at a glance by their range where the customer has come from.

 

[JeremyNYC]

I would still say that it's a bad practice, in that it's assigning some human-readable value to an autonumber field. A far better practice would be to add a field to your table(s) indicating the origin of the record--make it numeric with a lookup table.

That way there's no interpretation, there's an actual indicator, specifically keyed to the information you're tracking.

I know that's just one example, so I'm not trying to invalidate your point by challenging that one example.

But I do feel very strongly that it's best to leave autonumbers with only one job to do--enabling Jet to distinguish between records.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[oharab]

I totally agree with jeremy, Autonumber should have no meaning outside of Jet.

If you want to show an auto number to your users, then you should use a number you have generated yourself.

thread705-308576 has some code in which generates a number that includes the current year. It could easily be adapted to include text strings or dates or whatever you want.


Cheers

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[Fubear]

One other reason - which is how I found out how to do this in the first place.

I had an access database that when we sent it out to the client, started doing really weird things. When i went off-site to check it out, I found that for some reason the autonumber had jumped back a few thousand and was overwriting old records when entering new records, and then the old records data was being linked into the new.

I had to find a way to reset the autonumber to a 'safe' value, and reload backups of currupted data. It was a messy process.