Ms Access SubForm Update When Main Form Update

Question

Hello

I have a main form which have a field customer name and a subform which also have a field customer name, both forms are linked with invoice id, customer name and invoice date. thes forms have one to many relationship using invoice id. when I select a customer in main form and add design through subform. subform add design for   the same customers which I selected from main form, now if I change customer in main form all the designs which I already added in subform should also update customer. both forms have same invoice id. I want the subform update customer for all records  when I change customer in main form. I got an access vba code but that's not working. kindly help. 

Thanks

Answer 1

There are two issues here:

1.  The parent form is presumably based on an Invoices table or similar, in which the customer is a foreign key.  If you are really using the customer name as a foreign key of text data type, personal names can legitimately be duplicated so are unsuitable as keys.  However, if you have used the lookup field wizard when designing the table, while you see the customer name in the table and in the parent form, the field will in fact almost certainly be a long integer number data type referencing the primary key of a referenced Customers table or similar.

2.  The subform's table will, I assume, be based on an InvoiceDetails table or similar.  This table should contain an InvoiceID foreign key column, but must not contain a Customer column.  A Customer column would not be transitively dependant on the primary key of the table, which would therefore not be normalized to Third Normal Form, and consequently be open to update anomalies such as that which you are experiencing when selecting a different customer in the parent form.  There is no need to include the customer name or invoice date in the subform's table as each row in that table maps to a row in the parent Invoices table via the InvoiceID keys.  In other contexts than the form/subform, e.g. in a report you would simply need to join the tables on InvoiceID in a query used as the report's RecordSource.

For an example of an invoices form you might like to take a look at InvoicePDF.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file is primarily intended to illustrate how to output a report as a PDF file, but happens to use as its example a simple invoicing application.

Answer 2

In a correctly designed database the customer name should appear only once, in a column in a Customers table.  Elsewhere the customer should be referenced by their Customer_ID value, this being the primary key of the Customers table.

The Invoice_Details table or similar, on which the subform is based, should not have Customer_Name or Invoice_Date columns, only the foreign key Invoice_ID column which references the primary key of the Invoices table.  Nor should the Invoices table have a Customer_Name column, only a foreign key Customer_ID column referencing the primary key of a Customers table. In broad outline the tables should be like this:

Customers

….Customer_ID  (PK)

….Customer_Name

….etc

Invoices

….Invoice_ID  (PK)

….Customer_ID  (FK)

….Invoice_Date

Products

….Product_ID  (PK)

….Product

….Unit_Price

and to model the many-to-many relationship type between Invoices and Products:

Invoice_Details

….Invoice_ID  (FK)

….Product_ID  (FK)

….Unit_Price

….Quantity

The primary key of the last table is a composite of the two foreign keys Product_ID and Invoice_ID.  Note that both the Products table and the Invoice_Details table have Unit_Price columns.  This is because prices change over time, but while the values in Products will change, the values in Invoice_Details should remain fixed as those at the time the invoice was raised.  Consequently, in each table Unit_Price is functionally determined solely by the whole of the table's primary key.  The table is thus normalized to Third Normal Form at least, with no redundancy and no consequent risk of update anomalies.

The relationships, each of which should be enforced, are as below:

Customers---<Invoices---<Invoice_Details>----Products

The many-to-many relationship type between Invoices and Products has been resolved into two one-to-many relationship types.

The only code necessary is to assign the current Unit_Price value from Products to the Unit_Price column in Invoice_Details.  This is normally in the AfterUpdate event procedure of a combo box in the subform bound to the Product_ID column in Invoice_Details, e.g.

Private Sub Product_ID_AfterUpdate()

    ' assign current unit price from Products table

    ' to Unit_Price column in Invoice_Details table

    Me.Unit_Price = Me.cboProduct_ID.Column(1)

End Sub

The properties of the combo box should be:

Name:                  cboProduct_ID

ControlSource:    ProductID

RowSource:         SELECT Product_ID, Unit_Price, Product FROM Products ORDER BY Product;

BoundColumn:    1

ColumnCount:     3

ColumnWidths:   0cm;0cm;8cm

The exact final dimension of the ColumnWidths is not critical so long as it is at least the width of the control.  The important thing is that the first two dimensions are zero to hide those columns and show only the product name.  As the Column property is zero-based, Me.cboProduct_ID.Column(1) in the above code references the second column, Unit_Price.

With a correct design, as outlined above, a different customer can be selected in the Invoices form, merely by selecting a different row in the Customer_ID combo box.  If it is necessary to add a new customer this should be done in a form bound to the Customers table.  This form can be opened from the Customer_ID combo box in the Invoices form by setting the combo box's ListItemsEditForm property to the name of the form bound to the Customers table.  When a value not currently in the combo box's list of names is typed into the combo box the relevant form will be opened.  The same can be done with code in the combo box's NotInList event procedure, which has the advantage of passing the new name to the form via the OpenArgs mechanism.  For examples of the NotInList event procedure's use in a number of different contexts take a look at NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

Answer 3

Thanks For Your Reply. i am retyping my problem with detail. 

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. Kindly help. 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 sub form)
!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

Kindly any one suggest me some different code which can help.

Answer 4

What is the Recordsource of the main form and the subform? 

Your post is somewhat confusing. If the main form is bound to the Customer table, and the subform to the Invoice table, as it sounds like, the link should be on CustomerID. The subform should show all invoices for the Customer.

If the main form is bound to the Invoices table., then I'm not sure what the Subform would be showing. 

Pleas answer my question above and explain the purpose of this form.