Setting up cascading listboxes

[thegull]

So I'm trying to create a cascading set of list boxes for a search form I've created. Basically I'm trying to make the "details" listbox update it's values based on the selected category/categories in the box above.

To provide some context:
I have a search form, connected to a single record search table that stores the data I need for queries. I have created a select query that identifies the associated "details" items for each category (multiple items can be selected). I have a large table of items (the main index table) that I check against to identify all the unique "details" associated with items in that category. This table in turn draws from sub tables of categories and details (so I can easily add new categories for a "create" record form's listboxes). The query that runs to identify the "details" runs off of some build up queries (ID the category/ies selected against the category list, ID the items in the index against the category list, then match the ID's to the "details" stored in each record).

The issue is that in order to run the query I need to use the OpenQuery command it seems to run a select query, and I don't want it to open it up for the user to see, I would like to have it seamlessly flow through to the 2nd listbox (i.e. run the query in the background)... and even with the OpenQuery command method, the 2nd listbox is not showing the names of the "details" available in the listbox (but does provide the correct number of checkbox options). Any advice would be greatly appreciated. Thanks


I'm new to VBA in access (I know it in excel) and tried using the format found here:
http://bytes.com/topic/access/insights/605958-cascading-combo-list-boxes

Here is what my code looks like now:

Private Sub Categorylist_AfterUpdate()
    cat1 = "SELECT tbl_BookSearch.Category, tbl_Categories.Category, tbl_Categories.ID FROM tbl_BookSearch INNER JOIN tbl_Categories ON tbl_BookSearch.Category.Value = tbl_Categories.ID;"
    cat2 = "SELECT tbl_Books.ID, tbl_Books.Title, tbl_Books.Category, tbl_Categories.ID FROM tbl_Categories INNER JOIN tbl_Books ON tbl_Categories.ID = tbl_Books.Category;"
    DoCmd.RunCommand acCmdSaveRecord
    With Me![Category Detail]
        If IsNull(Me![Category]) Then
            .RowSource = "SELECT [tbl_Details].[ID], [tbl_Details].[Industry  /Detail] FROM tbl_Details ORDER BY [Industry  /Detail]; "
        Else
           'DoCmd.RunSQL cat1
          ' DoCmd.RunSQL cat2
           'DoCmd.RunSQL cat3
            DoCmd.OpenQuery "qry_detail4_Cat_Details"
            .RowSource = "SELECT [qry_detail4_Cat_Details].[zqry_03_Category_Search_Results].[Category Detail].Value FROM [qry_detail4_Cat_Details];"
        End If
        Call .Requery
    End With
End Sub

 

[alansidman]

Take a look at the three tutorials on combo boxes listed at this web site.

http://www.datapigtechnologies.com/AccessMain.htm

Alan

 

[thegull]

Thank you for the information there alan. I got it mostly set up, however I'm encountering a strange problem now. When I select categories in my top level listbox, the number of check boxes (for the multi-select options in my sub-listbox) changes to the correct number of options, suggesting it is pulling the right data; however, the related text (column 2 of the listbox) does not show up. My null (no top-level boxes selected) code though does display the check boxes and the names... any suggestion how to get the listbox to show the data?

One thing I'm noticing is that my query I run is outputing a text field, and the underlying field in the table is a number (to associate with the ID from the sub-table), this is probably the problem but is there any way to work around it so the form displays the text value (the ID translated to the corresponding value)...

 

[jackd]

The references Alan gave are excellent.
Suggest you review http://www.datapigtechnologies.com/flashfiles/combobox3.html
and see importance of columns, and bound column.

 

[alansidman]

the related text (column 2 of the listbox) does not show up.

In the properties for the combo box, on the format tab, set the column widths. If the text you want to see is in the second column and the PK is in the first column, set the column widths similar to 0";1" Also make sure that the number of columns in the line above represents the number of columns you have in the look up.

the underlying field in the table is a number (to associate with the ID from the sub-table),

You will have to join your primary table with the lookup table and have the lookup table field for the item in your query and checked to display. Uncheck the field that is in your primary table holding the number value.

Alan