none
1) Combo box option depending on previous combo box value & 2) subform depending on combobox input value problem RRS feed

  • Question

  • 1) Combo box option depending on previous combo box value & 2) subform depending on combobox input value problem

    I am having these 2 problems and I have been googling for the past few days but canot solve the issue still. 

    1) Combo box option depending on previous combo box value

    What I am trying to do is to narrow the 2nd combo box choices to the 1st combo box selection. Eg: 1st combo box is a list of countries, 2nd combo box is a list of cities in the countries. User select USA in 1st combo box, than 2nd combo box is list of US states like LA, New York etc etc. 

    1st Combo box : (ComboMainParts) Main Parts List (Aka Engine, Windscreen)
    1st Data table: MainParts
    2nd Combo box: (ComboMainPartsD) Models of the Main parts List (Aka Engine Model, windscreen model)
    2nd Data table: MainPartsDetails

    I tried using the Query method to filter it based on the Combobox selection, but the 2nd combo box does not show any results.

    1) I did a after update code to change combo box 2 values when combo box 1 value changes

    Private Sub ComboMainPartsD_AfterUpdate()
    ComboMainParts.Requery
    End Sub

    2) Added a Row Source code to reference the MainPartsDetails Table and set the criteria filter

    Row Source: MainPartsDetailsQuery

    Criteria: [Forms]![MainPartsDetails]![ComboMainParts]

    2) subform depending on combobox input value problem

    I am trying to preview the information of the Main Parts Details in the same form. So when I choose the Main parts Engine >>> Engine Model : Rolls Royce Trent . I want to load the details related to that engine model like fuel used, fan blades used etc etc. That data is in the 3rd table name Engine Model details. 

    I tried using the Master and child data link but it cannot load the subform after I have selected the combo box option. 

    Any help appreciated

    Friday, January 27, 2017 2:27 AM

All replies

  • Cascading combo boxes is a frequent topic.  Your post seems to indicate you understand the principles.  Most are unaware of the need to requery the 2nd combobox after the 1st combo box selection is made.

    Really your challenge is troubleshooting.  Perhaps make stand alone query will help.  Have combo 1 select a value.  Then make a query that uses combo1 value as its criteria.  this is straight forward to test.  Let's call/save that as Q2.

    As you change a value in Combo1 and then re run Q2 - - you should see the correct results each time.  If so then make Q2 the basis/record set for Combo2 control.

    Plus of course add a vba line in the Combo1 After update event for Combo2.requery

    Friday, January 27, 2017 8:50 PM
  • You should be able to show the data from the relevant rows in a subform by setting the LinkMasterFields property of the subform control to ComboMainPartsD, or in the case of a bound form to the name of the column to which it is bound,  and the LinkChildFields property to the name of the relevant foreign key column in the Engine Model Details table.   Alternatively you could base the subform on a query which includes a parameter on the relevant foreign key column referencing the ComboMainPartsD control in the parent form, and then requery the subform control in the AfterUpdate event procedure of the ComboMainPartsD control.  Whichever you do, the BoundColumn property of the combo box must reference the primary key of  MainPartsDetails of course, so its RowSource would be along these lines

    SELECT [MainPartDetailID], [MainPartDetail] FROM [MainpartsDetails] WHERE MainPartID = Forms!YourParentFormName!ComboMainParts ORDER BY [MainPartDetail];

    Its ColumnCount property would be 2, its BoundColumn property 1, and its ColumnWidths property 0 (zero) to hide the first column.

    The ComboMainParts control must be set up similarly of course so that its value is the MainPartID, and in its AfterUpdate event procedure the ComboMainPartsD control should be set to Null and requeried with:

        Me.ComboMainPartsD = Null
        Me.ComboMainPartsD.Requery

    You'll find examples of the use of correlated combo boxes in various contexts in ComboDemo.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 its text (NB, not the link location) and paste it into your browser's address bar.

    The key thing about this demo is that it illustrates the use of such controls in the context of a correctly normalized set of tables.  In a referencing table (locations in my demo) which references a hierarchy of entity types (Counties, Districts and Parishes in my demo), only the primary key of the lowest level of the hierarchy (Parishes in my case) should be referenced in a foreign key column.  The other levels are all determined by Parish, so to include foreign keys referencing them would introduce a transitive functional dependency, and the table would not be normalized to Third Normal Form, and consequently open to the risk of update anomalies. In your case any referencing table to which the parent form might be bound should reference only the key of the Engine Model Details table, so the ComboMainParts control would be unbound regardless of whether the form is bound or not.

    In my demo, in addition the use of such controls for data entry, there are examples for drilling down through a hierarchy using unbound controls.  It's not clear from your post whether your parent form is bound or not, but if not, these would provide a model for what you are attempting, without the need for a subform.  In these the data in the form is progressively restricted by virtue of the subform's query which references the controls as parameters, and also tests for a control being Null.  With queries like this, however, do not attempt to build the WHERE clause or save the query in query design view.  If you do so Access will move things around and, at best, the logic will be obscured, or the resulting query might be too complex to open.  Always enter the WHERE clause at least in SQL view, and always save the query in SQL view.

    Ken Sheridan, Stafford, England

    Saturday, January 28, 2017 2:15 PM