Starting with different autonumber

[kabir_hussein]

Hi i have been trying to make one of my database tables start with a different autonumber and so far have been unsuccesful. Does anyone have any ideas on how to do this. I am sure it is easy, i have been reading the help menu but it did not help me at all.

many thanks

i am trying to start a table with the number beginnig with 100 and so on

 

[ColinEssex]

From Access help - it does work

Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.

1 Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
2 In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
3 Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.

4 Delete the temporary table.
5 Delete the record added by the append query.
6 If you had to disable property settings in step 3, return them to their original settings.

When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.

Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

Col

 

[Rich]

Since Autonumbers have no meaning other than to give a record a unique value, starting from 100 is pointless. You should define your own unique PK

 

[kabir_hussein]

thank you

hi just wanted to say after several attemtps i have managed to do it many thanks

kabir hussein

 

[kabir_hussein]

order number

cheers rich

for the advice but as i needed it for an order number i wanted to start the primary key from 1000 or so, but i couldnt do it so i asked how to do it from a 100

thanks any way

 

[ColinEssex]

Just to chuck in my opinion - others may disagree.

The autonumber is designed to be used as a unique link between tables. You should not try to use it for order numbers etc because it can become very confusing when records get deleted and so on.

If you need a unique order number, you should create a field specially for this purpose and leave the autonumber alone.

Col

 

[Pat Hartman]

I don't believe that there would be a problem in using the autonumber as an order number provided gaps are not going to be an issue. Starting this number at 1000 or 10000 makes sense because you don't want order numbers to be too short and you don't want them to have leading zeros. So picking a number of 4, 5, or 6 digits is a good place to start numbering from.

 

[rube]

Autonumbering Question

Pat, if I could throw an Autonumbering question at you?

A few years ago I hastily threw together a poorly designed database using the autonumber as my PK. After that time, you replied to a post stressing the importance of normalization, good data mgmt, etc. which I took to heart.

The problem is now is I have redesigned this old db, and put in correct design, but I cannot import the old data in, as the PKs do not match up. I just started redigning the tables, etc. with no data as there was so much bad data from poorly designed forms where I allowed incomplete, dirty forms to save, and I couldn't make relationships because of the bad data.

My question is, is there a way to "force" the PKs to the old value, as I have a bazillion subforms/tables, that I am either going to have to manually enter, or write code that will walk it through copying the data.

Any input is appreciated.

Thanks,

Rube

 

[Pat Hartman]

There is no way to change an autonumber once it is assigned but, you can import data with existing autonumbers provided they don't duplicate any existing ones. I don't know if this will help you if you have already entered data but give it a go.

Select records from the original table, including the old autonumber value and append them to the new format tables, mapping the old autonumber field to the new autonumber field. With this method, you should be able to append records from related tables that reference these old autonumbers.

 

[rube]

Normalization

Thanks for the reply, and for the post I referenced, it really did change the way we design our Apps.

Rube

 

[Pat Hartman]

Thanks rube. It makes be feel great that I had such a profound impact on your development efforts. I hope that you are happier with your apps these days.

 

[rube]

One more question

Pat,
I am not sure if you will get/see this or not, but if you would entertain one more question along these lines.

Is it better to use an Autonumber or Generated ID? The reason I ask is if you have several tables that feed off of an Autonumber, and the db crashes, and you can save some of the records and import them back to the original, it will append them, changing the number. The subordinate tables will then be off (unless I am missing something). ie PK of 233 Main table, the db crashes and the customer resinstalls inputing into a new table (the old one is corrupt), and they import 2 records, when I append the first good record of the old table, PK of 233 now becomes 3 (along with all the records that follow), and the subordinate IDs of the tables still show 233 (etc.). If I had a self-generated ID, say SMITHJ1 for John Smith, I would only have to worry about the overlaps.

Just trying to further refine, if you don't have time to look at this don't worry, I appreciate your input.

Rube

 

[Pat Hartman]

If you go back to my earlier post in this thread, you'll see that I said that you can use an append query to add data to your table and PRESERVE the old autonumber. An append query is the ONLY place that you can supply a value for an autonumber.

 

[Berggy]

I find this post to be most interesting and I hope you can solve this mystery also.

We use the autonumber in access as the ShipMemo for our company. We use numbers 10000 to 99999. I am told that the previous keeper of the database would delete the old records ( say 10000 to 60000 ) and reset the autocounter bact to 10000.

Well all attempts to do so by the "new guy" yields either access renumbering the old records also or not working at all.

Can you tell me of a way to reset the autonumber back to 10000 but leave the existing records' number alone?

 

[Pat Hartman]

If you compact your db, the autonumber will increment from the existing max value. So, if you delete all the rows, the autonumber will start back at 1. If you delete all the rows but 1 and the autonumber of the remaining row is 63242, the autonumber for the next record you add will be 63243.

 

[Berggy]

OK I have deleted records 10000 to 84069, leaving records 84069 to 94378.
I removed the relationships, the primary key, the required fields and the indexes.
I made a table Temp with one field called MemoID.
I put in the table one record of 9999.
I used an append query to insert this record into the original table and it does.
Then I deleted the 9999 record from the original table and put the primary key back.

The original table still wants the next record to be 94379.

What am I doing wrong here?

I need the next record to be 10000

 

[Berggy]

Never mind ... I got it .... found the answer in the developer's handbook for jet 4.0.

ALTER TABLE tblSMemo ALTER COLUMN MemoID COUNTER(10000,1)
or
ALTER TABLE tablename ALTER COLUMN columnname COUNTER(seedvalue, incrementamount)

I just remove the relationship on the table, run this code in the query window and then restore the relationship.

Thanks!