Inserting Record from Multiple Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,
I have a form (form1) that is controlled by table1 (Orders), which launches
another form (form2) controlled by table2 (Items) - upon completion of a
record in form2 (and closing of form2), which adds a record to table2, I
would also like it to add a record to a table3 (shipments), using a
combination of fields from table1 and table2.
I thought about using a DoCmd.RunSQL INSERT INTO command on the closing of
form2, but how would I bring in fields from table1? Should I link table1 or
should I link in the form1 fields?
Thanks!
-gary
 
Hi -

As long as both form1 and form2 are still open (it's not clear from your
explanation whether that is true or not), you can use SQL to do the
insert, making sure your references to the form1 files are fully
qualified, thus:

insert into table3 fields ..... values( forms!form1!field1,
forms!form1!field2, me!field1,....)

where me! refers to form2.

The above is just pseudo-SQL, of course; the roal code is more complex,
to put in commas, and quotes where required.

A suggestion: put this code to append to table3 in the on click event
for a "Close" command button on form2, then use docmd.close to close the
form.

Hope this helps

John
 
Back
Top