Using Listbox selection values in query???

  • Thread starter Thread starter WOLFE
  • Start date Start date
W

WOLFE

I am trying to use a list box to select what values should be used in a
query. The list box contains about 20 items, and is set so the user can
select multiple items. After selecting the items they wish to view, I would
like to run a query that will only return records with the selected item on
them. Is this possible? I have tried to store the selected values in an
array, and pass them to an IN clause, but that does not work. I thought
about using VB code to return the records to a recordset, but do not know
how to pass this to a report. Please help.


WOLFE
 
Hi Wolfe:

You can use the "Listbox.Value" method:

Dim vv As String

vv = Listbox.Value
DoCmd.RunSql "Select TableValues.* from TableValues WHERE
SpecialField.Status = " & Chr(34) & vv & Chr(34) & ";"

Regards,
Al
 
values in the listbox. I need to be able to query based off all selected
values in the listbox.<<<

Hi Joshua:

This code should work (it's based on similar problem that I've used while
programming a PALM database using Satellite Forms, code for which is similar
to the VBA of MS Access):

Dim frm As Form, ctl As Control

Dim varItm As Variant, sqlstmt As String, sqlstmt2

sqlstmt = ""

Set frm = Forms!form1 ' sample form

Set ctl = frm!List7 ' sample listbox

' begin phrase initiation

If sqlstmt = "" Then

sqlstmt = "Select TableValues.* from TableValues WHERE "

End If

' use a for each... next to get at the various variables and add them to
your sqlstmt

For Each varItm In ctl.ItemsSelected

sqlstmt2 = "SpecialField.Status = " & Chr(34) & ctl.ItemData(varItm) &
Chr(34) & " OR "

sqlstmt = sqlstmt & sqlstmt2

Next varItm

' now get rid of trailing " OR "

If Right(sqlstmt, 4) = " OR " Then

sqlstmt = Left(sqlstmt, Len(sqlstmt) - 4)

End If

' now add in the ";" and the end quotations

sqlstmt = Chr(34) & sqlstmt & ";" & Chr(34)

' voala!!!

MsgBox sqlstmt

' now just add it into your code--

DoCmd.RunSQL sqlstmt

This should work fine, although I haven't played around with it other than
to bring up the msgbox statement... let me know how it works for you.

Regards,

Al
 
Back
Top