Kerrie said:
I am creating a table using a make table query and I would
like one column to have sequential numbers for each row
starting at any number I specify.
Hi Kerrie,
In addition to Van's good reference to Stephan's
Serialize db, I wonder if you could not just as
easily add a sequential column to the table
you just made. If you are using Access 200x,
save the following code in a module.
'*** start code ***
Public Function fAddSequentialColumn(pTblName As String, _
pFieldName As String, _
Optional pSeed As Long = 1, _
Optional pIncrement As Long = 1) As Boolean
On Error GoTo Err_fAddSequentialColumn
Dim strSQL As String
strSQL = "ALTER TABLE " & pTblName & " ADD COLUMN " _
& pFieldName & " AUTOINCREMENT (" _
& pSeed & ", " & pIncrement & ");"
CurrentProject.Connection.Execute strSQL, dbFailOnError
fAddSequentialColumn = True
Exit_fAddSequentialColumn:
Exit Function
Err_fAddSequentialColumn:
fAddSequentialColumn = False
MsgBox Err.Description
Resume Exit_fAddSequentialColumn
End Function
'*** end code *****
So....
in your code after you run your make table
query (use the Order By clause in your
query to sort your data sequentially as
you wish), then run the above function...
say the table you make = "tblNew"
your new seq. field = "SeqID"
you want to start with 2000
you want to increment by 1 (default),
then line after your make table query
might be:
fAddSequentialColumn "tblNew","SeqID",2000,1
caveat:
the function does not check if the table exists,
if the fieldname already exists,
if you already have an Autonumber field...
all conditions that will cause function to fail.
You could wrap the function in an IF/THEN/ELSE
to test for success.
Please respond back if I have misunderstood.
Good luck,
Gary Walter
