Using AutoNumber to issue specific numbers

[capnpat]

I found the post below and it is the same thing I am trying to do. I did everything in KathyL's reply but I can't figure out how to append the field from the 2nd table into the autonumber field of the first table. Any help would be appreciated.

Quote:
Originally Posted by bonbon68


I'm a newby - I set up am RMA database and am trying to set it up so that it generates a new RMA number starting from a specific number - The rma numbers were paper generated in the past and am currently on number 27234 - need it to generate numbers automatically from this point forward, I would like to be able to do it when they click on the add record button on the form

the field is currently called RMA_NO and is set up as a text field - I started to doing it; setting up a numeric field called newrma. Now I am lost. I'm not familiar with programming so please bare with me. HELP!!??? someone pleeeeeese

Reply by KathyL:
A table can have a field type of 'autonumber'. It would normally start numbering at #1. But you can append in a similar record from a 2nd table that has the field defined as numeric, with just your starting number and it should then continue numbering from that point.

[orange]

Read this
http://allenbrowne.com/ser-26.html

[capnpat]

The solution you posted my be a little over my head. Do I use the Call statement on the form for entering the data and the sub routine as a module to be run once?

[orange]

No. You don't do a call for every record.
In fact, using KathyL's comment/post:

Create a table, say MyTempTable with 1 field, say mNum of type Number
Put the value (the number you want your autonumber to start at -1) in the mNum field.
Then, your real Table - let's call it - MyTable
In this MyTable is your autonumber field called rma_no

So copy this code into a query (SQL view of a new query)

Insert into MyTable(rma_no) Select mNum from MyTempTable;

Then run the query.
Open MyTable and make sure that rma_no has the value you used in MyTempTable
Delete MyTempTable. And you can delete the query.

When you add records to MyTable from now on, the rma_no will increment by 1 (automatically)

[KathyL]

I found the post below and it is the same thing I am trying to do. I did everything in KathyL's reply but I can't figure out how to append the field from the 2nd table into the autonumber field of the first table. Any help would be appreciated.

Reply by KathyL:
A table can have a field type of 'autonumber'. It would normally start numbering at #1. But you can append in a similar record from a 2nd table that has the field defined as numeric, with just your starting number and it should then continue numbering from that point.

A simple query, done manually will do this. Define a select query using a table with one record containing a field with the starting value (e.g. 54321), and then change the select query into an append query, match the field to the autonumber field, and run the append query.

[ssanfu]

Just so you know:

"The autonumber is an internal reference number, guaranteed to be unique (except some times when using replication) and shouldn't be exposed to the user."

"Autonumbers are not meant to be "managed". They're a one time use, unique value that can be automatically assigned without fear of it being repeated. "

"AutoNumbers are intended for one purpose: to create an (almost guaranteed) unique value that can be used as a primary key. Seldom, if ever, should the value of the AutoNumber field even be shown to the user. If you care about the value of the AutoNumber field, then it's almost certain that you should be using a different data type. "


For RMA's, since they have meaning, you would be better off creating your own code to assign the numbers. You can find many examples of how to do this.

One site is "Rogers Access Library" http://rogersaccesslibrary.com

AutonumberProblem.mdb (beginner)
http://www.rogersaccesslibrary.com/forum/topic395.html

[KathyL]

Just so you know:

"The autonumber is an internal reference number, guaranteed to be unique (except some times when using replication) and shouldn't be exposed to the user."

"Autonumbers are not meant to be "managed". They're a one time use, unique value that can be automatically assigned without fear of it being repeated. "

"AutoNumbers are intended for one purpose: to create an (almost guaranteed) unique value that can be used as a primary key. Seldom, if ever, should the value of the AutoNumber field even be shown to the user.

Well, I wholeheartedly disagree with all 3 of these statements.
Just about everything in Access can be "managed". You just have to have the knowledge and experience to do so.

But everyone can have an opinion and all of our opinions are worth about 2 cents.

Kathy
I.T. Consultant

[orange]

Suggested reading on autonumbers.

http://www.utteraccess.com/forum/Aut...t-t443604.html

[capnpat]

Thanks, worked like a charm