Add record from Main form & Sub form

[jw5107]

I have a main form that holds aircraft delays. Its record source is a table. On the main form is a sub form - it holds records of aircraft parts that were needed for the delay. The sub forms record source is a query. The linking child & master fields are based on aircraft number and the airport.
I need to be able to add / save a record to another table for history purposes. Only certain aircraft delays. Like the current record the user is on.... How can I add a record from a form that has a subform to another table via code....??? Its a one to many link between the main form & the sub form. There can be 1 aircraft delay with many parts needed for that aircraft at that airport....
Any suggestions or examples....??
Thanks in advance..!!
jw5107

 

[Remou]

You can use two Append queries:

[tt]INSERT INTO tblLogMain
SELECT tblMain.*
FROM tblMain
WHERE tblMain.Key=[Forms]![frmForm]![Key][/tt]

[tt]INSERT INTO tblLogSub
SELECT tblSub.*
FROM tblSub
WHERE tblSub.Key=[Forms]![frmForm]![Key][/tt]

 

[jw5107]

Remou,

Looks like your example put the data in 2 different tables... I'm shootin' for only a few fields from the main form and a few from the sub form - into one table....
Any other suggestions...??
Thanks for the quick response...!!!
jw5107

 

[Remou]

How did you see this happening? All the problems put in one field? A separate entry for each problem with some additional data?

 

[jw5107]

remou,

When the user has the form open.. Say there is 12 records total. Say record 6 is the one the user wants to save - to a totaly different table. I need a command button that will save the current record from the main form (record 6) and all the matching records from the sub form - or record 6.....
Your example looks like to me that it puts the data in 2 different tables - "INSERT INTO tblLogMain" and then "INSERT INTO tblLogSub" - not sure how to use this example or if I'm totally missing something...
jw5107

 

[Remou]

I understand about the form, record 6 is save because:

[tt]tblMain.Key=[Forms]![frmForm]![Key][/tt]

How do you visualise saving the 'many parts' from the subform, There can be 1 aircraft delay with many parts needed for that aircraft at that airport....?

 

[jw5107]

Remou,

Ok - so what your example is showing me is that the records will have to be saved in 2 different tables because of the 1-to-many thing.. Then they'll have to be joined by the "Key" field...
I think I'm gettin' it...??? I think I'm hung up on something that won't work....!!! Duh...!!!!!
Let me implement your example & see what I come up with..
I will post back....
Thanks for the help & education..!!
jw5107