none
Synchronized (dependent/dynamic) Combo Boxes in Access 2010 RRS feed

  • Question

  • I have searched and tried every answer I could find, but I can't get my combo boxes to work properly.  I have two tables: EventCat and EventType, EventCat has fields "ID" and "Category".  EventType has "ID", "CatID", and "Type".  I'm trying to limit the types that show up in my "Type" ComboBox based on the Category.

    I also have a subsequent set of ComboBoxes that are three deep: Region-Community-Facility and need to update them based on the previous choice.

    Can someone explain this in a way that makes sense to me? I have very little experience with coding and nothing I have done has worked!

    Tuesday, March 28, 2017 5:16 PM

Answers

  • When a control(TextBox, ComboBox, etc) receives input, it causes events to "fire". You can "catch" these events in what are called "Event handlers". You create one by using the events tab in the property sheet for the control. If you set it to [Event Procedure] and click the ellipse, Access with create a method stub in the IDE for you. I would suggest using the AfterUpdate event of the combobox to change or filter the Recordsource of your other ComboBoxes.

    For Example:
    Private Sub cbxEventCategory_AfterUpdate()
    Me.cbxEventType.Recordsource = "SELECT Type FROM EventType WHERE CatId = '" & me.cbxEventCategory & "' "
    End Sub


    • Edited by HTHP Tuesday, March 28, 2017 6:01 PM
    • Marked as answer by MWoodall81 Wednesday, March 29, 2017 4:05 PM
    Tuesday, March 28, 2017 6:00 PM

All replies

  • When a control(TextBox, ComboBox, etc) receives input, it causes events to "fire". You can "catch" these events in what are called "Event handlers". You create one by using the events tab in the property sheet for the control. If you set it to [Event Procedure] and click the ellipse, Access with create a method stub in the IDE for you. I would suggest using the AfterUpdate event of the combobox to change or filter the Recordsource of your other ComboBoxes.

    For Example:
    Private Sub cbxEventCategory_AfterUpdate()
    Me.cbxEventType.Recordsource = "SELECT Type FROM EventType WHERE CatId = '" & me.cbxEventCategory & "' "
    End Sub


    • Edited by HTHP Tuesday, March 28, 2017 6:01 PM
    • Marked as answer by MWoodall81 Wednesday, March 29, 2017 4:05 PM
    Tuesday, March 28, 2017 6:00 PM
  • Correlating two or more combo boxes works by having the RowSource of one restricted by the selection in the other.  I'll illustrate this with your tables as examples, but before doing so I would strongly recommend that you change the name of the primary key column of EventCat to from the generic ID to CatID and that of EventType to EventTypeID.  Column names should be unambiguously indicative of the attribute which the column represents.  Also primary and foreign key pairs are best given the same name wherever possible.  Adhering to these two guidelines will make any SQL you write easier to write and read.

    You should also change the name of the Type column to EventType, partly for the above reasons, but also because Type is a keyword in Access, and as such should not be used for an object name.

    Also I'd recommend changing the table names to EventCats and EventTypes.  Using plural or collective nouns for table tables reflects the fact that a table is a set.

    So, the combo box to select an event category would have a RowSource of:

    SELECT CatID, Category
    FROM EventCats
    ORDER BY Category;

    Name the control cboEventCat or similar, set its ColumnCount property to 2, its ColumnWidths property to 0cm (or inches) and its BoundColumn property to 1.  In the control's AfterUpdate event procedure put:

    Me.cboEventType = Null
    Me.cboEventType.Requery

    The first line clears the event type control of any existing value, the second requeries it so that only the types which fall into the selected category are listed.

    The combo box to select an event type would have a RowSource of:

    SELECT EventTypeID, EventType
    FROM FROM EventTypes
    WHERE CatID = Form!cboEventCat
    ORDER BY EventType;

    In this the cboEventCat control is referenced as parameter to restrict the list to those event types in the selected category.  'Form' here is the Form property and returns a reference to the current form without your having to fully qualify it by name as a member of the Forms collection.

    In the form's Current event procedure requery the cboEventType control so that its list reflects the category selected in any existing records, with:

    Me.cboEventType.Requery

    The above setup should work fine in a form in single form view, but will not do so in a form in continuous forms view.  This is because the values of the combo boxes are the hidden numeric keys, so if a category is selected in the current row, any rows where a different category has been selected will appear to have no event type selected.  This is because the hidden numeric key value which points to the visible text value will no longer be represented in the control's RowSource, and the control in those rows will consequently be unable to map to the text value.  No data will be lost, merely hidden.

    To avoid this behaviour in continuous forms view there are two possible solutions.  The first is not to use 'surrogate' numeric keys but 'natural' keys, i.e. the visible text values, in a single column RowSource.  The other, and more commonly used, solution is to use 'hybrid' controls in which a read-only text box is superimposed on a combo box to give the appearance of a single combo box control.  The form is based on a query which includes the referenced table, and the text box is bound to the text column in that table.

    You'll find examples of both solutions, and other approaches, 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.

    Ken Sheridan, Stafford, England

    Tuesday, March 28, 2017 6:05 PM
  • Thank you!  I had to take out the single quotation marks, but it is working now!  Can this be adapted to include a third combobox for a set of CBOs later on this form?
    Wednesday, March 29, 2017 4:05 PM
  • Can this be adapted to include a third combobox for a set of CBOs later on this form?
    Yes.  You'll find examples of the use of three correlated combo boxes in my ComboDemo file to which I referred you in my last post.  The demo uses the local administrative areas of county, district and civil parish in my part of the world as its example.  The first two options illustrate the use of three combo boxes for data entry, in single form and continuous forms view respectively, in the latter case using hybrid controls.  The two options for 'drilling down through a hierarchy' illustrate the use of unbound correlated combo boxes for progressively restricting a form's records on the basis of selections in the combo boxes.  One of these is more complex than the other, so you might want to look at the 'simplified' option first.

    PS:  If you are rebuilding the RecordSource properties of the lower level combo boxes in code then you must rebuild the properties of both the second and third tier properties in the AfterUpdate event procure of the top level control, and rebuild that of the third level control in the AfterUpdate event procure of the second level control.

    It is also imperative that you set the values of the second and third level controls to Null in the AfterUpdate event procure of the top level control, and the value of the third level control to Null in the AfterUpdate event procure of the second level control.  Otherwise the data in the row can be left in an inconsistent state.
    • Edited by Ken Sheridan Wednesday, March 29, 2017 5:34 PM Postscript added.
    Wednesday, March 29, 2017 5:22 PM