Shared subform table

[Snoopy33]

I posted this problem several months back and although I received a
lot of help on it, I wasn't able to get my problem resolved. I'm
starting fresh now and would like to give it another shot. Here is my
problem put as simply as possible:

I'm working with three tables: "RMAGEN" fields are ID and Customer
"OrderInfo" fields are ID
and Customer
"Products" fields are ID,
OID, RID and ProductDescription

*This is scaled down, but I can expand later. I just want the
basis of the solution

I have two forms.... RMA and Order both of which share the Products
table as a subform. subform uses the RID and OID fields as linked
fields

When entering an ORDER, there could be 1 or MULTIPLE items in the
Products Subform.

Sometimes we need to get the core back when sending out a product(s).
I'd like a button on my Order form that i can click and create a new
RMA by passing the related fields (Customer, and each of the subforms
instances of ProductDescription) to a new RMA record.

Passing the "Customer" to the new form is easily accomplished. I
don't need help with that. The problem is in passing multiple items
in the subform from ORDER to create multiple items for the subform
from RMA.

Thanks to everyone for your assistance! This group is an invaluable
learning tool!

 

[Snoopy33]

OK,

I managed to resolve the issue myself. It seems to be working
anyway.

I made an update query like this:

UPDATE product SET product.rid = Forms!RMA!ID
WHERE (((product.oid)=[Forms]![Order]![ID]));

and put this code behind the On Click event of my command button

Private Sub MakeRMA_Click()


' So my users don't have to answer the update table query
messages
DoCmd.SetWarnings False

' So the main form get data to actually have an ID number for
the query to look at

DoCmd.OpenForm "rma", acNormal, , , acFormAdd, acHidden
Forms.rma.customer = Forms.order.customer

'the rest is running the update, making the rma visible and
turning warnings back on
DoCmd.OpenQuery "pq"
Refresh
DoCmd.Close acForm, "order"
Forms.rma.Visible = True
DoCmd.SetWarnings True

End Sub

I've actually got more code to prevent duplicate RMA's being created,
but that's the jist of it. Like i say, it seems to work ok.

Hope that helps someone else.