Autonumber Anger 1

[mrtauntaun]

Hi all. I have an autonumber field and I want to change the starting value of it. I have consulted the help file, followed it's intructions explicitly (with an append query), and it is not working. The query appends the number I want (5200), however, the next record starts with 5320 and so forth, not 5201. I have delted the autonumber field, saved it, created a new one with no name and it still gives me a value way to high. Am I missing something? I got this to work once, but it's not working now. Ideas? Thanx!!!!

 

[AutumnBlues]

Have you got New values set to 'Increment'?

 

[mrtauntaun]

Yes.

 

[JeremyNYC]

Here's how to do it: Follow all the instructions you've been following. Then compact your database.

Here's why not to do it: many people labor under false notions about autonumber values. I can't say for sure, but it sounds like you are trying to associate some value or information with the value in the autonumber field, and this is something to be avoided. You should certainly not (not saying you are, but just in case) have any expectation that the autonumbers will be output with no gaps. Any time you start to add a record and cancel it you will increment the autonumber.

In any case, I hope this helps.

Jeremy Wallace =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[mrtauntaun]

Ok, I ran the query again, and then I used compact and repair database under Database Utilities, but still no luck. Was that correct?

 

[SgtJarrow]

Delete ALL records from your table, compact the database, run the append query as previously done, and your should be fine. If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[AutumnBlues]

There may be a few dirty records in there. Try copying the table (data-definition only) and starting again.

If that doesn't work you might want to try (you might want to do this anyway) changing the autonumber field to a regular number field and setting up a function that gives you the next ID. Then call that function in your append query. Like this...
-------------------------

Sub MyAppend()

Dim strSQL as string

strSQL = "INSERT INTO tblWhatever ( ID, OtherField) SELECT " & GetNextID & ", SomeText"

DoCmd.RunSQL strSQL

Exit Sub
End Sub

---------------

Function GetNextID() as long

Dim strSQL as string
Dim Rec as Recordset
Dim db as database

strSQL = "select Max(ID) as LastID from tblWhatever"
set db = currentdb

Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)

With rec
If .RecordCount = 0 Then
GetNextID = 1
Else
GetNextID = !LastID + 1
End If
End With
Exit Function
End Function
------------------

AutumnBlues

 

[mrtauntaun]

Wow. Actually, what I ended up doing was removing the autonumber field, exporting the table to an excel file, deleting the original, and then imported it. Kind of a pain, but it worked
I will keep what you've suggested in mind for future projects, thank you all so much for your help!