insert into multiple tables

[vicsaccess]

ok, here's a new one on me but I'm sure it is common amongst the expertise here. i have an unbound entry form for requested work which then gets approved by a supervisor and moved to another table for a employee to do the work. along the employees path they have to fill out some description about the work they will do and the hours involved. this goes into a separate table related to the work table because most jobs will have several people working on it. so far fairly easy. today I'm informed that entry work with certain higher criteria must skip the approval process and go directly into the work table. again so far fairly easy. now my problem is that they want the requested work to also have default information to go into the secondary table associated with the work table. my head scratcher is that since this is an unbound entry form, i can write an insert SQL for the requested work to go directly into the work table but how do i write the second insert SQL to add the default information into the secondary table when i don't know what the primary ID is on the first entry to make it the foreign key of the secondary table. not sure i'm on the right mental path but my thought is


1. insert into work table
2. search for the highest primary key
3 insert into secondary table with the found primary key as foreign key.

not sure if this is making sence, please let me know if i need clarification and how you would handle this. thanks

[pbaldy]

More reliable is a recordset. Relevant bits from code adding to an AR Master table and then related detail:

        With rsMaster
          .AddNew
          'various field values set
          .Update
          .Bookmark = .LastModified
          lngARKey = !AR_Key  'this sets a variable to the new autonumber value
        End With

        With rsDetail
          .AddNew
          !AR_Key = lngARKey
          'and so on

[orange]

I agree with what Paul has said, but I'm wondering if your application is still undergoing "new requirements" or has been set such that you can design and test. Perhaps I'm reading more into your description than intended, but it sounds like requirements are changing and may continue to do so.
Sometimes you have to work with versions-- a,b,c... will be in Version 1 -- you can't be in constant development and have any control of design, but you know your environment better than we do.

[vicsaccess]

thanks Paul, i had just looked at a similar one by Ken Snell and was wrapping my head around it. i have done a little reading on using recordsets but i don't recongnize the rsmaster? Orange, you are and are not reading more into it. i have a running DB that is in use at my location that we are using as a test or beta version since you can't think of everything while planning/building. it works great thanks to the help of everyone here but as you know someone always come back with, its nice but it sure would be nice if it could do this? then comes the fact that i'm a tinker who is happy to learn more and apply it. i believe we will test and improve this test version for a few months and then roll it out to the other locations within our business unit.

[pbaldy]

rsMaster is simply the name of a recordset variable, as is rsDetail. Previous to that:

Set rsMaster = db.OpenRecordset("SELECT * FROM Tbl_AR_master WHERE 1=0", dbOpenDynaset, dbSeeChanges)
Set rsDetail = db.OpenRecordset("SELECT * FROM Tbl_AR_detail WHERE 1=0", dbOpenDynaset, dbSeeChanges)