MS ACCESS Form Autofill multiple comboboxes based on original selection from a combobox.

Posted on 2011-10-14
Last Modified: 2013-11-28
The attached spreadsheet has been imported into access as a table called qv_categories. I created a form that a user must select which section between compliance or procedural, then the next field "category" would be prefilled in a combobox with categories tied to the section chosed. The same would go for the subcategory. Once the user selects all three fields, the data should be updated into the qv_category table. My issue is that there are multiple rows for the compliance and procedural sections. So when I create a combo box from the table, there are 16 rows in the combo box with multiple compliance and procedural records. How can I limit this two just one compliance and one procedural, then have another combobox updated with the corresponding category from either on chosen.

Question by:dplowman
    LVL 119

    Expert Comment

    by:Rey Obrero
    take a look at this
    Cascading Lists for Access Forms
    LVL 2

    Accepted Solution

    First off, make sure you have the comboxes set with the correct control source, so this would be the name of the field the data will be stored in within your qv_category table.

    Now you want to ensure the rowsource of each combox is correct. The rowsource is the list of options you have to choose from, there are a number of ways in which to do this. If you enter in design mode your combox properties you will see under the data tab a property called 'row source type'. For a small set of values that will never change it is useful to use value list, this you can simply type into the 'row source' property in design time and it will be set. This would probably be best for your first box for Compliance/Procedural.

    To enter the row source as a value list simply enter the values you want separated by a semi-colon so it would look like this


    Now the rest of the boxes are reliant upon this selection, so they need to be queries.

    This will depend where the data is coming from. It is most likely to be either from the table qv_category itself or from a separate lookup table. I will assume it is from a lookup table called qv_category_lookup since you have not clarified this yet.

    The row source would be a query where the criteria is the current value of the previous combox

    Instead of setting it at design time with something like this
    SELECT [category] FROM [qv_category_lookup] WHERE [section]=[forms]![myQVform]![section];
    you would find it needs to be updated with the new data every time it is due to change, there is an easier way

    Create an event in the first combox (where you choose PROCEDURAL/COMPLIANCE) for "After Update" and enter the following

    Private Sub cboSection_AfterUpdate()
      me.cboCategory.RowSource = "SELECT [category] FROM [qv_category_lookup] WHERE [section]='" & me.cboSection.Value & "';"
    End Sub

    Open in new window

    You could then easily adapt this to create a similar procedure to update the second combox based on the value of the first.

    If you want to be extra-clever, try setting all except the first of the comboxes to enabled=false in design time then as they are updated set enabled=true on the next combox while it's rowsource is being updated at the same time.

    Hope this helps and solves your problem.

    Feel free to ask for explinations of any part of it, or if I have misunderstood you please explain further and I will be happy to correct myself.


    Author Comment

    Thanks for the quick reply. I will try to look at this over the weekend and let you know if I have any questions early next week!
    LVL 2

    Expert Comment

    Ok, enjoy your weekend, I sure will be! It's curry night :)

    Author Comment


    I placed the below code in the first combobox with the compliance and procedural values.

    Private Sub cboSection_AfterUpdate()

    Me.cboCategory.RowSource = "SELECT [category] FROM [dbo_qv_categories$] WHERE [section]='" & Me.cboSection.Value & "';"

    End Sub

    When I select the category field from the second combobox, the fields are empty.What should I be placing in the row source for the category combobox?

    Author Comment

    Perfect again! Not sure what the issue was, but I deleted the category combobox and recreated it. Your process works perfect! Stay tuned, because I may need some help creating a call monitoring form!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now