Populate List box from 1st List Box using Multi Select

[CMPaccess]

I currently have a form with two list boxes.

The first list box call up an SQL statment.

I then have a command button which using an addition query allows you to select one row at a time and add it to the second List box. I also have a second command which copies all the entries across.

What I would like to acheive is something similar but using the Multi select function.

i.e. you select two or threee rows hit select and they appear in the second list box.

Is this possible using queries or do I need to go more into VBA and if so what is the best route.

I would appreciate your help.

thanks

 

[HarleyQuinn]

I think you could do what you require using the IN statement. Something like:

SELECT * FROM TBL_1 WHERE ROW1 IN ('" & YOURVALUES & "')

YOURVALUES would be a string variable containing all of the selected items from the listbox, seperated by ','
i.e. you have a list of 5 items:

Monday
Tuesday
Wednesday
Thursday
Friday

Your user selects Monday, Wednesday and Friday. You would add the values to YOURVALUES so YOURVALUES would look like this:

Monday','Wednesday','Friday

And therefore your query above (when run) would run like this:

SELECT * FROM TBL_1 WHERE ROW1 IN ('Monday','Wednesday','Friday')

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[hkaing79]

Two options:
As Harleyquinn suggested, you can create a string and use that in your query or just use the string and set that to your rowsource (field list instead of table/query).

To do this, you can use the following

Dim i as Variant

If listbox.ItemsSelected.Count Then
For Each i in listbox.ItemsSelected
strFilter=strFilter & "'" & listbox.Column(0,i) & "'"
'replace 0 with the column you want to extract
Next
End If

The other option is to create a table from the first listbox and setting the rowsource for the second listbox equal to the table.

 

[CMPaccess]

Thanks for the replies. I'm not sure whether your suggestions will be able to actually acheive what I want.

Mainly because I actually use the queries to pass info to other queries rather than just populate list boxes.

below are the three queries I would use in the process.

Hopefully that will help explain what I'm trying to acheive.

Step 1 - List Box 1 - Query 1
SELECT [Drawings].[DrawingID], [Discip] & " | " & [DrawingNumber] & " | " & [DrawingTitle1] & " | " & [Revision] & " | " & [Status] AS Drgs
FROM Drawings
WHERE ((([Drawings].[Discip])=[forms]![Issue]![DisciplineName]))
ORDER BY [Drawings].[CADFilename];

Step 2 - Command Button for Single Select - Query 2
This populates the second List box
INSERT INTO SelectDwgs ( DrawingID, IssueID )
SELECT [forms]![Issue].[Drgs] AS Exp1, [forms]![Issue].[IssueID] AS Exp2;

Step 3 - Command Button to take contents of List box 2 to a subform - Query 3

INSERT INTO [DWG Issues] ( IssueID, DrawingID, DrawingNumber, Title, Revision, Status, DigitalFile, Discip )
SELECT [Issue].[IssueID], [SelectDwgs].[DrawingID], [Drawings].[DrawingNumber], [DrawingTitle1] AS Expr1, [Drawings].[Revision], [Drawings].[Status], [Drawings].[CADFilename], [Drawings].[Discip]
FROM Issue INNER JOIN (Drawings INNER JOIN SelectDwgs ON [Drawings].[DrawingID]=[SelectDwgs].[DrawingID]) ON [Issue].[IssueID]=[SelectDwgs].[IssueID]
WHERE ((([Issue].[IssueID])=[forms]![Issue].[IssueID]))
ORDER BY [Drawings].[CADFilename];

Is this possible based on the above ??

Thanks again for the help

 

[hkaing79]

There's several threads in the Forms forum regarding multiple select listboxes. One method is to use the AddNew property of recordset. The other is to use the INSERT INTO statement.

I prefer INSERT INTO as I find it faster, but it can be a bit tricky depending on your table structures. So if you feel comfortable with SQL, then I suggest using the INSERT INTO. You have the code above to convert your selected items into a string. Use "INSERT INTO newTable SELECT field1 FROM originalTable WHERE field1 IN (" & strItemsSelected & ")"

If you're uncomfortable with SQL, I recommend recordset. Here's an example code:

Dim db AS DAO.Database
Dim rs AS DAO.Recordset
Dim i AS Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM table;")
For Each i in listbox.ItemsSelected
  rs.AddNew
  rs("Field1") = listbox.Column(0,i)
  rs("Field2") = listbox.Column(1,i)
  rs.Update
Next
rs.Close
db.Close

 

[CMPaccess]

I went with the Add new method.

Your code worked a treat. Thanks very much.