Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Multi pick copy to list box (access 97 sr2 to 2003)

    I am comfortable with the current multi pick options which I am using to define the filter on a query i.e. in my forms list box I can pick multiple record values and then turn this into criteria for query. But I would like to use two list boxes and have the selection copied to or removed from the second list box. This is so that the user can see a list of chosen items rather than scrolling down a list to see what is and is not selected.
    I'm sure that Sybex had a version in their 97 developer book, but can't find my book.
    I figured it should work along same lines as the usual wizards where you can copy fields back and forth across two list boxes.
    Does anyone have link to examples/explanations?

    TIA
    Alan
    Cheshire
    UK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    <post#=282035>post 282035</post#> has a demo attached with single-select list boxes. Moving items is done by setting/clearing a Yes/No field in the underlying table.

    <post#=284472>post 284472</post#> has a demo attached with multi-select list boxes. Moving items is done by running append and delete queries on the underlying tables (one for each list box)

    Note: both demos rely on modifying tables. This is problematic in a multi-user environment.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    The trick I used once is the following:
    Add a yes/no field to the table used for the two list boxes. Name is ysnSelected.
    In the rowsource for the first listbox use a where statement with ysnSelected = False
    In the rowsource for the second listbox use a where statement with ysnSelected = True
    In the OnDouble click event of the first listbox open the a recordset with the table, find the record that is selected in the listbox and set the the ysnSelected field to True and do a requery of the two listboxes.
    You can do the same with the second listbox and set the ysnSelected field to False to unselect an item.
    In the On close event of the form, run an update query to set all the ysnSelected to False.
    Francois

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

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    Here is a different picklist demo that uses some basic ADO and doesn't rely on changing tables or using checkboxes. Take a look at it as well. It require a reference to the Microsoft ActiveX Data Object library (ADO) but it uses 2.1, which is the version that installs with Office 2000. If you don't have ADO 2.1 on your machine, change the reference to a later version of ADO.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    Thanks to all, there's a bit to go at there.
    Francois, the issue of more than one user makes it a little difficult to cater for more than one person reporting at any one time. But the check box in the table does mean that previous selection is retained.
    HansV, thanks for the zips, as you say, the modifying of tables is a ittle problematic also in the multi user environment.
    Charlotte, would the use of ActiveX not also depend on the end users environment (sorry, not too familiar with distributing ActiveX enabled db's. Cue the admonishments). i.e. would I need to make available ActiveX components as part of installation?

    Thanks
    Alan
    Cheshire
    UK

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    ADO is not an ActiveX control, it is an object library for use in VBA. At least one version of ADO is installed with Access 2000 or later, so no need to distribute it, and it is available for Access 97 too, although it doesn't work too well there.

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

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    Why don't you put the UserName (network username) into the table, in that way you can save your selections per user. You would only read the record for the particular user in the multi user environment.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    A bit late to the party on this, but if your application is split into a back-end and a front-end and deployed to user workstations, as long as you put the table in question in the front-end the multi-user issue could be handled. There are many other reasons for pushing the front-end to the workstation as well - I would seriously consider it.
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Multi pick copy to list box (access 97 sr2 to 2003)

    How good is this site!?!?!?!
    Well done guys and gals! I'm sure Santa will bring exactly what you want this year for being so good.
    From the leads in the db's I read I searched further and placed this code on the first listboxes onClick event (first list box is populated from query on all course names)
    Private Sub lstCourses_Click()
    Dim intI As Integer
    Dim strSelection As String 'string of text to go into selected list
    Dim varItem As Variant 'string of the selected text
    Dim strChosenOne As String
    With Me.lstCourses
    intI = 1
    For Each varItem In .ItemsSelected
    strChosenOne = strChosenOne & .Column(0, varItem) & ";"
    strSelection = strSelection & .Column(0, varItem) & vbCrLf
    Me.lstSelection.RowSource = strChosenOne
    Me.lstSelection.Requery
    intI = intI + 1
    Next varItem
    End With
    End Sub

    The list populates a second list box from where I can build the sql required.
    Thanks again to everyone.

    Alan
    Cheshire
    UK

Posting Permissions

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