Change autonumber (Access 2000)

Topic

New Reply

I want to change the autonumber of the field in table to 1000
and to preserve the old number from 1 to 22.Is there any code in Visual basic to do
that?. This concerns my tables Houses where houseid up to now is 22.I want the next
autonumber to be 1000.
Can somebody help me ?

Reply | Quote

Replies

You can create an append query that adds a record with value 1000 for the AutoNumber field. If you prefer, you can run this in VBA:

Dim strSQL As String
strSQL = “INSERT INTO [NameOfTable] ( [NameOfField] ) VALUES ( 1000 )”
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Replace NameOfTable with the name of the table and NameOfField with the name of the field.

Reply | Quote

Thanks for the detailed and useful reply

Reply | Quote

It isn’t that easy to reset the Autonumber next value to a specific number. One technique is to use an append query which specifies a value of 999, then delete that record. Then the next number used will be 1000. Note however that if someone starts entering a record and then cancels, that number is used up, and the next number will be 1001. If you are trying to manipulate autonumbers, you might want to reconsider and use another scheme such as using the max value of existing records, or storing the next number in a table and doing the increment function on your own.

PS – you cannot change the number to be a regular integer, add a record, and then change it back to autonumber – Access won’t let you.

Reply | Quote

I would just like to add 1 little thought to the answers given by Hans and Wendell, as sometimes there is a problem with appending records to a table in which there is an autonumber (and you force the value for this field).

I’d use the append query to set the # of the new record to 998. Then I would open the table in datasheet view and manually start adding a new record (you won’t actually try to save this record, just start data entry and cancel later). As soon as you start any data entry on the new record, Access will assign the autonumber. Make sure it is 999. If it is, everything is OK. If not, navigate to my website (see below) for a tool that will help you correct the autonumber setting.

Reply | Quote

When will people stop to use autonumber for what it isn’t desing.
An autonumber is only to be used as an ID and it should not matter what it contain.
If you need a specific numbering, use a numeric field and use the proper techniques to do the numbering yourself.

Reply | Quote

Here here. Amen to that.

I’m not trying to impose religion…it’s JUST a saying

Reply | Quote

What it contains does matter when the ID autonumber has already been used, so the ability to reset an autonumber field is very important.

Reply | Quote

Welcome to Woody’s Lounge!

Sorry, I don’t understand what you’re saying here. Could you explain? Thanks.

Reply | Quote

Thanks for the welcome. I may have been slightly off the topic – it’s now after 3:30 AM and I should have waited till the morning.

I frequently have to import multiple related tables. In Access 97 I never had a problem but in 2000 the autonumbering gets messed up – the next autonumber ID assigned to a new record has already been used so new records can’t be added until i have reset the autonumber field to a higher number than the biggest already assigned in the imported table.

See also “problem with repeating autonumbers (Access 97)” [Post: 513,879) and Mark Liquorman’s website http://www.Liquorman.net under “Tips and Downloads”. According to Mark: “The problem with AutoNumbers is a known and documented Access 2000 problem.”

I agree that a manually incremented Id would avoid my problem, but there are a lot of tables in a lot of databases that would need to be changed. Anyway, all I was saying is that it is valid and sometimes necessary to want to set the next available autonumber. Autonumbering is a convenience, it would be more convenient if you could easily set the next number occasionally, especially when it is malfunctioning..

Reply | Quote

OK, thanks for the explanation. I agree that the contents of an AutoNumber field matter if there are records in other tables linked to it.

I think Francois’ remark was directed more to people who want to reset an AutoNumber for aesthetic reasons. They have added some records (for example in a test) and deleted them again. They don’t like the resulting gap in the AutoNumber sequence. If prevention of such gaps is really important, it’s better not to use an AutoNumber field.

Reply | Quote