Update records in subform from parent form

Hello everyone,

I've searched through threads and I've not found exact answer I'm looking for. I am new to VBA and I hope someone could enlighten me on my problem.

I have a "CustomerOrder" form with "OrderDetail" subform, linked together by OrderID. The main form has a Order ID listbox and when clicked, it filters the customer Order and also the subform with corresponding OrderIDs.

The main form also has an "INVOICE" button and when the "INVOCE" command button is clicked, it places the OrderID into the Invoice Number field. What I want to accomplish from this point on is that when the "INVOICE" button is clicked, I want it to also update records in the subform with newly generated invoice (OrderID) number into the InvoiceNumber field.

I've tried following code but the code only updates the first record. I'm stumped.

I've attached link for the snapshot of the form. Form Snapshot
Can anyone help me with this task? Please see the code below:

Thanks!!!

Private Sub cmdINVOICE_Click()
Dim rs As Recordset
Set rs = Me.[OrderDetail subform].Form.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
With rs
.Edit
[OrderDetail subform].Form![InvoiceNo] = [OrderID]
.Update
End With
rs.MoveNext
Loop
MsgBox "A Total Of " & rs.RecordCount & " Records Were Updated"
Set rs = Nothing
Me.Refresh
End Sub

OrderID and InvoiceNumber fields are in same table? Why two fields with the same value? Why have InvoiceNumber also saved in the child records? This is duplication of data. If the tables are related by the OrderID then InvoiceNumber can always be retrieved in query. But the OrderID and InvoiceNumber are the same so why is this necessary?

The invoice # and Order ID are in same table. In most cases, the OrderID becomes the invoice number. However, sometimes an order is shipped out separately and the invoice # will have "-1", "-2" etc.

For example, if OrderID of 1001 is received with 10 different items and if I can only ship out 5 items, the Invoice # will be 1001. When I ship the remaining items, the subsequent invoice # will be 1001-1.

My recommendation would be not to use orderID as the invoice number. If orders have to be partially shipped and billed then each shipment and billing would be entirely new invoice number. Each invoice would list the items from purchase order that are billed. Ideally, I think would open an Invoice data entry form, select the customer purchase order to bill and then select the items to bill. However, sounds like this would be a major modification of database.

Code updates only first record because refers to the subform instead of recordset.

Might help to explicitely declare the recordset as DAO.
Dim rs As DAO.Recordset
or eliminate the recordset

Private Sub cmdINVOICE_Click()
With Me.[OrderDetail subform].Form.RecordsetClone
Do Until .EOF
.Edit
!InvoiceNo = Me.OrderID
.Update
.MoveNext
Loop
MsgBox "A Total Of " & .RecordCount & " Records Were Updated"
End With
Me.Refresh
End Sub

Thanks for your input. However, using entirely different invoice number system is not my call. The company have been using the order number as invoice number for years and if the orders are broken up, they just add "-1", "-2" etc.

I just have to program it that way..

Thanks again for your suggestion and I will tryout your code!

Hey roh - did you ever find a solution? I'm facing the same issue. I've tried both of June's suggestions, but neither fixed it.

I've tried both of June's suggestions, but neither fixed it.URL
URL
URL
URL

I've tried following code but the code only updates the first record. I'm stumped.URLURLURLURL

Sorry, guys, the code works for me but only once after the form first opens. A minor change allows the code to execute any number of times.

With Me.[OrderDetail subform].Form.RecordsetClone
.MoveFirst
Do Until .EOF

Would have to know more about your data and form structure and analyse your exact code.

Should start a thread on your issue. Can include reference to this thread.