Multi-Select List Box

  • Thread starter Thread starter Lambi000
  • Start date Start date
L

Lambi000

Hi,
I'm back. Here's my code again. I can't seem to get paranthesis around the
selected items and I can't figure out why. I know the thing works if I could
get those things around each selected item, but I can't seem to figure it
out.

Public Function Combo_List()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim frm As Form
Dim ctl As Control
Dim ctl1 As Control
Dim varItem As Variant
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("MultiSelect")
Set frm = Forms![Report Parameters]
Set ctl = frm!List90
Set ctl1 = frm!Text94
strSQL = "Select * from Purchasing Where Purchasing.FirstOfTTYPE = "
'Concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR
Purchasing.FirstOfTTYPE = "
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 12)
qdf.SQL = strSQL
qdf.Close
DoCmd.OpenQuery "MultiSelect"
Set db = Nothing
Set qdf = Nothing
End Function



NOTE: If I put another paranthesis after the Select statement, I will get a
parenthesis on the left of my item selected.
 
Try the following modifications and let me know if it works.

Dim strWhere as string

Set db = CurrentDb()
Set qdf = db.QueryDefs("MultiSelect")
Set frm = Forms![Report Parameters]
Set ctl = frm!List90
Set ctl1 = frm!Text94
Click to expand...

strSQL = "Select * from Purchasing "
if ctl.Itemsselected.count = 0 then
strWhere = ""
else ctl.ItemsSelected.count = 1 then
strWhere = "Where Purchasing.FirstOfTTYPE = "
Else
strWhere = " WHERE Purchasing.FirstOfType IN ("
endif
'Concatenate to strWHERE
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem
Click to expand...
'Trim the end of strWHERE
strWhere = Left$(strWhere, Len(strWhere) - 2)
Click to expand...
if ctl.itemsselected.count > 1 then
strWhere = strWhere & ")"
qdf.SQL = strSQL & strWhere
qdf.Close
DoCmd.OpenQuery "MultiSelect"
Set db = Nothing
Set qdf = Nothing
Click to expand...


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Lambi000 said:
Hi,
I'm back. Here's my code again. I can't seem to get paranthesis around the
selected items and I can't figure out why. I know the thing works if I could
get those things around each selected item, but I can't seem to figure it
out.

Public Function Combo_List()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim frm As Form
Dim ctl As Control
Dim ctl1 As Control
Dim varItem As Variant
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("MultiSelect")
Set frm = Forms![Report Parameters]
Set ctl = frm!List90
Set ctl1 = frm!Text94
strSQL = "Select * from Purchasing Where Purchasing.FirstOfTTYPE = "
'Concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR
Purchasing.FirstOfTTYPE = "
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 12)
qdf.SQL = strSQL
qdf.Close
DoCmd.OpenQuery "MultiSelect"
Set db = Nothing
Set qdf = Nothing
End Function



NOTE: If I put another paranthesis after the Select statement, I will get a
parenthesis on the left of my item selected.
Click to expand...
 
Thanks for the help. I've got it working now by using a variable I called
strQuote that uses Chr(34) and putting that in the string. Works like a
charm. Here it is:


Public Function Combo_List()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim frm As Form
Dim ctl As Control
Dim ctl1 As Control
Dim varItem As Variant
Dim strSQL As String
Dim strQuote As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("MultiSelect")
Set frm = Forms![Report Parameters]
Set ctl = frm!List90
Set ctl1 = frm!Text94
strQuote = Chr$(34)
strSQL = "Select * from Purchasing Where Purchasing.FirstOfTTYPE ="
'Concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & strQuote & ctl.ItemData(varItem) & strQuote & " Or
Purchasing.FirstOfTTYPE ="
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 29)
qdf.SQL = strSQL
qdf.Close
DoCmd.OpenQuery "MultiSelect"
Set db = Nothing
Set qdf = Nothing
End Function

Again, thank you very much.
 
Back
Top