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

[ase001]

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

[HansV]

<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.

[Francois]

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.

[charlotte]

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.

[ase001]

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

[HansV]

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.

[patt]

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.

[WendellB]

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.

[ase001]

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