Am I Screwed with Autonumber?????

[mathilda]

I have a situation where I need to put back in a deleted record to a
table whose primary key is an Autonumber. And of course the autonumber
has to match what it was before. Any way of saving my butt on this one?

[Steve Jorgensen]

Good news - you're not screwed.

1. Create a new query, and close the initial dialog without adding any tables.
2. Make your query an append query, and choose the table you need to add the
row to.
3. Select each field name of the target table in the "Append To:" cell of a
different column in the grid.
4. In the field line, type the values that go to the fields in the "Append
To:" cells below.
5. Run the query.

That's it - you're done.

One caveat. I've seen this cause Autonumber correction sometimes (not
always), so it's good to compact the database after you do this.

On 18 Feb 2005 11:31:15 -0800, "mathilda" <sm***********@ yahoo.com> wrote:

I have a situation where I need to put back in a deleted record to a
table whose primary key is an Autonumber. And of course the autonumber
has to match what it was before. Any way of saving my butt on this one?

[Sven Pran]

"mathilda" <sm***********@ yahoo.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

I have a situation where I need to put back in a deleted record to a
table whose primary key is an Autonumber. And of course the autonumber
has to match what it was before. Any way of saving my butt on this one?

No, I don't believe you are screwed. As long as your old "autonumber " has
not been reassigned you should be able to reisert a line with the same
number.

But Autonumber is used in a lot of circumstances where it should not be
used:

When you need an indexed table then you usually have some field that can be
used as identifier and you should index on that field. If there is no such
field
then the reason is usually that the table should not be indexed because of
its
nature.

The only real reason I know for using autonumber is like when you have a
table
of invoices and need a method of assigning a new invoice number for each
line
added to the table.

regards Sven

[deko]

> I have a situation where I need to put back in a deleted record to a

table whose primary key is an Autonumber. And of course the autonumber
has to match what it was before. Any way of saving my butt on this one?

If you have 5 records autonumbered 1 thru 5, and you've deleted #5, you
could compact and close and re-enter the record. The new record should be
assigned autonumber 5.

But more likely you have thousands of records which are not autonumbered
sequentially and the one deleted was somewhere in the middle. If that's the
case, I'd be surprised if you find a way to get the record back in with the
same autonumber. Obviously, you'd be better off using something else as
unique identifier. Autonumber is a great choice for a primary key because
it's a single field, static identifier. But it's a poor choice if it makes
any difference what that number is.

If you want a unique, sequential identifier, there are ways to do it, e.g.
DMax("AcctNum", "tblAccts") + 1. But I'd still use Autonumber as a primary
key.

[Arno R]

"deko" <de**@hotmail.c om> schreef in bericht news:YP******** ********@newssv r14.news.prodig y.com...

I'd be surprised if you find a way to get the record back in with the
same autonumber.

Surprize:
Make a duplicate table with the same fields as your original table (use copy and paste on your
original table)
In this table change your autonumber-field in long integer!
Add one record in this table with your desired autonumber.
Now use an appendquery and you're done.

--
Hope this helps
Arno R

[deko]

> Make a duplicate table with the same fields as your original table (use
copy and paste on your

original table)
In this table change your autonumber-field in long integer!
Add one record in this table with your desired autonumber.
Now use an appendquery and you're done.

have you tried changing the field back to Autonumber after doing this?

[Arno R]

Sorry, maybe I explained to fast.
You use the append query to append the long integer-record from the dupe-table to the
*original* table.

Arno R

"deko" <de**@hotmail.c om> schreef in bericht
news:9h******** ********@newssv r13.news.prodig y.com...

Make a duplicate table with the same fields as your original table (use

copy and paste on your

original table)
In this table change your autonumber-field in long integer!
Add one record in this table with your desired autonumber.
Now use an appendquery and you're done.

have you tried changing the field back to Autonumber after doing this?

[deko]

> You use the append query to append the long integer-record from the
dupe-table to the

*original* table.

it worked. I *am* surprised :)

[mathilda]

This worked perfectly!

Saved my keister on this one, thanks.