By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,473 Members | 1,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,473 IT Pros & Developers. It's quick & easy.

Am I Screwed with Autonumber?????

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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?


Nov 13 '05 #2

P: n/a

"mathilda" <sm***********@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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
Nov 13 '05 #3

P: n/a
> 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.
Nov 13 '05 #4

P: n/a

"deko" <de**@hotmail.com> schreef in bericht news:YP****************@newssvr14.news.prodigy.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


Nov 13 '05 #5

P: n/a
> 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?
Nov 13 '05 #6

P: n/a
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.com> schreef in bericht
news:9h****************@newssvr13.news.prodigy.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?

Nov 13 '05 #7

P: n/a
> You use the append query to append the long integer-record from the
dupe-table to the
*original* table.


it worked. I *am* surprised :)
Nov 13 '05 #8

P: n/a
This worked perfectly!

Saved my keister on this one, thanks.

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.