Filter Multiselect Listbox with another multiselect listbox?

[Guest]

Hi people,
Can I use a multiselect (simple) to filter another multiselect (simple)? I
ask this because I don't see a filter property on the data tab of the listbox
properties.

If the above answer is yest then, here is how i am trying to do this.
I got two multiselect listboxes(lstCrops & lstPlantings) on an unbound form,
one based queries.

When I select a CropName from lstCrops which has CropID as bound column,
lstPlanting should be filtered to what selection(s) of CropName I make. There
is a CropID field in the query that is behind lstPlantings.

Here is the code I have on a command button (btnListFilter) to requery the
listbox and apply the filter. When I click btnListFilter, I get this error
message: Wrong number of arguments or invalid property assignment (Error 450)

Private Sub btnListFilter_Click()
On Error GoTo Err_btnListFilter_Click
'Variables
Dim stDocName As ListBox
'Dim frm As Form
Dim strFilter As String

Set stDocName = Me.lstPlantings

'Cycle thru listbox using MultiSelectSQL function
'and assign filter variable with values selected from listbox
strFilter = "CropID" & MultiSelectSQL(lstCrops)

'Requery the listbox and apply filter
DoCmd.Requery stDocName, , , strFilter


Exit_btnListFilter_Click:
Exit Sub

Err_btnListFilter_Click:
MsgBox Err.Description
Resume Exit_btnListFilter_Click
End Sub

Can anyone enlighten me with where I am going astray or rather what I should
do?

Thanks so much in advance.

 

[AccessVandal via AccessMonster.com]

Change your strFilter to,

"SELECT col1,col2,col3 FROM Table WHERE CropID " & MultiSelectSQL(lstCrops)

Me.lstCrops.RowSource = strFilter
Me.listCrops.Requery

Delete "DoCmd.Requery stDocName, , , strFilter"
No option arguments for this "DoCmd".

I can't tell much of this "MultiSelectSQL(lstCrops)" function.

Why don't you use this function to fill the listbox rowsource?

 

[AccessVandal via AccessMonster.com]

Sorry, please change the listbox name.

Change your strFilter to,

"SELECT col1,col2,col3 FROM Table WHERE CropID " & MultiSelectSQL(lstCrops)

Me.lstPlanting.RowSource = strFilter
Me.lstPlanting.Requery

lstPlantings or lstPlanting?

 

[Douglas J. Steele]

It would help to know what the MultiSelectSQL function returns. In order for
your existing code to work, it needs to return a string like "IN (1, 2, 3)"
(or "IN ('1', '2', '3')" if CropID is a text field). Does it?

AccessVandal is correct, though, that you need to reset the RowSource
property of the other listbox.

 

[Guest]

Hi Doug,
Here is the function that I have it as a standard module that I use for
other multiselect listboxes as well. I guess it returns a string like "IN
(1, 2, 3)" as my PKs are all in AutoNumber.

Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) & Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter & ","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function


--
niuginikiwi
Nelson, New Zealand

It would help to know what the MultiSelectSQL function returns. In order for
your existing code to work, it needs to return a string like "IN (1, 2, 3)"
(or "IN ('1', '2', '3')" if CropID is a text field). Does it?

AccessVandal is correct, though, that you need to reset the RowSource
property of the other listbox.

 

[Guest]

Hi AccessVandal,
Thanks so much for the help. That was super stuff!
Here is what my code looks like and i does filter other listbox
lstPlantings. Yes, it was lstPlantings (sorry bout the miss-spelling).

Private Sub btnListFilter_Click()
On Error GoTo Err_btnListFilter_Click
'Variables
Dim stDocName As String
Dim strFilter As String

'stDocName = Me.lstPlantings

'Cycle thru listbox using MultiSelectSQL function
'and assign filter variable with values selected from listbox

strFilter = "SELECT
PlantingDetailsID,Property,Block,DatePlanted,CropName,VarietyName,Beds,PlantingID,CropID
FROM qryPlantingCombined WHERE (((qryPlantingCombined.Cut) = No)) AND
qryPlantingCombined.CropID" & MultiSelectSQL(lstCrops)

'Requery the listbox and apply filter
Me.lstPlantings.RowSource = strFilter
Me.lstPlantings.Requery



Exit_btnListFilter_Click:
Exit Sub

Err_btnListFilter_Click:
MsgBox Err.Description
Resume Exit_btnListFilter_Click
End Sub


Thanks once again.