none
Refreshing subform within a subform within a form RRS feed

  • Question

  • I have a main form that has a tab control with sub forms in each tab (subform A, B, C).

    In the one subform B, I have a dropdown that has a ID and name that I want to filter another subform B1 in subform B when I select from the dropdown (Column 1 is ID, column 2 is Name).

    I have subform control B1 with Link Master Field=MasterID and Link Child ID=ID.  The subform B1 table ID links to the subform B recordset MasterID

    The issue I am having is when I select a new name in the dropdown, the B1 subform does not change to match based on the link fields. If I go to a different record then back it changes, but not in real time.

    Normally when I use the link fields properties, I do not need to do a requery in code. ANy reason this is not working or is there a workaround?

    Here is the data structure

    Main form RS is Property Table with ID

    subform B in tab B is Listing data with PropertyID foreign key and BrokerID foreign key.

    dropdown in listing subform has ID and broker name, with control source broker ID

    subform B1 RS is broker table with ID

    Wednesday, October 9, 2019 1:33 AM

Answers

  • tblProperty.ID=tblListing.PropertyID

    tbllisting.BrokerID=tblBroker.ID

    That suggests that the model is like this:

    tblProperty---<tblListing>----tblBroker

    which, in relational database terms, means that tblListing is modelling a binary many-to-many relationship type between properties and brokers by resolving the relationship type into two one-to-many relationship types.  Colloquially tblListing is what is sometimes referred to as a 'junction table'.

    The usual interface for this would be a properties subform in single form view, within which would be a listings subform in continuous forms or datasheet view.  A third subform would only be required where the model is hierarchical, e.g. Customers---<Orders----<OrderDetails.  This can be expressed in a customers parent form as two nested subforms, in which case the orders subform would also be in single form view, or as two correlated subforms, in which case both subforms would be independently within the parent form, with the order details subform being correlated with the orders subform.

    You'll find examples of correlated and nested subforms, using Northwind data, in CorrelatedSubs.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.

    However, your model would not require a third subform at all, and the listings subform would be linked to the parent form by setting the LinkMasterFields and LinkChildFields controls to PropertyID.  The broker for each listing in relation to the current property would be represented by a combo box in the subform bound to the foreign key BrokerID column in tblListing.

    It would perhaps help us gain a better under understanding of why you feel it necessary to have nested subforms if you were to provide details of your database's model, which is most easily done by posting a screenshot of the database's relationships window, clearly set out so that the tables and the relationships between them are readily apparent, and all columns in each table are visible.  As following is the model for the CorrelateSubs demo, to which referred earlier:




    Ken Sheridan, Stafford, England

    • Marked as answer by JHarding08 Tuesday, October 22, 2019 5:00 PM
    Thursday, October 10, 2019 4:23 PM

All replies

  • subform B in tab B is Listing data with PropertyID foreign key and BrokerID foreign key.

    That seems wrong. A broker is not a property.


    -Tom. Microsoft Access MVP

    Wednesday, October 9, 2019 3:46 AM
  • tblProperty.ID=tblListing.PropertyID

    tbllisting.BrokerID=tblBroker.ID

    Wednesday, October 9, 2019 3:30 PM
  • Is there a property in a subform control that allows for requerying/refreshing of the subform when it is linked to a master form control with the link master ID and link child ID?
    Wednesday, October 9, 2019 3:57 PM
  • tblProperty.ID=tblListing.PropertyID

    tbllisting.BrokerID=tblBroker.ID

    That suggests that the model is like this:

    tblProperty---<tblListing>----tblBroker

    which, in relational database terms, means that tblListing is modelling a binary many-to-many relationship type between properties and brokers by resolving the relationship type into two one-to-many relationship types.  Colloquially tblListing is what is sometimes referred to as a 'junction table'.

    The usual interface for this would be a properties subform in single form view, within which would be a listings subform in continuous forms or datasheet view.  A third subform would only be required where the model is hierarchical, e.g. Customers---<Orders----<OrderDetails.  This can be expressed in a customers parent form as two nested subforms, in which case the orders subform would also be in single form view, or as two correlated subforms, in which case both subforms would be independently within the parent form, with the order details subform being correlated with the orders subform.

    You'll find examples of correlated and nested subforms, using Northwind data, in CorrelatedSubs.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.

    However, your model would not require a third subform at all, and the listings subform would be linked to the parent form by setting the LinkMasterFields and LinkChildFields controls to PropertyID.  The broker for each listing in relation to the current property would be represented by a combo box in the subform bound to the foreign key BrokerID column in tblListing.

    It would perhaps help us gain a better under understanding of why you feel it necessary to have nested subforms if you were to provide details of your database's model, which is most easily done by posting a screenshot of the database's relationships window, clearly set out so that the tables and the relationships between them are readily apparent, and all columns in each table are visible.  As following is the model for the CorrelateSubs demo, to which referred earlier:




    Ken Sheridan, Stafford, England

    • Marked as answer by JHarding08 Tuesday, October 22, 2019 5:00 PM
    Thursday, October 10, 2019 4:23 PM