Full Version: Change Master/child Links Using VBA
UtterAccess Forums > Microsoft® Access > Access Forms
I have a form with a sub form to manage a many to many relationship.

On the main form, the record set contains supplierID and productcatid and this is VBA based upon combo box values.

On the main form, there is a list box which contains products. A combo box has a list of ProductCategoryID with a union of -1 for All Categories which changes the SQL of this list box via VBA to show either selected category of products or all products.

The sub form contains supplierID, productid, productcategoryid, product description and price. This sub form needs to be linked to the main form by:-

1. SupplierID if the product category combo is -1 to show all the products for that supplier


2. SupplierID and ProductCategoryID if the product category combo on the main form is set to any other value to show products for category and supplier.

From an event on the main form, how do I change the linking fields between just SupplierID and then SupplierID & ProductCategoryID

The me.mysubform.linkmasterfields="SupplierID" only sets one field. What is the syntax to set two child and two master fields?.

Larry Larsen
Hi John

Could be as simple as:

me.mysubform.linkmasterfields = "FieldName1, FieldName2"
me.mysubform.linkchildfields = "FieldName1, FieldName2"

Remember to have the correct number in both..
As I recall I had issues error message while trying to set these properties in code, however the property WAS set. Its been quite a while, but when I look at my code, this is the syntax I use which doesn't give an error message:
            Me.SubContainer.SourceObject = ""
            Me.SubContainer.LinkChildFields = "DocumentLineageID"
            Me.SubContainer.LinkMasterFields = "DocumentLineageID"
            Me.SubContainer.SourceObject = "frmComment"
in the alternative, couldn't you just set a filter on the subform?
Well a filter is not the same. When you set the Master/Child relationship it also ensures that new records created are given the right ParentID. A filter might result in the correct records created, but creating new records with the subform would fail.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.