How to search subform and use data to add record in another subform

Question

I have a main form INVOICE with subform INVOICEDETAIL(ITEMID, InvoiceID, Quantity, Price) and subform ALLITEMS (primary key ITEMID and subform readonly).

INVOICE is linked to INVOICEDETAIL via the InvoiceID field while INVOICE is not linked to ALLITEMS thus all items shows in ALLITEMS.

I want user to be able to double click items on ALLITEMS subform and the item is added in the INVOICEDETAIL subform. perhaps a dialog box pops up to let user enter also the quantity and price in INVOICEDETAIL or focus is set in INVOICEDETAIL to let user enter this information.

Can you help?

Answer 1

Perhaps something on the lines of this in the double-click event of AllItems ID control:

Me.Parent.[InvoiceDetail Subform Control Name].SetFocus
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.Parent.[InvoiceDetail Subform Control Name].Form.ID = Me!ID
Me.Parent.[InvoiceDetail Subform Control Name].Form.Price.SetFocus

Alternatively, run an append query:

strSQL= "Insert Into InvoiceDetail (ID, Price) " _
      & "Select ID, Price From AllItems " _
      & "Where ID=" & Me.ID

CurrentDB.Execute strSQL, dbFailOnError

You will then need to requery the InvoiceDetail subform and find the relevant ID.