Multi-Select Listbox to use in Query

[rvermin]

Who knows the solution to following?

I have a form and I have two listboxes on it.

The first one is called "possible selections customer types", and contains "A - Best", "B - Good" and "C - Worse".

The second one is called "selected customer types", and is filled with with the selections made in listbox one.

What I would like to do is that the users are able to choose "A", "A"& "B", "A"&"c", "B"&"C", B or C as values and add them to the 2nd selection list.


Now... I know how to get these two lists and know how to get the values from lsit one to list two using selection command buttons, but:

How on eart can I use the selected values in a sql query in VBA?


I tried to do following:

Dim rst as DAO Recordset
Dim StrSQl as String
Dim Db as Database
Dim WSelection
DimWrSetOpen as boolean

Select SelectedItems
Case "A - Best"
wSelection = "A"

Case "B"
wSelection = "B - Good"

Case C
wSelection = "C - Poor"


StrSql = Select [Customer] from tblOrders Where (([Customernumber]="1&quot And ([Customertype] = WSelection ))

Set Rst = Db.OpenRecordset(StrSql)
WrSetopen = True


' Now I have a recorset I could work with... BUT:

The problem is it won't work!!!

Please need some Help!

Any other help with this kind of nulti-selection "problems" are welcome as well....

Many thanks in Advance!

Ramon

 

[grtammi]

Here's an example that may help you - just to let you know, I haven't tested this code, but it should give you an idea on how to start off with what you need to do.

Oh yah, make sure the MultiSelect property of the first list box is set to simple or extended, so that the user is able to select more that one item.

dim db as dao.database
dim rs as dao.recordset
dim sSQL as string
dim sWhereClause as string
dim itm as variant

' default the value of sWhereClause
sWhereClause = ""

' now, iterate each selection in the list box that the 
' user made, and append the appropriate value to the 
' where clause variable

for each itm in <your first list box name>.ItemsSelected
   ' test if where clause variable empty - if so, add AND
   if sWhereClause = &quot;&quot; then sWhereClause = &quot; AND &quot;
   ' append selected values to WHERE clause
   sWhereClause = sWhereClause & &quot;([Customertype] LIKE '&quot; & <your list box name>.ItemData(itm) & &quot;')&quot;
next

' enclose complete sWhereClause in (), and append a semi-colon onto the finished where clause
sWhereClause = &quot;(&quot; & sWhereClause & &quot;);&quot;

' now, set up SQL statement
sSQL = &quot;SELECT [Customer] from tblOrders WHERE([Customernumber]=1)&quot; & sWhereClause

' you can set up database and recordset variables here, but you don't really need to - just set the RowSource of the second list box to sSQL and requery it, like so.

with <name of second list box>
   .RowSourceType=&quot;Table/Query&quot;
   .RowSource = sSQL
   .Requery
end with

' more code or what not else
' ...

Of course, the above cannot tell you if there are records returned - you could use your db and rs variables to attempt to open the recordset and test for an rs.eof=true condition ... something like this:

 ' ... previous code here
set db = currentdb
set rs = db.openrecordset(sSQL)
if rs.eof then
   ' end of file true, therefore no records
   msgbox "There are no records there"
else
   ' populate recordset
   rs.movelast
   rs.movefirst
   ' display message
   msgbox "Hey, there are " & rs.Recordcount & " records!"
end if

' close variables - not closing DAO object can cause the 
' database to bloat, LOTS
rs.close  :  set rs = nothing
db.close  :  set db = nothing

' ... more code here ...

HTH

Greg





Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...