Henry Simpson
hsimpson88 at hotmail.com
Mon Jun 30 15:57:08 CDT 2003
I was responding to the statement:
...Supposing you were required to preserve the Autonumber -- how would
you go about appending records as you archive -- you couldn't I'm
guessing...
And now you say:
...I wasn't talking about forcing a new AutoNumber -- I was talking
about appending records that already have an AutoNumber...
The implication in both cases is: you can't set a record's autonumber to an
arbitrary value. That is not true. What you do is force the AN of the
resulting archive record to the value in the originating table in the
append. There is no need to add and delete blank records to force AN values
which is the only interpretation I can place on what John said:
... if you do a restore you MUST go get the last number, add 1 to it,
and insert a fake record with that number, then delete that fake record back
out...
John's suggestion could work for the situation where you were archiving
additions to the end of a table and one or more consecutive records ending
with the last were deleted but the approach reinforces the implication that
one can't simply set the value as required.
Good thing this issue is moot but a casual surveyor of the archive may pick
up some misconceptions or may be led to believe that list members don't
understand autonumbers.
If you have a table tblArk with 10 records and a FirstName field and an
Autonumber field named PK and you need to write a record that has an
existing AN of 45:
Dim db as Database
Set db = CurrentDb
db.Execute("Insert Into tblArk(PK, FirstName) Values(45, 'Joe')")
MsgBox db.RecordsAffected
If you add and delete a thousand records, you can still run the code above
and you will have 1 record added provided that the number 45 isn't already
taken and notwithstanding that the next automatic number would be over 1000.
In any case where the AN 45 is not already used, the example code above
will insert a record and set its autonumber value. If you have a record
with an existing autonumber that needs to be changed, you can insert the
field values into a new record and set the desired autonumber for the
duplicate and then delete the old record. While you cannot directly edit an
existing AN, you can readily achieve the result.
There is no need to get into the question of why one would want to change or
set an autonumber. I was merely attempting to prevent repetition of a too
often repeated misconception.
Hen
> >
> > The main point I was making was that I understood Susan to imply that
>one
> > can not set the value of an autonumber field to any arbitrary valid
>value
> > and that is not accurate.
>
>============== I don't remember what I said, but I'm sure that's not what I
>meant. :) I wasn't talking about forcing a new AutoNumber -- I was talking
>about appending records that already have an AutoNumber...
>
>Susan H.
_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail