Having mutiple AutoNumber type in table??

[angelandgreg]

Well, i know that you can't use the AutoNumber as the DataType for more then one ... but I was wondering if I can use something so that I can have another field also do some sort of autonumbering out of the default AutoNumber DataType?

I need to have another field in my current table with an autonumber all ready being used. This field is all ready used and linked to another table.

It needs to be a unique number for every entry as well.

Can someone please help?

I've thought about breaking up the current table as a Header and Detail tables BUT this will mean a complete rework and will take quite sometime to have to rework versus coming up with something that'll automatically generate an ID for each entry.

 

[fneily]

Nothing like trying the search feature of the forum first. But to post again:

Let’s say you want the numbering to begin with a different number other then 1. Let’s say 57. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 56. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 57. Delete record number 56.

Let’s say you want to create your own autonumber field. Create a table with a field called ID. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

Let’s say you want an autonumbered field by Microsoft but want some characters in front of the number like mc001, mc002, etc. Go to design view, click on the autonumber field and in the Format box type “mc”00
This places mc next to 2 zeros and then tacks on the autonumber.

 

[angelandgreg]

thanks.
i did search as I don't like having to relist a same post.
but did not find anything I could use.

the 2nd part with the form and incremnting, I have that and it sounds like this is the best bet. Nothing on the Table side directly then ...