Update Related Fields of Subform When Updating Main Form In Ms Access

Question

I have a main form and a continuous subform which is connected to main form using invoice id, customer name and invoice date. When I add a new invoice, I select customer in main form and enter data in subform because I allow edits in my subform.

Now if I change customer in main form, customer should also change in subform for the data which I already added. But my subform turns to blank, when I select previous customer it shows the entered data.

I want my subform customer update to newly selected customer in main form. I used some vba code but its not working. The below code I got from a tutorial.

Private Sub Customer_Name_AfterUpdate()
With Me.[Sales_Invoice_Detail(its my subform].Form.RecordsetClone
Do Until .EOF
.Edit
!Customer_Name = Me.Customer_Name (This is the Field which i want to update in my subform)
!Invoice_Id = Me.Invoice_Id( it has one to many relationship between forms)
.Update
.MoveNext
Loop
'MsgBox "You Change Customer To " & Customer_Name & "!"
End With
Me.Refresh
End Sub

You can link the two by the ID or use .requery of the subform if it's query based. How is the form set up, have you used the form wizards linking? — Nathan_Sav
provide table definition for both forms and the relationship. — Krish
Forms are Linked With Id, Customer Name and Date. Subform is query based and have two tables. — Farhan Islam
It's going to be difficult to read the code once it gets any bigger if you don't indent it properly. — James Z

Gustav Gustav · Accepted Answer · 2018-11-15T12:56:19

a continuous subform which is connected to main form using invoice id, customer name and invoice date

Remove customer name and invoice date from the connection (MasterLinkFields/ChildLinkFields). These can be pulled either directly from the parent form or - using a query - from the source of the subform.

Update Related Fields of Subform When Updating Main Form In Ms Access

1 Answers