Results 1 to 5 of 5
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Have one Multi Select Listbox Drive Another (A2k SR1)

    I am using the following scenario for one MultiSelect listbox to drive the selection available in a second
    MultiSelect listbox. Could those out there please review this and provide comments. Is there a better
    way to do this? Are there any issues with setting the Rowsource as I did. I don't typically do this in
    this manner but find that the code was much shorter and easier to do than some other ways that were done.

    Thanks for your suggestions.


    <pre>Scenario: A form contains two multi-select Listboxes, Listbox1 and Listbox2.
    You want the selection made in Listbox1 to drive the data available to select
    in Listbox2.

    For example. Listbox1 allows the selection of Year(s). Listbox 2 then shows the
    available months, January through December, for the year(s) selected in Listbox1.

    Note: A table exists that stores all available Years and Year Month combinations.
    Query_GetYearAndPeriod returns all of the data from this table.

    The following example does the following:
    - User makes a selection in Listbox1
    - Code generates a sql statement based on the data selected in Listbox1
    using the After Update event of Listbox1.
    - The Rowsource of Listbox 2 is set equal to the Sql Statement generated
    from the selections made in Listbox1.


    Place the following code in the After Update event of Listbox1

    Private Sub Listbox1_AfterUpdate()
    Dim ctl As Control
    Dim varitem As Variant
    Dim strSql As String

    'Set the control (ctl) to Listbox1
    Set ctl = Me!Listbox1

    'strSql is generated based on the Listbox1 selections. Use to drive the_
    'RowSource of Listbox2
    'Query_GetYearAndPeriod returns all Year and Year Month_
    'Combinations from a table.
    strSql = "Select Period, Year, YearMonthText from_
    QUERY_GetYearAndPeriod where [YEAR] ="

    'Complete the WHERE portion of the Sql Statement strSql_
    'based on the items selected in Listbox1
    For Each varitem In ctl.ItemsSelected
    StrSql = strSql & ctl.ItemData(varitem) & " OR [YEAR]="
    Next varitem

    'Trim the strSql statement of erroneous characters, i.e., or [year]=
    strSql = Left$(strSql, Len(strSql) - 11)

    'Reset the RowSource of Listbox2 as follows:
    Me.Listbox2.RowSource = strSql
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Have one Multi Select Listbox Drive Another (A2k SR1)

    <P ID="edit" class=small>(Edited by patt on 14-Feb-03 10:56. Added comment)</P>Rather than building up a series of Year = nnn OR Year = etc, you could setup:

    Year in (nnn,nnn,nnn,...)

    You will end up with a shorter SQL string this way.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Have one Multi Select Listbox Drive Another (A2k SR1)

    Like Pat, I would suggest you build an "IN" list instead of a bunch of OR conditions. I'm not sure why you're refreshing the form, since it doesn't appear that there is any data involved and you don't need a refresh to repaint the listbox.
    Charlotte

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Have one Multi Select Listbox Drive Another (A2k SR1)

    Thanks Patt. I will make the change as suggested.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Have one Multi Select Listbox Drive Another (A2k SR1)

    Thanks Charlotte. The refresh has been removed.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •