Append autonumber without reseeding?

I have an Access XP database where I would like to append a record with a specific autonumber but I do not want to reseed the autonumber.

Anyone have a method of running an append query with a specific autonumber without reseeding the autonumber?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

So you currently have an autonumber of say, 10000.  You want to add 2,000,000 then have the next one be 10001?

can you explain in a bit more detail what you are trying to accomplish - some specific examples would be helpful, because what you are saying could lead to some major problems, if implemented EXACTLY as you have asked.

Gustav BrockCIOCommented:
You can't.
You can with an append query appen a custom ID. Next ID will be ID+1.
You could before doing that retrieve the Max(ID), append that after the custom ID, then delete it. However, the next ID would be either Max(ID)+2 or your custom ID+1.

To put it short: If you need custom numbers use a separate field for those.
Autonumbers are not intended for numbers carrying any meaning at all.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

emijohnsAuthor Commented:
Bummer. It was doing this just as I wanted it to in 97 and then in XP things changed and I'm trying to make it work.

Here's the scenario...

1. The user presses the custom made Edit button which copies the record and its child records into a temp table for safe keeping.
2. The user modifies the record/child records in the original table. (this includes adding and/or removing child records)
3. The user presses the custom made Undo button which copies the temp table records/child records (keeping the same unique autonumber) back into the original table.

The 3rd step is where the problem comes in. If a user modifies the fields in a child record I can use an update query to change the values back to their original values. If a user adds a child record, I can undo that change with a delete query.


If a user deletes a child record and then presses the Undo button, how do I add a child record back to the original table without reseeding the autonumber?
why are you worrying about the primary key (autonumber field). what difference does it make if the 'autonumber filed' is 10, or 54 or 12378 for that matter?  you should NOT be using autonumber fields for anything that is MEANINGFUL to the user - in fact, they should be completely UNAWARE of those fields in your database.

Steve BinkCommented:
You could encompass the whole thing in a transaction, but since I've never used Access for transactions, I don't know safe that will be.

Ideally, adding the child record should not require a specific auto-number.  For example, I start editing record 3746 and its associated children.  The children are numbered 1, 2, 3, and 4, and are also joined to the parent table by way of the '3746' autonumber.  Now I delete child record 2, then hit the Undo.  Record 3746 is still in the database, and can still be associated with children.  I add record 2 back in, and although it receives a new autonumber (say 5, assuming there is no other data), it is still referenced properly by the 3746 parent ID.

cactus_data said it best: Autonumbers are not intended for numbers carrying any meaning at all.

If you are concerned about the value of your autonumber, then you should not be using autonumber for that field.
emijohnsAuthor Commented:
True, the autonumber is not used as a meaningful number at this time.  The hesitancy that I have is if for some reason in future development I need to create a child record off of the current child record. At that point I would need a stable autonumber.

Is creating a child record from a child record considered bad database design?
Steve BinkCommented:
Not if you need it, but if you think you will need it, plan for it now.  You can also decrease the number of headaches you'll have down the line if you turn on the referential integrity options to cascade DELETE and INSERT actions.  This makes sure, for example, that you cannot delete a child record unless all of its sub-children have been previously disposed of.  The reverse hold true also: you won't be able to INSERT a child unless the parent exists.

You can use an autonumber for that type of relationship, so long as you do not depend on that autonumber staying constant through multiple deletions/insertions.  The key point: autonumber should have NO meaning to you or your users aside from being a guaranteed unique value.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

> cascade....INSERT actions



> Is creating a child record from a child record considered bad database design?

Not at all, but you don't need a "stable" autonumber for this. At the point where you need to create your child records, you simply do so using the existing autonumber value. As the others have said, it really doesn't matter what those autonumbers are at all. If you need to do it programmatically, there are ways to do it in code and/or SQL without needing a sequential autonumber. You simply need a way to identify which records you want to create children for.
Steve BinkCommented:
deerrrr...allow me to rephrase:

Turn on the option to enforce referential integrity for INSERT and DELETE actions, and the options for cascading DELETE and UPDATE actions.

It's been a little hectic.  :)  Thanks for the catch, shane.  Cascading INSERTs...that implies that Access is psychic, yes?  hehehe

emijohnsAuthor Commented:
Thank you all for you input. It really does help to talk these things out with knowledgeable folks.

My plan is to forget the idea of keeping the original autonumber when copying the records back into the original table. Neither myself or my co-worker can think of a good enough example in the future where we would need to create a child record on a child record at this point.
One other technique is to add a "deleted" column and have the user merely flag the child record as deleted. In your "commit/undo" process, you could then do the actual delete. This may limit your user interface options though, such as letting a user operate in a datasheet, but there may be a way around even that.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.