Q. Multiselect listbox rowsource order by:

[niuginikiwi]

I have 2 multiselect listboxes ( lstProducts and lstRates)
I have the code below on the After Update event property of
lstProducts which requeries the rowsource of lstRates and order them
by ProductName everytime lstRates is required by selecting Product(s)
from lstProducts.
The code below does well to a certain extent, that is, when I select a
first product with i know has some Rates info that will appear on
lstRate, it doesn't appear until I select another product then it
appears. In other words, I always have to select two or more products
from lstProducts so the values in the lstRates show up.
I hope I had made this clear. Any help to get it to list the rates
after just selecting one product will be great. Thanks.
Here is the AfterUpdate event:

Private Sub lstProducts_AfterUpdate()
Dim stCtrl As ListBox
Dim strFilter As String

On Error GoTo Err_Handler
'Purpose:
Set stCtrl = Me.lstRates

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

strFilter = "SELECT DISTINCT tblProductDetails.ProductDetailsID,
tblProductDetails.ProductID, tblProducts.ProductName,
tblProductDetails.CropRate, tblProductDetails.Rate,
tblProductDetails.RateUnit FROM tblProducts INNER JOIN
tblProductDetails ON tblProducts.ProductID=tblProductDetails.ProductID
WHERE tblProductDetails.ProductID " & MultiSelectSQL(lstProducts)
strFilter = strFilter & "ORDER BY tblProducts.ProductName"
'Requery the listbox and apply filter
stCtrl.RowSource = strFilter
stCtrl.Requery


Exit_Handler:
Exit Sub


Err_Handler:
Call LogError(Err.Number, Err.Description, conMod &
".lstProducts_AfterUpdate")
Resume Exit_Handler

End Sub

 

[Douglas J. Steele]

You'll have to post the details of your MultiSelectSQL function.

 

[niuginikiwi]

Hi Doug,
Here is the MultiSelectSQL function that I have as a standard module
that gets used in the sub procedure to requery the list box.

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

 

[Douglas J. Steele]

Your code looks as though it should work.

When you've only selected a single entry in lstProducts, what is
MultiSelectSQL returning? What's actually being passed for strFilter when
it's not working?

 

[niuginikiwi]

Doug,
When I select a single entry in lstProducts, nothing is returned ( Im
saying this by watching the lstRates listbox being requeried, let me
know if there is another way of capturing what is being returned or
being passed for strFilter)... until I select a second entry bringing
the number of selected entries on lstProducts to two... and only then
related values of both selected entries are passed onto strFilter and
values show up on lstRates.
Would that be a bug in Access or is it just something that I am doing
wrong?
Thanks in advance

 

[Douglas J. Steele]

Put a breakpoint in your lstProducts_AfterUpdate routine (you do this either
by clicking in the gray border to the left of the code on which you want to
break, or by selecting the line of code, then selecting "Toggle Breakpoint"
from the Debug menu). You can then single-step through the code using F8. At
a minimum, you need to step through MultiSelectSQL to determine what it's
doing when only 1 item is selected from the listbox.

Another debugging technique is to use Debug.Print statements to write
specific values out to the Debug window (Ctrl-G)

 

[niuginikiwi]

After steping throught the afterupdate event of lstProducts, I find
that when one entry on lstProducts is selected, the mulitSelectSQL
function does not do any iteration through itemselected. It just
enters the function and then skips to the end and gets out of the
function back to the lstProducts afterupdate event procedure ... but
when i select two entries, the multiSelectSQL function does go through
itemseleted and counts entries into the In( blah, blah) criteria.
Do you have any idea why it would not take just one selection ie
In(blah) as well as In(blah, blah)?
Cheers!

 

[niuginikiwi]

Hi Doug,
Also something else I noted is that when I added in line:
strFilter = strFilter & "ORDER BY tblProducts.ProductName"
into the code below and thats when this behaviour started to happen.
It works with one entry selected with out the ORDER BY line but it
does not order by the ProductName as I would like when the AfterUpdate
event of the lstProducts fires, so Im adding in the above line to get
the list sorted by ProductName.

Private Sub lstProducts_AfterUpdate()
Dim stCtrl As ListBox
Dim strFilter As String


On Error GoTo Err_Handler
'Purpose:
Set stCtrl = Me.lstRates


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


strFilter = "SELECT DISTINCT tblProductDetails.ProductDetailsID,
tblProductDetails.ProductID, tblProducts.ProductName,
tblProductDetails.CropRate, tblProductDetails.Rate,
tblProductDetails.RateUnit FROM tblProducts INNER JOIN
tblProductDetails ON
tblProducts.ProductID=tblProductDetails.ProductID
WHERE tblProductDetails.ProductID " & MultiSelectSQL(lstProducts)

'this line below here causes it not to select a single selected entry
strFilter = strFilter & "ORDER BY tblProducts.ProductName"

'Requery the listbox and apply filter
stCtrl.RowSource = strFilter
stCtrl.Requery

Exit_Handler:
Exit Sub

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod &
".lstProducts_AfterUpdate")
Resume Exit_Handler

End Sub

 

[Douglas J. Steele]

I just noticed you need a space before ORDER BY:

strFilter = strFilter & " ORDER BY tblProducts.ProductName"


When you've got multiple rows selected, you'll end up with )ORDER BY without
that space, and it might work. When you've got a single value, though, there
won't be the closing parenthesis, so it will fail.

 

[niuginikiwi]

Thanks Doug,
I needed that space INDEED..
Thanks again for pointing it out. I wouldn't have known.