Using values selected in list box for querying

[AnDe]

I want to use the values selected in a list box as parameters for a query.

 

[dhstein]

Create Public Function in a New Module:

Public Function GetVariable(VariableType As String) As Variant

Select Case VariableType
Case Is = "MyVariable1"
GetVariable = MyVariable1
Case Is = "MyVariable2"
GetVariable = MyVariable2


'etc etc - other variables can be defined the same way


Case Else
MsgBox "VariableType needed for GetVariable function"
End Select


End Function

*****************************************

In VBA Code :

MyVariable1 = txbMyTextBox.Value

*****************************************


In Query :

set criteria field in column for which you want to check the value:

GetVariable(MyVariable)

 

[Allen Browne]

A query cannot directly read the items selected from a multi-select list
box.

You can write a VBA function that accepts the list box as its parameter, and
the query field to match as its second, and returns True (if it matches) or
False (if it does not.) You use it in the query like this:
SELECT * FROM Table1 WHERE MyFunc([Forms].[Form1].[List0], [SomeField]);

Or, if the query is feeding a form or multi-select list box, you can build a
filter string and use it as shown here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

 

[dhstein]

Of course Allen is correct - you can't use the values in a list box. But you
can create a combo box instead and use those values with the method in my
previous response

 

[ken]

Normally in a query to return rows on the basis of an arbitrary set of
values in the same column you'd use the IN operator with a value list
as its argument, but this does not accept a parameter as the
argument. However, you'll find a couple of methods of simulating it
at:

http://support.microsoft.com/kb/100131/en-us

The second method using the InParam and GetToken functions is probably
the more reliable and simpler to use and will cope with different data
types in exactly the same way. First paste the two functions into a
standard module in your database. Then in the form with the list box
add a hidden text box, txtHiddenParameter say. You need to fill the
text box with a value list delimited by commas, so in the list box's
AfterUpdate event procedure put:


Dim varItem As Variant
Dim strValueList As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strValueList = strValueList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strValueList = Mid(strValueList, 2)

Me.txtHiddenParameter = strValueList

In the query put the following in the 'field' row of a blank column in
the design grid:

InParam([YourFieldName],[Forms]![YourFormName]!
[txtHiddenParameter])

uncheck the 'show' check box, and in the 'criteria' row put:

True

or in SQL view put:

WHERE InParam([YourFieldName],[Forms]![YourFormName]!
[txtHiddenParameter])

Ken Sheridan
Stafford, England

 

[dhstein]

Ken,

I'm experimenting with this technique. I think you intended an "If"
statement:

If ctrl.ItemsSelected.Count > 0 Then


I always get a "0" for ctrl.ItemsSelected.Count in a List Box

What am I doing wrong? Thanks.

David

Normally in a query to return rows on the basis of an arbitrary set of
values in the same column you'd use the IN operator with a value list
as its argument, but this does not accept a parameter as the
argument. However, you'll find a couple of methods of simulating it
at:

http://support.microsoft.com/kb/100131/en-us

The second method using the InParam and GetToken functions is probably
the more reliable and simpler to use and will cope with different data
types in exactly the same way. First paste the two functions into a
standard module in your database. Then in the form with the list box
add a hidden text box, txtHiddenParameter say. You need to fill the
text box with a value list delimited by commas, so in the list box's
AfterUpdate event procedure put:


Dim varItem As Variant
Dim strValueList As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strValueList = strValueList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strValueList = Mid(strValueList, 2)

Me.txtHiddenParameter = strValueList

In the query put the following in the 'field' row of a blank column in
the design grid:

InParam([YourFieldName],[Forms]![YourFormName]!
[txtHiddenParameter])

uncheck the 'show' check box, and in the 'criteria' row put:

True

or in SQL view put:

WHERE InParam([YourFieldName],[Forms]![YourFormName]!
[txtHiddenParameter])

Ken Sheridan
Stafford, England

I want to use the values selected in a list box as parameters for a query.

 

[ken]

David:

Actually, that line shouldn't be there at all. I'd intended to delete
the If…Else….End If construct completely from the code (it was copied
from a routine in one of my applications) as its not really relevant
in your case. Part of the If line seems to have slipped through the
net.

Make sure the list box's MultiSelect property is set to either
Extended or Simple, depending on whether you want to select multiple
items by Ctrl+Click/Shift+Click or simply by clicking each item to be
selected.

BTW if you want a button on the form to clear all selections in the
list then its code would be:

Dim n As Integer

For n = 0 To Me.YourListBoxName.ListCount - 1
Me.YourListBoxName.Selected(n) = False
Next n

lstStkNumbers_AfterUpdate

Ken Sheridan
Stafford, England

Ken,

I'm experimenting with this technique. I think you intended an "If"
statement:

If ctrl.ItemsSelected.Count > 0 Then

I always get a "0" for ctrl.ItemsSelected.Count in a List Box

What am I doing wrong? Thanks.

David

Normally in a query to return rows on the basis of an arbitrary set of
values in the same column you'd use the IN operator with a value list
as its argument, but this does not accept a parameter as the
argument. However, you'll find a couple of methods of simulating it
at:

The second method using the InParam and GetToken functions is probably
the more reliable and simpler to use and will cope with different data
types in exactly the same way. First paste the two functions into a
standard module in your database. Then in the form with the list box
add a hidden text box, txtHiddenParameter say. You need to fill the
text box with a value list delimited by commas, so in the list box's
AfterUpdate event procedure put:

Dim varItem As Variant
Dim strValueList As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strValueList = strValueList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strValueList = Mid(strValueList, 2)

Me.txtHiddenParameter = strValueList

In the query put the following in the 'field' row of a blank column in
the design grid:

InParam([YourFieldName],[Forms]![YourFormName]!
[txtHiddenParameter])

uncheck the 'show' check box, and in the 'criteria' row put:

True

or in SQL view put:

WHERE InParam([YourFieldName],[Forms]![YourFormName]!
[txtHiddenParameter])

Ken Sheridan
Stafford, England

 

[ken]

Ignore the last line, that's another bit of stray code from my form.

Ken Sheridan
Stafford, England