Set Autonumber to a spacific starting number 6

[intlguild]

I know I can advance the autonumber by pasting and deleteing records. But is there a cooler way to do it by using a form?

Thanks...

 

[RickSpr]

Are you starting with either a new table or an empty one that's been compacted? If so, you could have your form add and delete a record n times to set the next autonumber to n+1.

You can't change the autonumber directly. When you compact the database, it's set to one more than the largest value still in the table field. If the table is empty, it's set to 1. You can then increase it only by adding records. Rick Sprague

 

[CindiN]

I have discovered another way to set the AutoNumber field to the value you want. This will work for a new table, beginning the first number with whatever you choose. And, it will work for an existing table that contains records so that the next new record will start at a specified field.

Here's a couple of rules before beginning:
- Make sure the NewValues Property for AutoNumber field
is set to --Increment--.
- No Primary Keys in either table...if there are,
Remove them until you have finished these steps.
- Original Table Properties:
Required - No
Indexed - Yes(Duplicates OK)
ValidationRule - Must allow Null values
(for fields or records)


1. Create a Temp. Table with a Number Field, Long Integer and use the same field name as in your original table.
2. In Datasheet view, enter a value one less than the number you want to start with in your original table. Ex. Temp. Table would be 99, if you want your original table to start with 100.
3. Create and run an Append Qry that appends the Temp table to the original table.
4. Delete the Temp Table.
5. Delete the record in your original table that the append query added.

When you add a new record to your original table, it will start with a value 1 greater than you gave in the Temp table.

If you compact your DB, AutoNumber for the next record will be reset to 1 more that the highest previous value. Therefore, before compacting, make sure to add at least one record to the table first.

In other words, if there are no values in the Table and you compact it, it will reset the value to 1. (one more than 0).
If the last record in your table was Number 50, then you Compact the DB, the next record will start with 51.

Hope this helps!
CindiN

 

[harrisharris]

To CindiN,

I have tried your method which is in line withthe HELP on Access. However, when I run the query, I get a message box prompting me for a parameter value. I havent a clue what I should do with this. Can you help? Many thanks

 

[CindiN]

I'm not sure what you have done. When you create an append query you do not enter any information that would cause that repsonse from your system.

Forgive me if I'm telling you something you already know... To create an append query, you must first have the two tables already created as defined in my last message. One Temp Table and then your original table. Go to the Query Object, select new, design view and click ok. Select only the Temp Table from the ShowTable Window and then close the Show Table window. Double click on the TempID Field in the Temp Table (should be the only field in the table), and it places it in your query grid. Go up to your toolbar and select the Query Type button, actually click on the buttons' down arrow to select the Append option. (Query Type button is to the left of the Red Explanation button (RunButton)). You should get a box that asks: What do you want to Append To...click the down arrow at the end of the white box and choose your original table, or type in the name, and click OK to the box. Then click the run button and it will tell you, you're about to append one field, click OK. Save and Close the append query. Then go to your Tables Object and open your original table, add a new record entry and it should start with number 100.

If it's something else, let me know the circumstances and I'll try to figure it out.
CindiN

 

[harrisharris]

To CindiN,

You are a star, it works. I was getting myself in a mess with the Append Query. Many thanks for your help.