Macros with autonumbering

[Chris]

I am trying to assign an ID number from one table into another table
through forms. Is it possible to create a command button on a form to
feed a new record with this existing number?

For example, I have multiple tables each with its own autonumber.
Each of these numbers are fed into thier appropriate fields in a master
table. A quick visual would be that I've got a table with an
autonumber for field: HA. It is formatted as HA -1, HA -2, etc. I also
have another table with PH - 1, PH -2, etc. and so forth with several
of these tables. Those in turn feed into a master table all with
similar fields but with a new "master" number (autonumber) lableing
each input as MT -1, MT -2, etc.

When I enter data into one of my individual tables and autonumber
labels it as HA - 3, is there a macro for a command button on that form
that will insert that HA - 3 into the master table as a new record,
giving it its own master ID number?

[tombsy]

Chris,
If I were building the database, i would not use Autonumbers as they can
present difficulties that non autonumeric fields dont.
You will have to write some code.

So, I would, instead, have a separate table "tblMyID"

It would contain two fields
IDType [Text 20]
ID [long]
the data would look something like this

IDType | ID
HA | 1
PH | 34
MT |12

Each time I want a new number, I increment the ID for the relevant IDType

e.g
Docmd.runSql "UPDATE tblMyID SET ID = ID +1 WHERE ID = 'HA'"
Dim iMyNewID as long
iMyNewID = dlookup("ID","tblMyID","ID = 'HA'")

This would return the number 2, so you could then use it anywhere in your
app.
hope that helps

"Chris" <ru******@ipsincorporated.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

>I am trying to assign an ID number from one table into another table
through forms. Is it possible to create a command button on a form to
feed a new record with this existing number?

For example, I have multiple tables each with its own autonumber.
Each of these numbers are fed into thier appropriate fields in a master
table. A quick visual would be that I've got a table with an
autonumber for field: HA. It is formatted as HA -1, HA -2, etc. I also
have another table with PH - 1, PH -2, etc. and so forth with several
of these tables. Those in turn feed into a master table all with
similar fields but with a new "master" number (autonumber) lableing
each input as MT -1, MT -2, etc.

When I enter data into one of my individual tables and autonumber
labels it as HA - 3, is there a macro for a command button on that form
that will insert that HA - 3 into the master table as a new record,
giving it its own master ID number?