Dynamic row source for listbox from multi-select listbox

[sk84gtspd]

Hi,
So I have been browsing around and I haven't been seen what I'm looking for.

Ultimately, I am using the selections made of the form to generate a query for the user.

I have a CITIES listbox that is populated with values from a stored query.

I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.

I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this

 SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));

I also want multi-select so that is you can un-select all and get the results for all cities.

Here is my half thought approach.

 Private Sub CityList_AfterUpdate()
 
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
'            "FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION " & _
'            "WHERE (((t_location.CITY)=" & _
' LocQryStr &           [Forms]![MasterQueryGenerator]![CityList].OnSelect()));
 'Set ctl9 = [Forms]![MasterQueryGenerator]![CityList]
'For i = 0 To [Forms]![MasterQueryGenerator]![CityList].ListCount - 1
         ' If [Forms]![MasterQueryGenerator]![CityList].Selected(i) Then r = r + 1
        'Next i
 
'REQUERY THE LOCATION LIST TO SHOW LOCATIONS IN THE SELECTED CITY
Me.LocationList.Requery
 
End Sub

^ I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.


Its possible I may have been unclear about something please let me know if this is true.

Any help will be greatly appreciated

 

[vbaInet]

Get one listbox working and you can easily incorporate it into the other.

With that in mind, what you need to do is build the WHERE string that looks like this:

WHERE FieldName IN ([COLOR="Red"]1,2,3,4[/COLOR])

It's the part in red that you will build in code by looping through the ItemsSelected property of the listbox. See here for more:

https://msdn.microsoft.com/en-us/library/office/ff823015.aspx

 

[sk84gtspd]

So I refined it to this in the afterUpdate() event

 Private Sub CityList_AfterUpdate()
 
Dim LocQryStr As String
'Dim testString As String
Dim CityListCtl1 As Control
 Set CityListCtl1 = [Forms]![MasterQueryGenerator]![CityList]
 LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
            "FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION " & _
            "WHERE (((t_location.CITY)="
 For Each varItem In CityListCtl1.ItemsSelected
     LocQryStr = LocQryStr & "'" & CityListCtl1.ItemData(varItem) & "', "
     'testString = testString & "'" & CityListCtl1.ItemData(varItem) & "', "
  Next varItem
                
'Trim the end of LocQryStr
LocQryStr = Left$(LocQryStr, Len(LocQryStr) - 1)
LocQryStr = LocQryStr & ");"
                    
'Next i
 
'REQUERY THE LOCATION LIST TO SHOW LOCATIONS IN THE SELECTED CITY
Me.LocationList.Requery (LocQryStr)
 
End Sub

I know that I cant send the string with the requery but how do I get to accomplished what is intended by that?

Do I need to do something like?

 Me.LocationList.row_source=LocQryStr
 Me.LocationList.Requery

Any other ideas?

 

[vbaInet]

Remember, I mentioned that the resultant WHERE string should be:

WHERE [COLOR="Blue"]IN[/COLOR] [COLOR="blue"]([/COLOR]...[COLOR="blue"])[/COLOR]

and not

WHERE [COLOR="Blue"]=[/COLOR] (...)

... and remember the open and close parentheses.

First of all view the final LocQryStr in the Immediate Window to ensure that you've done it properly.

 

[sk84gtspd]

I did some tweaking got it to work and yes I'm I will be able to transfer this over to my other list box.

I WHERE IN did need to be changed duh! me :banghead:

I'm not sure why the solution I was working on a week ago didn't work Access seems to like being restarted every now and then.


I posted the working code below.

 Private Sub CityList_AfterUpdate()
 
Dim LocQryStr As String
Dim testString As String
Dim CityListCtl1 As Control
 Set CityListCtl1 = [Forms]![MasterQueryGenerator]![CityList]
 LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
            "FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION " & _
            "WHERE t_location.CITY IN ("
 For Each varItem In CityListCtl1.ItemsSelected
     LocQryStr = LocQryStr & "'" & CityListCtl1.ItemData(varItem) & "', "
 '    testString = testString & "'" & CityListCtl1.ItemData(varItem) & "', "
  Next varItem
                
'Trim the end of LocQryStr
LocQryStr = Left$(LocQryStr, Len(LocQryStr) - 2)
LocQryStr = LocQryStr & ");"
                    
 'MsgBox (LocQryStr)
 'REQUERY THE LOCATION LIST TO SHOW LOCATIONS IN THE SELECTED CITY
Me.LocationList.RowSource = ""
Me.LocationList.RowSource = LocQryStr
'Me.LocationList.Requery
 
End Sub

Thanks for the help and patience

 

[vbaInet]

You're welcome!

I WHERE IN did need to be changed duh! me :banghead:

I'm not sure why the solution I was working on a week ago didn't work Access seems to like being restarted every now and then.

You probably didn't use IN() or the right combination of OR. Basically IN() is the same as OR but in an array format.

By the way you don't really need the RowSource = "" because it's being replaced anyway.