Making a field's autonumber start at a value other than 1 1

[craigorama]

Hi again!

I'm trying to create a field in a table that will increment by +1 each time. However the value of the first row has to be a number other than 1.

I retrieve that value through the function Get_Location_No.

Here is my code:

Dim strSQL As String
Dim tabName As String
tabName = "TELLUS_REPORT_UTM"

Dim number As Double
number = Get_Location_No
MsgBox number 'The value is 572 here

strSQL = "alter table " & tabName & " add column loc_num counter (" & number & ",1)"
MsgBox strSQL

DoCmd.RunSQL strSQL

End Sub

However, the field still starts at '1'. How can I make it start at 572?

Thanks!

 

[AlexCuse]

CraigORama -

Have a look at this kb article.

http://support.microsoft.com/kb/812718

hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[craigorama]

Alex,

I'm pretty new to this but i'm not sure if that applies to my situation. The article you linked to discusses how to reset an autonumber field. I just want to create a new field that starts at '572' and counts up from there.

One of my initial problems was that i already had one autonumber field in the table. As a workaround I created a new column as a long and copied the autonumber field into that one then deleted the original autonumber field.

The next step is where I'm stuck. Getting the new autonumber field to start at 572.

Thanks for your help.

 

[fneily]

If you searched on the Access forums, this has been answered numerous times. However,
Let’s say you want the numbering to begin with a different number other then 1. Let’s say 572. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 571. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 572. Delete record number 571.

 

[Remou]

Creating autonumbers with meaning is dangerous. Autonumbers can change and go out of sequence, gaps can appear and even negative numbers. All you can ask from an autonumber is that it should be unique.

 

[TheAceMan1]

How are ya craigorama . . .

[blue]As a workaround [purple]I created a new column as a long and copied the autonumber field into that one then deleted the original autonumber field[/purple] . . .[/blue]

If the above is true then you've indicated an [blue]independent table[/blue] with a new field of data type Long, [blue]and not autonumber[/blue]! This makes the requirement for your changes as easy matter of updating the value for existing records. [purple]Save the table under another name so you can come back to square one if necessary[/purple].

So . . . in a module in the modules window, copy/paste the following routine:

[blue]Public Sub SetID(TableName As String, FieldName As String)
   Dim db As DAO.Database, rst As DAO.Recordset, idx As Long
   
   idx = 572
   Set db = CurrentDb
   Set rst = db.OpenRecordset(TableName, dbOpenDynaset)
   
   If rst.BOF Then
      rst.AddNew
      rst(FieldName) = idx
      rst.Update
   Else
      Do
         rst.Edit
         rst(FieldName) = idx
         rst.Update
         idx = idx + 1
         rst.MoveNext
      Loop Until rst.EOF
   End If
   
   Set rst = Nothing
   Set db = Nothing
      
End Sub[/blue]

While in the module, open the [blue]Immediate Window[/blue] and type ([blue]you![/blue] substitute proper names in [purple]purple[/purple]) . . .

[blue]Call SetID([purple][b][i]TableName[/i][/b][/purple], [purple][b][i]FieldName[/i][/b][/purple])[/blue]

. . . and hit enter.

Now go check the table!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[craigorama]

Hi again,

Thanks for all your responses!

fneily, I tried to search but I guess I wasnt using the right terms because I couldn't really find something that answered my question. I'm still pretty new to this so odds are the answers were there but I just couldnt understand them. Plus I was trying to do this all with code.

In the end I did turn it into a long field and use an ado recordset like ace said. thanks, man! It was too much of a hassle to use an autonumber considering the table already has an OID field. Initially I had thought to use an autonumber because I knew how to set it up programatically using counter (1,1).

Here is the code for anyone who's interested:

Public Sub ADD_LOCATION_NUMBER_FIELD_TO_TELLUS_REPORT_UTM()
Dim strsql As String
Dim tabName As String
tabName = "TELLUS_REPORT_UTM"

strsql = "Alter table " & tabName & " add column loc_num double"
DoCmd.RunSQL strsql
Dim number As Double
number = Get_Location_No
MsgBox number

Dim theDBName As String
Dim theDBProvider As String
Dim theDBCon As ADODB.Connection
Dim theCat As ADOX.Catalog


'=======================
'Connect to the database
'========================
Set theCat = New ADOX.Catalog
theDBName = CurrentProject.Name
theDBProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theDBName


Set theDBCon = CurrentProject.Connection
theCat.ActiveConnection = theDBCon

Dim theFileSys As FileSystemObject
Dim rs As ADODB.Recordset

Set theFileSys = New FileSystemObject
Set rs = New ADODB.Recordset

Dim sourcesql As String
sourcesql = "Select loc_num from " & tabName & ""
MsgBox sourcesql

rs.Open tabName, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do While Not rs.EOF
number = number + 1
rs.Fields("loc_num") = number
rs.MoveNext
Loop
rs.Close
'=======================================================
'Close any open connections and set objects to nothing
'=======================================================

Set rs = Nothing
End Sub

Thanks again for all your help everone.