AutoNumber

  • Thread starter Thread starter JIM.H.
  • Start date Start date
J

JIM.H.

Hello,
I have an AutoNumber field in my table and I need to start
AutoNumber from 1 each time I delete and append records.
How can I do that?
Thanks,
Jim.
 
Jim, when you say "delete and append records," I trust you mean delete all
the records in the table and append new ones. If that's the case, compact
the database before you append.
 
Yes I meant delete all records. However I do that on a
temporary table when the users click a button so I can not
compact database at that time. Is there any other way?
 
Thanks Bob,
I do not need AutoNumeber actually. However when I append
some records to a table, I need a column which is a
sequencial number starting from 1 for each record, how can
I do that.
 
how shoudl I call this function in an append query? ANd
how can I set LastVal initial value to 1. I can not ask
users to provide a parameter.
 
put it as the key "field" to append
as all numeric values are initialized as 0 it will allways start of with 1..

Pieter
ie
if you have
INSERT INTO DEST(FIELD1..FIELDX)
SELECT FIELD1.. FIELDX FROM SOURCE
ommitting the autonumber field
change to
INSERT INTO DEST (FIELD0,FIELD1..FIELDX)
SELECT UNIQUENUM(FIELD1) AS FIELD0 ,FIELD1..FIELDX) FROM SOURCE

can't be more specific without the actual SQL ;-)

Pieter
 
Thanks Pieter,
It is working fine. One last question though, each time I
run my append query. I want lastVal should start from 1.
how can I do that?
 
like i said, all number variables are initiated to 0 including static ones,
thus the first value returned will allways be 1...

pieter
 
but offcourse ;-)
if you run it more than once in a session it will fail with my code..
you should initiate it to make sure ...
thus¨
function UniqueNum(Byval AnyFieldShouldDo As Variant,byval reset as boolean
= false)& call UniqueNum(0,true)
before running the query

sorry about the rebuff

pieter
 
Back
Top