copy autonumber from one table to another

[Noemi]

Ttable 1 is updated when a button is clicked on my form and Table 2 is
updated from the sub form when the same button is clicked.

What I would like to do is have the autonumber field which is the primary
key in the 1st table to copy into my 2nd table once a query is run to update
the 2 table from the subform. I have been advised how to run the query from
the form but dont know how to store the autonumber which is generated as the
record is being added so I can copy the number into the second table, however
the autonumber might be duplicated in the 2nd table due to the subform has
multiple rows but they all fall under the one autonumber.

I hope someone can help me.

Thanks

 

[Nikos Yannacopoulos]

Noemi,

How are you doing the updates? Recordset operations, or SQL Append queries?
In a recordset operation, the autonumber field value is assigned the
moment you run .AddNew, so you can store it in a variable anytime after
that (and before you move to another record or close the recordset), and
use it in adding the records tothe second recordset.
In the SQL Append queries case, I'm afraid it is rather tricky; I can't
think of an easy way to do it, except by first appending the record and
then doing a DLookup based on the other fields (their values still
available form the form controls). i would opt for the recordset ops though.
If you need more detailed help, please post your current code.

HTH,
Nikos