Access: How to get autoID in updatequery after insert query

[Crams]

Access 2003.

First:
I like to create an all in one query that:
- create a new record in a table
- retrieve the autonumber ID of that new record
- use that value in a update query for another table.

If such a all-in-one sollution is not possible:

how can I create a solution without using VBA and the maximum of
possibilities of queries themself and a minimum of Macro stuff. I think this
must be possible by answering the next questions.

After a insert query:
How to get autonumberID (using a query) and directly use it as a subquery.
The retrieved value is then used to set a field in a updatequery.

 

[Allen Browne]

Access will not just pop up the new autonumber value for you without any
code or macro.

If you are feeling brave, and you are the only user in this database and no
other processes are running and the autonumber seed has not been damaged and
it is not a random autonumber, perhaps you could use a DMax() expression to
get the highest value in the autonumber table and assume it is the one that
was assigned.

If you want to do it reliably, you will need to use VBA code, but I assume
there's no point posting an example of that for you.

 

[miss031]

If a person wanted to use VBA to do this, how would they? I am inserting a
record into a table, and I need that PK autonumber ID as a FK in another
table. I have no way of linking a subform on my form. Hopefully you can
help!

 

[strive4peace]

So we can tailor the VBA for you, please tell us:

the name of your main table and the name of the autonumber field

the name of the related table and the name of the foreign key that
related to the main autonumber field

also, you said you wanted to UPDATE the related table ... do you mean
add a new record or change a record that is already there?



Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*