Copy data from record in subformA to SubformB

[liddlem]

Hi there
I would like my users to be able to view stock items in one (sub)form and when they double click on an item, then certain data is copied to an orders (sub)form.

I have therefore embedded two unbound subforms into a master form.

The master form (Frm Stock) has no fields, but it has 2 subforms.
Frm_Stock_Subform and Frm_OrderDetail_Subform

So I have 2 problems here.

Problem 1
I would like the following code trigger when the user double clicks anywhere on the record that the user wants to order.
Thus far, I can only make it fire only when the user doubl;es clicks in a specific field (in this case, the ID field is the trigger)

Problem 2
I cannot activate the Frm_OrderDetail_Subform in order to create the new record. (I have looked at this link - Refer to form and subform

Or should I consider another solution?

Private Sub ID_DblClick(Cancel As Integer)
On Error GoTo Err_ID_DblClick

Dim MyOrderID As Variant
Dim MyStockID As Variant
Dim MyAmt As Variant
Dim MyDesc As Variant
Dim MyAccCde As Variant
Dim MyVendID As Variant
Dim MyVendName As Variant


''pick up the field values from the Frm_Stock_Subform
MyOrderID = Me.ID
MyStockID = Me.ItemCode
MyAmt = Me.VendorPrice
MyDesc = Me.Description
MyAccCde = Me.Acc
MyVendID = Me.VendorID
MyVendName = Me.VendorName


''Make the target (Frm_OrderDetail_Subform) active to append values.
' - - - - -NONE of the following 5 lines of code works for me
Me!Frm_Stock_subform.Form!Frm_OrderDetail_subform!OrderID.SetFocus
Me!Frm_OrderDetail_subform.Form!OrderID.SetFocus
Me!Frm_OrderDetail_subform!OrderID.SetFocus
Forms!Frm_OrderDetail_subform.OrderID.SetFocus
Forms!Frm_Stock!Frm_OrderDetail_subform.OrderID.SetFocus

'Add a new blank record
RunCommand acCmdRecordsGoToNew

'' Update the values
Me.ID = MyOrderID
Me.ItemCode = MyStockID
Me.VendorPrice = MyAmt
Me.Description = MyDesc
Me.Acc = MyAccCde
Me.VendorID = MyVendID
Me.VendorName = MyVendName

'' Set focus to the QTY field so that user can type in the quantity needed
Me.Quantity.SetFocus


Exit_ID_DblClick:
    Exit Sub

Err_ID_DblClick:
    MsgBox Err.Description
    errnum = 0
    Resume Exit_ID_DblClick
End Sub

Thanks for assistance

 

[MarkK]

For problem 1, you can put a double click handler in every textbox in the record, and get them all to do the same thing. You can also use a button.

For problem 2, you need to start to learn how to use recordsets to work with data in tables. You want to be able to open a recordset and move data into, or read data out of, a table, and this will become simpler and faster than using forms. Then, when all your work with data is done, then you open a form to show your work, or let users interact with it.

hth,

 

[liddlem]

Thanks Lagbolt
Hmmm - After posting, I was wondering if I couldn't solve problem 2 as follows.
And would this be the same as working with recordsets?

Instead of 3 forms, limit to 2 forms - Namely, StockItems (Parent) and Orders (Subform)
On the Parent Form, create a hidden field (called MyStockId)

On doubleclick, update the value of MyStockID to the StockId.

Create a query (called Qry_AddToOrder) that selects data based upon the MyStockID field like
Select (relevant field names) from Stock where StockID = MyStockId

Then launch an append query (Qry_AppendToOrder) that updates the Orders form from the Qry_AddToOrder.

 

[MarkK]

Tables you need for that kind of system, where there are orders and each order has quantities of products . . .

tblOrder
OrderID
CustomerID
Date
PO

tblOrderDetail
OrderDetailID
OrderID
Quantity
ProductID
UnitCost

. . . so two tables with a one-to-many relationship, and the UI will be a form with subform.

Then you have ...

tblProduct
ProductID
Product
Description
Price

It sounds like you want to make an interface that allows a user to select products from the Product table, and add those to an order, but you'll have to have a record in a table the represents that order object, and when you add product you'll need to be able to link that via the order detail table, which connects the product to the order.

Does that make sense?