none
Help Needed for Multiple Extended cascading List Boxes Filtering a SubForm! RRS feed

  • Question

  • I am somewhat new to Access development and have been tasked to build an extensive database.  The more recent trouble I've come into is building a form that includes 5 multi-select cascading list boxes that will filter the subform.  I tried offering alternatives, but it just keeps coming down to needing to be able to select multiple values per list box that filter the next list box AND that filters the subform results.  I tried to take a similar form that was cascading combo boxes and change those into extended list boxes, but the only thing that occurs is that there are several parameter boxes that pop up from the subform query and nothing occurs to the subform.  I know that since it’s extended list boxes AND those lists are filtered that there is code that is needed, but I can’t seem to get anything to work based on code I’ve copied online.  So I need help to get me going, please!  Here's what I've got:<o:p></o:p>

    First, the tables & relationships:<o:p></o:p>

    1. TblSites: includes the fields SiteID & Site; related to tblIndex & tblStudies via SiteID.<o:p></o:p>

    2. tblIndex: includes the fields IndexID, RecordID, StudyID, SiteID, LocationID, DivisionID, DescriptionID, ComponentID, Quantity, Cost, SubTotalCost, TotalFees, TotalCost; related to tblSites via SiteID, tblStudies via StudyID, & tblStudyRecords via RecordID.<o:p></o:p>

    3. tblStudies: includes StudyID, DateAdded, SiteID; related to tblSites via SiteID, tblIndex via IndexID, and tblStudyRecords via StudyID.<o:p></o:p>

    4. tblStudyRecords: includes RecordID, DateAdded, StudyID, Location, Division, Description, Component, Cost, & SubTotalCost; Related to tblIndex via RecordID, & tblStudies via StudyID.<o:p></o:p>

    Second, the query the subform is based on:

    SELECT tblSites.Site, tblStudyRecords.Location, tblStudyRecords.Division, tblStudyRecords.Description, tblStudyRecords.Component, tblStudyRecords.ComponentLocation, tblStudyRecords.MaintenanceItem, tblStudyRecords.InstallationDate, tblStudyRecords.Age, tblStudyRecords.Lifespan, tblStudyRecords.RemainingLife, tblStudyRecords.Quantity, tblStudyRecords.Units, tblStudyRecords.Cost, tblStudyRecords.SubTotalCost, tblStudies.ProjectManagementFee, tblStudies.DesignerFee, tblIndex.TotalFees, tblIndex.TotalCost
    FROM ((tblSites INNER JOIN tblIndex ON tblSites.SiteID = tblIndex.SiteID) INNER JOIN tblStudies ON (tblSites.SiteID = tblStudies.Site) AND (tblStudies.StudyID = tblIndex.StudyID)) INNER JOIN tblStudyRecords ON (tblStudies.StudyID = tblStudyRecords.StudyID) AND (tblStudyRecords.RecordID = tblIndex.RecordID);
    

    Third, the subform is simply a form datasheet based on the query.<o:p></o:p>

    Last, the list boxes include the same SQL as was used in the form that included the cascading combo boxes, so it could be incorrect for list boxes.  Here’s the SQL:

    lboSite:

    SELECT DISTINCT tblSites.Site, tblIndex.SiteID FROM tblSites INNER JOIN tblIndex ON tblSites.SiteID = tblIndex.SiteID WHERE (((tblIndex.SiteID)=Forms!frmQryStudyRecords!lboSite)) Or (((forms!frmQryStudyRecords!lboSite) Is Null)) ORDER BY tblSites.Site
    

    lboLocation:

    SELECT DISTINCT tblLocations.Location, tblIndex.LocationID FROM tblLocations INNER JOIN tblIndex ON tblLocations.LocationID = tblIndex.LocationID WHERE (((tblStudyRecords.Location) = Forms!frmQryStudyRecords!lboLocation)) Or (((forms!frmQryStudyRecords!lboLocation) Is Null)) AND (Site = Forms!frmQryStudyRecords!lboSite OR Forms!frmQryStudyRecords!lboSite Is Null) AND (Division = Forms!frmQryStudyRecords!lboDivision OR Forms!frmQryStudyRecords!lboDivision Is Null) AND (Description = Forms!frmQryStudyRecords!lboDescription OR Forms!frmQryStudyRecords!lboDescription Is Null) AND (Component = Forms!frmQryStudyRecords!lboComponent OR Forms!frmQryStudyRecords!lboComponent Is Null) ORDER BY tblLocations.Location;

    lboDivision:

    SELECT DISTINCT tblDivisions.Division, tblIndex.DivisionID FROM tblDivisions INNER JOIN tblIndex ON tblDivisions.DivisionID = tblIndex.DivisionID WHERE (((tblIndex.DivisionID) = Forms!frmQryIndex!lboDivision)) Or (((forms!frmQryIndex!lboDivision) Is Null)) AND (SiteID = Forms!frmQryIndex!lboSite OR Forms!frmQryIndex!lboSite Is Null) AND (LocationID = Forms!frmQryIndex!lboLocation OR Forms!frmQryIndex!lboLocation Is Null)  AND (DescriptionID = Forms!frmQryIndex!lboDescription OR Forms!frmQryIndex!lboDescription Is Null) AND (ComponentID = Forms!frmQryIndex!lboComponent OR Forms!frmQryIndex!lboComponent Is Null) ORDER BY tblDivisions.Division;

    lboDescription:

    SELECT DISTINCT tblDescriptions.Description, tblIndex.DescriptionID FROM tblDescriptions INNER JOIN tblIndex ON tblDescriptions.DescriptionID = tblIndex.DescriptionID WHERE (((tblIndex.DescriptionID) = Forms!frmQryIndex!lboDescription)) Or (((forms!frmQryIndex!lboDescription) Is Null)) AND (SiteID = Forms!frmQryIndex!lboSite OR Forms!frmQryIndex!lboSite Is Null) AND (LocationID=Forms!frmQryIndex!lboLocation OR Forms!frmQryIndex!lboLocation Is Null) AND (DivisionID = Forms!frmQryIndex!lboDivision OR Forms!frmQryIndex!lboDivision Is Null) AND (ComponentID = Forms!frmQryIndex!lboComponent OR Forms!frmQryIndex!lboComponent Is Null) ORDER BY tblDescriptions.Description;

    lboComponent:

    SELECT DISTINCT tblComponents.Component, tblIndex.ComponentID FROM tblComponents INNER JOIN tblIndex ON tblComponents.ComponentID = tblIndex.ComponentID WHERE (((tblIndex.ComponentID) = Forms!frmQryIndex!lboComponent)) Or (((forms!frmQryIndex!lboComponent) Is Null)) AND (SiteID = Forms!frmQryIndex!lboSite OR Forms!frmQryIndex!lboSite Is Null) AND (LocationID = Forms!frmQryIndex!lboLocation OR Forms!frmQryIndex!lboLocation Is Null)  AND (DivisionID = Forms!frmQryIndex!lboDivision OR Forms!frmQryIndex!lboDivision Is Null) AND (DescriptionID = Forms!frmQryIndex!lboDescription OR Forms!frmQryIndex!lboDescription Is Null) ORDER BY tblComponents.Component;

    I appreciate any help you can give!


    <o:p></o:p>


    Tuesday, August 15, 2017 11:02 PM

Answers

  • Oh, and you'll also have to adjust the second listbox to use the following:

    strIndex = SimpleCSV("SELECT IndexID FROM tblIndex WHERE SiteID " & fnMultiSelect(Me.lboSite, 0) _
        & " AND LocationID " & fnMultiSelect(Me.lboLocation, 0))
    

    Sorry for the mix up. This happened when I switched from using the regular lookup tables into just tblIndex.

    • Marked as answer by R'C Friday, August 18, 2017 6:51 PM
    Thursday, August 17, 2017 5:25 PM

  • Could you provide some examples?

    The following is code from my DatabaseBasics demo which selects or deselects all items in a multi-select list box:

    Private Sub cmdSelectAll_Click()

        Dim n As Integer
        
        ' loop through all items in list
        ' and select each one
        With Me.lstContacts
            For n = 0 To .ListCount - 1
                .Selected(n) = True
            Next n
        End With

    End Sub


    Private Sub cmdClearSelections_Click()

        Dim n As Integer
        
        ' loop through all items in list
        ' and deselect each one
        With Me.lstContacts
            For n = 0 To .ListCount - 1
                .Selected(n) = False
            Next n
        End With

    End Sub

    I've never had occasion to invert the selections, but the code for this would be:

        Dim n As Integer
        
        With Me.lstContacts
            For n = 0 To .ListCount - 1
                .Selected(n) = Not .Selected(n)
            Next n
        End With



    Ken Sheridan, Stafford, England

    • Marked as answer by R'C Friday, August 18, 2017 6:52 PM
    Friday, August 18, 2017 10:36 AM

All replies

  • Hi,

    It might be easier to break down your issue into smaller problems. Which one do you want to tackle first?

    Wednesday, August 16, 2017 12:25 AM
  • That's a good thought.  I guess having each list box filter the next list box in line.  Because once all values are selected, then those values need to be passed to the subform as parameters.  So the first thing would be to get the list box values filtered by selections in previous list boxes...
    Wednesday, August 16, 2017 12:36 AM
  • Okay, if I understand it correctly, we could tackle the part of making the cascade work first (if you haven't got it to work yet).

    To make a cascading combobox work, we typically use something like this:

    Me.ComboboxRowSource = "SELECT FieldName FROM TableName WHERE ForeignKeyField=" & Me.ParentComboboxName

    Of course, this won't work now because you are using a multi-select listbox. Instead, we'll need to combine the selected items into a comma-delimited string and then use the In() clause in our criteria.

    Assuming you already know how to collect the selected items and create a comma-delimited from it, our code will then become something like this:

    Me.ComboboxName.RowSource = "SELECT FieldName FROM TableName WHERE ForeignKeyField In(" & varUserItems & ")"

    Hope it helps...

    Wednesday, August 16, 2017 2:11 AM
  • You might like to take a look at 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.

    This little demo file includes two examples for drilling down through a hierarchy by means of unbound correlated combo boxes.  One is a more complex solution which allows entry to the hierarchy at any level, the other a simpler solution in which selections are made from each combo box, starting at the topmost level of the hierarchy.

    The first thing to note is that the forms do not use subforms, but merely place the combo boxes in the header of a bound form in continuous forms view.  This is not particularly relevant to the methodology, however; it merely means that the parent form is requeried in the AfterUpdate event procedure of each combo box rather than Requerying the subform.

    The key to both solutions is the design of the form's query.  In the simpler version for instance it is:

    SELECT Counties.County, Districts.District, Parishes.Parish,
    Locations.Location, Parishes.ParishID  
    FROM ((Counties INNER JOIN Districts ON Counties.CountyID = Districts.CountyID)  
    INNER JOIN Parishes ON Districts.DistrictID = Parishes.DistrictID)  
    INNER JOIN Locations ON Parishes.ParishID = Locations.ParishID  
    WHERE (Counties.CountyID=[Forms]![frmDrillDown_Simple]![cboGotoCounty]
      OR [Forms]![frmDrillDown_Simple]![cboGotoCounty] IS NULL)
    AND (Districts.DistrictID=[Forms]![frmDrillDown_Simple]![cboGotoDistrict]
      OR [Forms]![frmDrillDown_Simple]![cboGotoDistrict] IS NULL)
    AND (Parishes.ParishID=[Forms]![frmDrillDown_Simple]![cboGotoParish]
      OR [Forms]![frmDrillDown_Simple]![cboGotoParish] IS NULL)
    ORDER BY County,District,Parish;

    The basis of this is that for each combo box the query is restricted on the value in a row equalling that of the combo box, OR the combo box being NULL.  Each of the OR operations is parenthesized to force it to evaluate independently of the AND operations.

    If a value is selected in a combo box the expression will evaluate to TRUE if the value in the relevant column equals that of the combo box, or if the combo box is NULL, i.e. no selection is made, in which case the expression will evaluate to TRUE for every row regardless of the value in the relevant column.

    In the AfterUpdate event procedure of each combo box those below it in the hierarchy are requeried and set to NULL.  The AfterUpdate event procedure of the first combo box, to select a county, for instance is:

    Private Sub cboGotoCounty_AfterUpdate()

        ' set district and parish combo boxes to Null
        ' and requery controls to show districts in
        ' selected county
         Me.cboGotoDistrict = Null
         Me.cboGotoDistrict.Requery
         Me.cboGotoParish = Null
         Me.cboGotoParish.Requery
         
         ' requery form to show locations
         ' in selected county
         Me.Requery
         
    End Sub

    The District combo box's RowSource is restricted by referencing the Count combo box as a parameter, and the Parish combo box's RowSource is similarly restricted by referencing the District combo box as a parameter.  That for the District combo box for instance is:

    SELECT DistrictID, District
    FROM Districts
    WHERE CountyID = Form!cboGotoCounty ORDER BY District;

    Note that the Form property is used here to return a reference to the current form, rather than explicitly referencing the form as a member of the forms collection.

    Ken Sheridan, Stafford, England

    Wednesday, August 16, 2017 11:04 AM
  • I've just noticed that you want to select multiple items per list box.  This is rather more difficult than making single selections in list or combo boxes.  You'll find an example of how to correlate multi-select list boxes as CorrelatedLists.zip in my same OneDrive folder.  In this demo Northwind data is used as an example, with one or more customers being selected in one list box, which restricts a second list box to those products ordered by any of the customers selected in the first control.

    The second list box is correlated with the first by rebuilding its RecordSource property in code as follows:

    Private Sub lstCustomers_AfterUpdate()

        Dim varItem As Variant
        Dim strCustomerList As String
        Dim strSQL As String
        
        With Me.lstCustomers
            If .ItemsSelected.Count <> 0 Then
                For Each varItem In .ItemsSelected
                    strCustomerList = strCustomerList & "," & .ItemData(varItem)
                Next varItem
                ' remove leading comma
                strCustomerList = Mid(strCustomerList, 2)
                strSQL = "SELECT Products.ID,[Product Name], SUM(Quantity) " & _
                    "FROM Products INNER JOIN (Orders INNER JOIN [Order Details] " & _
                    "ON Orders.[Order ID] = [Order Details].[Order ID]) " & _
                    "ON Products.ID = [Order Details].[Product ID] " & _
                    "WHERE [Customer ID] IN(" & strCustomerList & ") " & _
                    "GROUP BY Products.ID,[Product Name] " & _
                    "ORDER BY Products.[Product Name]"
            End If
            Me.lstProducts.RowSource = strSQL
        End With
              
    End Sub

    What this does is firstly build a value list of all selected CustomerID values by looping through the first list box's ItemsSelected collection.  This value list is then used as the argument for the IN operator in the SQL statement, which is assigned to the second list box's RowSource property.

    My demo only includes the two list boxes, but the process could be extended to multiple multi-select list boxes by putting similar code in the AfterUpdate event procedures of each, with the exception of the last in the hierarchy.

    This demo does not restrict a form's recordset like my combo box demo, but you'll find examples of how to do so in MultiSelect.zip in the same OneDrive folder.  This demo actually opens a report filtered on the basis of the selections, but it would be a simple task to filter an open form or subform in the same way.  The file uses two methods.  One filters the report (form in your case) by building a value list for use as the argument of the IN operator and filtering the report on this.  The other method builds the value list in the same way, but assigns it to a hidden text box control in the form.  This is then referenced by the report's query as a parameter.  However, the IN operator does not accept a variable as a parameter, so this is simulated by calling an InParam function, which is published by Microsoft.  The InParam function calls a GetToken function, also published by Microsoft.  These two functions are added to a standard module in the database

    In your case you would need either to rebuild the SQL statement for the form's RecordSource or call the InParam function in the same way.  If you want to restrict the rows returned only when the selections are made in the final list box in the hierarchy, then you would only need to do so in the case of the final list box.  If, on the other hand, you want to drill down progressively, as in my ComboDemo, you would need to do so in the case of the each list box.

    Ken Sheridan, Stafford, England

    Wednesday, August 16, 2017 11:36 AM
  • I tried your code above, @Ken, modifying it as I thought would work, but what happened is that no list appeared in the 2nd list box (lboLocation) and when I clicked a value in the 1st list (lboSite), a parameter window opened asking for the Site parameter.  Here is the code as I used it:

    Private Sub lboSite_AfterUpdate()
    
        Dim varItem As Variant
        Dim strSiteList As String
        Dim strSQL As String
        
        With Me.lboSite
            If .ItemsSelected.Count <> 0 Then
                For Each varItem In .ItemsSelected
                    strSiteList = strSiteList & "," & .ItemData(varItem)
                Next varItem
                ' remove leading comma
                strSiteList = Mid(strSiteList, 2)
                strSQL = "SELECT tblLocations.LocationID, tblLocations.Location " & _
                    "FROM tblLocations INNER JOIN (tblIndex INNER JOIN tblStudyRecords " & _
                    "ON [tblIndex].[RecordID] = [tblStudyRecords].[RecordID]) " & _
                    "ON tblLocations.LocationID = [tblIndex].[IndexID] " & _
                    "WHERE [Site] IN(" & strSiteList & ") " & _
                    "GROUP BY tblLocations.LocationID, tblLocations.Location " & _
                    "ORDER BY tblLocations.Location"
            End If
            Me.lboLocation.RowSource = strSQL
        End With
              
    End Sub

    Is there a way I can attach a sample Db to help give a better view into what I'm doing and with what?

    Wednesday, August 16, 2017 7:20 PM
  • You cannot post a file to this forum, but you can do so to a file-sharing site such as Microsoft's OneDrive, and post the link in a reply here.

    Before you do so, however, I would ask what is the data type of the Site column?  The code at present assumes that it is a number data type, i.e. a numeric key in a hidden column in the list box, which is the case in my demo.

    Ken Sheridan, Stafford, England

    Wednesday, August 16, 2017 9:43 PM
  • lboSite has 2 column fields with the ID (autonumber PK), then text.  Bound column is 1, the ID column, and is hidden.
    Wednesday, August 16, 2017 10:10 PM
  • And is the foreign key Site column in the referencing table (tblLocations?) a column of long integer number data type referencing the autonumber primary key of the sites table?

    Ken Sheridan, Stafford, England

    Wednesday, August 16, 2017 10:36 PM
  • One of them, yes.  So tblSites.SiteID = tblIndex.Site; then the tblIndex table includes FKs for pretty much all the reference tables.  I tried to explain the references in my first post, but I don't think I did it very well.
    Wednesday, August 16, 2017 11:25 PM

  • If Site is a column of long integer data type, then I can't see any obvious problem with the code per se.  I suspect the SQL statement is more complex than is necessary, however, and need not join all the tables.  The example in my demo does so because the second list box returns values from the Products table, which does not contain the column on which the second list box is correlated with the first.  In your case I think it only needs to include the tblLocations table as it is this table from which columns are being returned, and I assume it is a column (Site) in the same table on which the second list box is correlated with the first.

    I you can't crack it, post a copy of the file to OneDrive or similar, and post the link here.

    Ken Sheridan, Stafford, England

    Thursday, August 17, 2017 10:49 AM
  • OK, here's a sample Db modeled on mine...I really am stuck

    https://1drv.ms/u/s!Akvvcs6GUyjrhhQxN5tyFIGYyzP6


    Thursday, August 17, 2017 2:31 PM
  • Hi,

    I made changes to your file, but I cannot upload it back to the same file share. How can I send it to you?

    Thursday, August 17, 2017 3:30 PM
  • You don't have a onedrive or similar type account? The only other way i know of is to send it via email and I'm not too hip about sharing my email address publicly, so to speak...


    Thursday, August 17, 2017 3:52 PM
  • You don't have a onedrive or similar type account? The only other way i know of is to send it via email and I'm not too hip about sharing my email address publicly, so to speak...


    Hi,

    I don't blame you. My email address is available in my profile, if you don't mind sharing your email address with only one person.

    Also, is there a way to allow upload to your Shared OneDrive folder just until I finished uploading your file to your OneDrive folder?

    Thursday, August 17, 2017 3:54 PM
  • Let me see...
    Thursday, August 17, 2017 3:55 PM
  • You don't have a onedrive or similar type account? The only other way i know of is to send it via email and I'm not too hip about sharing my email address publicly, so to speak...


    Hi,

    I don't blame you. My email address is available in my profile, if you don't mind sharing your email address with only one person.

    Also, is there a way to allow upload to your Shared OneDrive folder just until I finished uploading your file to your OneDrive folder?

    Why don't you simply do as I always do and upload it to a location where we can all see it?  Surely one of the basic principles of a forum like this is that everybody can see what every other participant in a thread is contributing?

    Ken Sheridan, Stafford, England

    Thursday, August 17, 2017 4:04 PM
  • I'm sorry Ken, I thought everyone could see it from the link I shared.  I'm still learning.  See if this link allows you access.  I shared the folder itself with edit permissions. 

    https://1drv.ms/f/s!Akvvcs6GUyjrbPFRH-9L8nEUNQw

    Thursday, August 17, 2017 4:07 PM
  • Why don't you simply do as I always do and upload it to a location where we can all see it?  Surely one of the basic principles of a forum like this is that everybody can see what every other participant in a thread is contributing?


    Ken Sheridan, Stafford, England

    Hi Ken,

    I don't mind "sharing" as you can probably tell with the file downloads I have available on my website. The "problem" in this case is it is not my file, so I feel it should be uploaded to the "owner's" file sharing location (and not in mine).

    I guess I could make excemptions at any time, but I just thought I would give the OP a chance to find another way first.

    Hope this is okay...

    Cheers!


    • Edited by .theDBguy Thursday, August 17, 2017 4:16 PM
    Thursday, August 17, 2017 4:12 PM
  • Surely one of the basic principles of a forum like this is that everybody can see what every other participant in a thread is contributing?


    Ken Sheridan, Stafford, England

    Oh, and as far as this statement goes... This is not in any way a shot at MSDN forums, but this is one of the reasons why I like UtterAccess forum, because they allow you to upload files with your post. If the OP posts his/her question at UtterAccess, I'll be happy to post my reply with his/her file there.

    Just my 2 cents...


    • Edited by .theDBguy Thursday, August 17, 2017 4:17 PM
    Thursday, August 17, 2017 4:14 PM
  • I'm sorry Ken, I thought everyone could see it from the link I shared.

    We can.  It is theDBguy's apparent reluctance to make his amended version of your file available to all which I was addressing.

    Ken Sheridan, Stafford, England

    Thursday, August 17, 2017 4:15 PM
  • Why don't you simply do as I always do and upload it to a location where we can all see it?  Surely one of the basic principles of a forum like this is that everybody can see what every other participant in a thread is contributing?


    Ken Sheridan, Stafford, England

    Hi Ken,

    I don't mind "sharing" as you can probably tell with the file downloads I have available on my website. The "problem" in this case is it is not my file, so I feel it should be uploaded to the "owner's" file sharing location (and not in mine).

    I guess I could make exceptions at any time, but I just thought I would give the OP a chance to find another way first.

    Hope this is okay...

    Cheers!


    As the OP has suggested you upload to OneDrive or similar, I don't see any need for qualms on that score.

    Ken Sheridan, Stafford, England

    Thursday, August 17, 2017 4:18 PM
  • As the OP has suggested you upload to OneDrive or similar, I don't see any need for qualms on that score.


    Ken Sheridan, Stafford, England

    Hi Ken,

    Can't we just agree it's a matter of personal preference? I use OneDrive to store my own files (including those I share with everyone) because I don't want to maintain other people's files on my OneDrive account.

    If you want, I can email you the OP's file, and you can share it with the rest of us on your OneDrive folder.

    Thursday, August 17, 2017 4:22 PM
  • I just need some help here.  Not meaning to cause any arguments.
    Thursday, August 17, 2017 4:24 PM
  • Surely one of the basic principles of a forum like this is that everybody can see what every other participant in a thread is contributing?


    Ken Sheridan, Stafford, England

    Oh, and as far as this statement goes... This is not in any way a shot at MSDN forums, but this is one of the reasons why I like UtterAccess forum, because they allow you to upload files with your post. If the OP posts his/her question at UtterAccess, I'll be happy to post my reply with his/her file there.

    Just my 2 cents...


    Oh, and one more issue I have with this forum regarding file sharing is this:

    If I post the OP's file on my OneDrive folder, Ken is correct where anyone who reads this thread will be able to see what I did. However, somewhere down the road, I might decide to clean up my OneDrive folder to make room for other files I need to share with later forum threads. So, when another person reads this thread and tries to go to the link for the file, they won't find it anymore.

    Again, allowing users to upload a file with their posts will make sure the file stays with the conversation.

    Just my 2 cents...

    Thursday, August 17, 2017 4:29 PM
  • I just need some help here.  Not meaning to cause any arguments.

    Hi,

    I'll go ahead and upload your file to OneDrive, but I can't guarantee how long it will stay there.

    Stand by...

    Thursday, August 17, 2017 4:31 PM
  • Can't we just agree it's a matter of personal preference?
    Sorry, but no.  I feel strongly that complete transparency of all contributions to all participants is an essential part of the forum ethos.

    Ken Sheridan, Stafford, England

    Thursday, August 17, 2017 4:32 PM
  • Would my shared folder link not allow you to upload there?  I went in and tried to share the folder with edit rights.  You may have missed it when I posted...

    https://1drv.ms/f/s!Akvvcs6GUyjrbPFRH-9L8nEUNQw

    Thursday, August 17, 2017 4:33 PM
  • Can't we just agree it's a matter of personal preference?

    Sorry, but no.  I feel strongly that complete transparency of all contributions to all participants is an essential part of the forum ethos.

    Ken Sheridan, Stafford, England

    And I don't disagree. However, the forum should provide the tools to do so.

    Just my 2 cents...

    Thursday, August 17, 2017 4:34 PM
  • Would my shared folder link not allow you to upload there?  I went in and tried to share the folder with edit rights.  You may have missed it when I posted...

    https://1drv.ms/f/s!Akvvcs6GUyjrbPFRH-9L8nEUNQw

    Hi,

    No, it won't allow uploads - only downloads.

    Try my link now:

    https://1drv.ms/u/s!Ag9k7-z-047QjBW0fbw4dJwfDuR3

    Hope it helps...

    Thursday, August 17, 2017 4:35 PM
  • Again, allowing users to upload a file with their posts will make sure the file stays with the conversation.
    That is one thing I do agree with.  It is unfortunate that Microsoft does not make this functionality available in its forums.  I was doing it 20 years ago as a Sysop in the old Compuserve MS Access forum, so its hardly rocket science.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, August 17, 2017 4:37 PM Typo corrected.
    Thursday, August 17, 2017 4:35 PM
  • Again, allowing users to upload a file with their posts will make sure the file stays with the conversation.

    That is one thing I do agree with.  It is unfortunate that Microsoft does not make this functionality available in its forums.  I was doing it 20 years ago as a Sysop in the old Compuserve MS Access forum, so its hardly rocket science.

    Ken Sheridan, Stafford, England


    Hi Ken,

    I am not a difficult person to talk to. I went ahead and uploaded the OP's file to my OneDrive folder. However, OneDrive only provides a very limited space for us "free" users, so I can't guarantee the file will be there forever.

    Cheers!

    Thursday, August 17, 2017 4:39 PM
  • I got it, thank you.  So looking at your code, there's a couple questions I have.  First, I see you added AfterUpdate events to lists 1 & 2.  Does that work in conjunction with the Click events or is it a one or the other circumstance?

    Second....I've never seen code that includes these "T" references you have, such as 

    "SELECT DISTINCT T1.DivisionID, Division FROM tblDivisions T1 INNER JOIN tblIndex T2 " & " ON T1.DivisionID=T2.DivisionID WHERE IndexID In(" & strIndex & ")"

    What do the T references do & how are they utilized?

    Third, since I've got the 5 lists, can I copy the code and replace the fields for the remaining 3 list boxes?

    That's just what I've thought of so far.  This is great, thank you so much!

    Thursday, August 17, 2017 4:51 PM
  • Also, I'm not getting the full results I should see in the subform in some instances.  For example, I may choose Site 1 in lboSite, but the results filter to a record showing Site 2.  At first, I thought this because Location C was still listed in the lboLocation box and the record with Site 2 includes Location C; however, when I choose Location C the subform records show no results...
    Thursday, August 17, 2017 5:01 PM
  • Hi,

    Is it working somewhat like you were intending? I forgot to mention I only went as far as to make two listboxes cascade rather than do them all to give you some practice.

    Also, I started out doing the cascade by using criteria from all previous listboxes, but I realized using tblIndex in your setup would be easier. I hope I was correct in thinking so.

    Now, for your questions:

    1. Yes, I prefer using the AfterUpdate event, and I believe it can replace the Click events you had previously, but you'll have to test to make sure.

    2. I forgot but I think maybe the Click event does not fire until after the AfterUpdate event anyway. Again, some tests could confirm this.

    3. I'm lazy, so I used an "alias" to minimize typing the complete table name in the SQL statement. So, the "T?" is called an Alias.

    4. Yes, you should be able to copy the code pretty much to the other listboxes, but you'll have to modify the names of the objects to make them work for the particular listbox.

    5. Did you notice how I added a Textbox on the form to use as a criteria for the subform? If this works for you, you can hide (set Visible=No) it later on.

    Hope it helps...

    Thursday, August 17, 2017 5:04 PM
  • It is doing something close to what I need, yes.  But I think there's an error somewhere in the relations because, for example, I pick Site 3 but it shows Site 2 in the subform and the Index txtbox shows 4.  Using tblIndex in place of previous listbox selected values is fine with me, and that's why I built that table in the first place-so everything could be joined.

    The person who helped me earlier believed the list boxes should remain empty until a choice is selected in the first list box.  I'm not a fan of that.  Do you think that leaving that as is makes sense or would it be better so that the list boxes always show their values, regardless of whether a selection in list box 1 has made?

    I'm not going to push my luck here, but eventually, someday, I'd like it so that the list boxes cascade in either direction based upon a choice in any of the boxes.  But that's further down the line.  I just have to get this working first...

    Thursday, August 17, 2017 5:14 PM
  • Hi,

    Good catch. Try replacing this line in the AfterUpdate event of the Site listbox:

    
    strIndex = SimpleCSV("SELECT IndexID FROM tblIndex WHERE SiteID " & fnMultiSelect(Me.lboSite, 0))
    

    I had LocationID in there...

    Thursday, August 17, 2017 5:18 PM
  • Oh, and you'll also have to adjust the second listbox to use the following:

    strIndex = SimpleCSV("SELECT IndexID FROM tblIndex WHERE SiteID " & fnMultiSelect(Me.lboSite, 0) _
        & " AND LocationID " & fnMultiSelect(Me.lboLocation, 0))
    

    Sorry for the mix up. This happened when I switched from using the regular lookup tables into just tblIndex.

    • Marked as answer by R'C Friday, August 18, 2017 6:51 PM
    Thursday, August 17, 2017 5:25 PM
  • I re-uploaded your file. I am not sure if the same link will work. Please let us know...
    Thursday, August 17, 2017 5:28 PM
  •  I went ahead and uploaded the OP's file to my OneDrive folder. However, OneDrive only provides a very limited space for us "free" users, so I can't guarantee the file will be there forever.
    Thanks.  I've taken a look at the file.  You seem to have done a good job on it.  I'm a little concerned at first sight about some of the relationship types in the model, however.  Is there really a many-to-many relationship type between sites and locations for instance, as is inherent in the design of tblIndex, and in the following data?

    SiteID    LocationID
    1           1
    1           2
    3           1

    I can understand a site having multiple locations within it, or vice versa depending on the direction of the relationship, but both being the case seems rather strange to me.  Perhaps the OP could clarify this.


    Ken Sheridan, Stafford, England

    Thursday, August 17, 2017 5:49 PM
  • Sorry for my delayed reply (dealing with a sick doberman, also).  Ken, yes, there is that need.  So I may have several different sites that have different and same location.  So Site 1 may have locations named A-G, Site 2 may have locations named A-J, Site 3 may have locations K-M, Site 4 with Locations A-C.  If there is a better way to organize that relationship, I'm all ears, but so far I've been unable to get anything other than what I have to work out for what I need.

    Db, I downloaded the new file...does it have anything different other than your proposed changes?

    Thursday, August 17, 2017 5:57 PM
  • So Site 1 may have locations named A-G, Site 2 may have locations named A-J, Site 3 may have locations K-M, Site 4 with Locations A-C.


    I see, the locations table is modelling location names, not location entities, i.e. physical points on the surface of the globe.

    Ken Sheridan, Stafford, England

    Thursday, August 17, 2017 6:01 PM
  • Exactly.  Several physical locations have the same name.
    Thursday, August 17, 2017 6:03 PM
  • OK, I think this is working pretty good.  I just want to ask again if it's possible to have the lists always visible instead of not appearing until a selection is made in previous list?

    Also, is it possible to have some sort of value in the lists that allows the use to "Select All?"  It would be nice to have a "Select None" also, but I figure that would defeat the cascading effect.  Maybe I'll figure out how to have some value like that where selection would remove the listbox from the cascading chain.  Something to consider for the future.

    Last, I'm going to need something to clear the filter so I'm thinking a "reset" button of sorts.  In doing that, should the click event just be to set all the list boxes to Null?

    Uploaded the file again (includes all list boxes running the code):

    https://1drv.ms/f/s!Akvvcs6GUyjrbPFRH-9L8nEUNQw 

    Thursday, August 17, 2017 6:52 PM
  • Hi,

    If you display all listboxes' options, I think it might result in confusion for the user because items might show as highlighted but not included in the subform, which means you'll probably end up with complex code to account for all scenarios such as which listbox(es) did the user click before and what if they don't go in order, etc.

    Just my 2 cents...

    Thursday, August 17, 2017 7:32 PM
  • OK, I can see that.  Do you think it's possible to have a "Select All" option?
    Thursday, August 17, 2017 8:26 PM
  • OK, I can see that.  Do you think it's possible to have a "Select All" option?

    Yes, as well as "Reset" or "Select None."

    One way to do it is to loop through the entire list of items and either select or deselect it.

    Hope it helps...

    Thursday, August 17, 2017 8:30 PM
  • Awesome.  I'm going to give this a few more looks then I'll close the question.
    Thursday, August 17, 2017 8:53 PM
  • Just let us know if you need help with the code for "select all." Good luck!
    Thursday, August 17, 2017 8:58 PM
  • Also, is it possible to have some sort of value in the lists that allows the use to "Select All?"  It would be nice to have a "Select None" also, but I figure that would defeat the cascading effect.  Maybe I'll figure out how to have some value like that where selection would remove the listbox from the cascading chain.  Something to consider for the future.

    Hi R'C,

    In "Selection lists" I never include special selections as "Select All" or "Select None".

    Instead the users have a few buttons to set all selections, to clear the selections, to invert the selections. These buttons ar emore or less standard in each muliselect list.

    Imb. 

    Friday, August 18, 2017 5:44 AM
  • Hi lmb,

    Could you provide some examples?

    Friday, August 18, 2017 10:00 AM

  • Could you provide some examples?

    The following is code from my DatabaseBasics demo which selects or deselects all items in a multi-select list box:

    Private Sub cmdSelectAll_Click()

        Dim n As Integer
        
        ' loop through all items in list
        ' and select each one
        With Me.lstContacts
            For n = 0 To .ListCount - 1
                .Selected(n) = True
            Next n
        End With

    End Sub


    Private Sub cmdClearSelections_Click()

        Dim n As Integer
        
        ' loop through all items in list
        ' and deselect each one
        With Me.lstContacts
            For n = 0 To .ListCount - 1
                .Selected(n) = False
            Next n
        End With

    End Sub

    I've never had occasion to invert the selections, but the code for this would be:

        Dim n As Integer
        
        With Me.lstContacts
            For n = 0 To .ListCount - 1
                .Selected(n) = Not .Selected(n)
            Next n
        End With



    Ken Sheridan, Stafford, England

    • Marked as answer by R'C Friday, August 18, 2017 6:52 PM
    Friday, August 18, 2017 10:36 AM
  • Could you provide some examples?

    Hi RÇ,

    The background of my remark was to make a separation between the selection of the Items, and the cascading of the selections. This makes the cascading process simple, as it is just an iteration of:

    - get the ID's of the selected items in a comma separated string
    - use this string as to search in the FK of the next table
    - make a new selection of these records

    and continue the loop.

    The other contributors also advise this way of working.

    I can show you an example of a dynamical form that can make a selection of fields that can be copied from an external database to the current, when building a new application or extending an existing one.

    The columns use the same subform, but by setting a specific property it bebaves as a one-select-menu, a multi-select-menu, an input-menu ...

    In case of a multi-select-menu the "Selection-buttons" can be added.

    The "Deselect" button is hardly used, only when a user wants to take a second thought. The "Selectie omkeren" button gets its meaning if you want part of the fields in one table, and the rest (or almost) in a different table. Not of much use for the user, but it can ..., just a method of the multi-select-menu.

    PS. Cascading through different relations is in my applications standard available for the users.

    In any (continuous) form they can:
    -  select a specific set of records,
    - collect the ID's in an id-string,
    - select a table(query) where the table-item has a FK,
    - display these records using the id-string,
    - select a specific set of records,
    - collect the ID's in an id-string,
    - ...

    Imb.


    • Edited by Imb-hb Friday, August 18, 2017 12:15 PM typo
    Friday, August 18, 2017 12:13 PM
  • If I wanted a button to clear all list box selections on the form, how would that code?  I know that you name the control when it's a single control, but what about multiple controls?  What is used in place of Me.lstContacts, I mean...
    Friday, August 18, 2017 1:06 PM
  • lmb,

    I kind of understand what you are describing, but if you can post an example we can look at it would help more.  I guess I can think of 2 big questions with what you describe.  One, my tables are not really made in a cascading fashion because I have some things that are indirectly related.  What I did was create a "join" table that allows the tables to have a "pseudo" relation.  So I'm having a difficult time seeing how it could work if my tables and FKs are not truly cascading.  Second, if the form is that dynamic, does it confuse users (i.e., can they easily use it to get the results they need without needing to teach them)?

    Friday, August 18, 2017 1:16 PM
  • If I wanted a button to clear all list box selections on the form, how would that code?  I know that you name the control when it's a single control, but what about multiple controls?  What is used in place of Me.lstContacts, I mean...

    Hi,

    For this, you can loop through all the controls on your form and check if it is a Listbox. If so, then you can loop through the items in it and deselect each one. For example:

    For Each ctl In Me.Controls
        If ctl.ControlType = acListbox
            For x = 0 To ctl.ListCount-1
                ctl.Selected(x)=False
            Next
        End If
    Next
    (untested)
    Hope it helps...

    Friday, August 18, 2017 2:40 PM
  • If I wanted a button to clear all list box selections on the form, how would that code?  I know that you name the control when it's a single control, but what about multiple controls?  What is used in place of Me.lstContacts, I mean...

    To clear all list boxes in a form loop through the Controls collection as theDBguy describes.  If you want to selectively clear a specific subset of list boxes in a form then set the Tag property of each of the controls you want to clear to a string expression of your choice, e.g. 'SelectMe'.  You can then conditionally execute code at each iteration of the loop:

    Dim ctrl As Control
    Dim n As Integer

    For Each ctrl In Me.Controls
        If ctrl.Tag = "SelectMe"
            For n = 0 To ctrl.ListCount-1
                ctrl.Selected(n) = False
            Next n
        End If
    Next ctrl

    Vary the code as in my other examples to select all or to invert the current selections.

    I have found, however, that, if the control has an AfterUpdate event procedure, doing this with a multi-select list box can cause an untrappable error.  I have no idea why this happens or how to avoid it, or whether having other event procedures of the control would have the same effect.  The error has all the signs of corruption of the form, and I have had to restore the file from a back-up to remove the error.  You might be lucky and find that no error occurs, but I thought it best to forewarn you, just in case you experience the same behaviour.

    Ken Sheridan, Stafford, England

    Friday, August 18, 2017 4:11 PM
  • I kind of understand what you are describing, but if you can post an example we can look at it would help more.  I guess I can think of 2 big questions with what you describe.  One, my tables are not really made in a cascading fashion because I have some things that are indirectly related.  What I did was create a "join" table that allows the tables to have a "pseudo" relation.  So I'm having a difficult time seeing how it could work if my tables and FKs are not truly cascading.  Second, if the form is that dynamic, does it confuse users (i.e., can they easily use it to get the results they need without needing to teach them)?

    Hi R'C,

    The point was NOT to include "Select All" or "Select None" in the multiselect list. but to separate these things. This makes the process of finding subsequent records more transparant.

    It is not necessarily that tables are connected through PK/FK's, but it can on any field. The crux however is that each record must have an unique identifier that is collected in an id-string, and used in the sql-command for the next step. In my systematics the crux is just a softie, because every record always has an unique identifier. Is this also in your case?

    About your second: There are two kinds of dynamics. One is the same task, but looking in a complete different database. Then the appearance of the form is the same, only the vertical dimension can change, because of a different number of items.

    The other is that the form can also display an different task. In classical Access that is just a different form. I happen to use a generalized form that is tuned by the definitions of certain properties. That means in a user environment (FE) I have no forms, but little pieces of code to tune the form.

    It is not more confusing then using two different forms in conventional Access.

    Imb.

    Friday, August 18, 2017 6:12 PM