Add data from one append query to two tables

[CatchinOn]

I have an append query where i would like to add the data from the result
into two tables. I am totally stuck on where to begin. I have tried to
alter teh SQL, but i am getting nowhere.. Any help is greatly apprecieated.

-H-

 

[Allen Browne]

You will need to execute two append queries.

 

[CatchinOn]

Thanks for the reply allen. I am trying that route too but with no luck.

I have the two append queries obtaining the correct information, and I want
load the information from the first query into a newly created record on an
order form and the information from the second query to load into a subform
on the same new order form.

I have the information from the first append query loading into a new order
form can't get the information from my second append query (that gets the
info from the subforms) to load into the subform on my newly created order
form.

Basically i think i am trying to take the information obtained from the two
append queries to load the new record information onto a form, and a subform
of the same record?

Thanks so much for the help...
-H-

 

[Allen Browne]

So, you need to know the new primary key value from the first append, in
order to use that value for the records in the 2nd append?

My preference is to use a Recordset to append the first level records,
because this makes it dead easy and reliable to get the value you just
added. You can then execute an append query statement to add the related
records.

Here's an example:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html
It assumes you have selected the record in the form that you want to
duplicate, so it performs an AddNew and Update on the RecordsetClone of the
form.

If you don't have a form open, you will need to OpenRecordset on the desired
table. If that's new, here's how:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

Once you have the recordset open and the first record appended, read the new
value (as shown in the first example above.) You then have the number you
need to create the append query string to add the related records (again,
first example above.)

If there are further records to add to the original recordset, use MoveNext
to get to the next one inside a loop until you have them all.

 

[CatchinOn]

Allen, thanks agian and i think we are getting somewhere but still a little
off. I currently have the first append query working correctly and opening
into a new record on the form with an new and unique orderID number, but i
can't get the subforms to link up. I get the correct information from teh
subform append query, but can't get them loaded into the subform on the newly
created record.

 

[Allen Browne]

If you are successfully writing to both tables (the main one, and the
related one), and you can see the correct foreign key value in the table but
it doesn't show up in the form until you close and reopen it, then you have
a timing issue here.

You may be able to solve that just by reorganising your code, e.g. don't
move to the new record in the main form until after the related records have
been created (in which case they should show.)

If you can't get that working, Requery the subform after the append query so
it learns about the new records.

If you cannot verify that the right records are actually in the tables, you
have a deeper problem to solve.

 

[CatchinOn]

Allen thank you once again, and it may be a timing issue. I'll throw around
some different scenarios and let you know if i still have problems. It all
seems to be putting the data in teh right places, so it seems like it may be
a timing thing.

Thanks again,
-H-

 

[tina]

and, Allen, if you find you need to post again to this thread, you might
want to read the other thread re this issue that the op had going in this
same ng, with subject "copy data from QuoteForm & QuoteSubform to OrderForm
& OrderSubfor", starting date of thread was 2/14/09.

hth

 

[CatchinOn]

Allen and Tina,

Sorry for posting tis message too. I'm new to these forums and didn't knwo
to reply to my same thread when i started. I am so close to getting this
right it is totally killing me. I posted another issue i found yesterday
and i'll paste it below because i'm not sure which thread to keep usinig.

HEllo agan,

After running some mock orders to make sure it was all working correctly I
noticed a problem (of course). When I add orders through the Quote_Form's
button "Add As Order" that we set up to run the append quieries then open the
new record in the order_form/order_subform it works like a charm.

And also when I go to add a new order simply by opening the
order_form/Order_subform to create a new order without a quote it works like
a charm also.

But if I have more than one quote for the same customer, and I try to "Add
As Order" another quote, everyting goes as we want and the new order is
created with an unique OrderID, but everything listed in order_subform (all
the materials ordered) is also appended to and shows up in all the earler
orders. Every new order I add through the quote_form puts all the materials
just ordered correctly into the new order, but also adds the newly ordered
materials into all the client's existing orders.

This is really wierd because if i go back to create a new order again by
opening the order_form/order-subform and create a new order, it all works
correctly, so i know it has something to do with the append quieries, but i
can't seem to work it out.

I would be grateful for any ideas. I'm so close it hurts...

-H-