Populating a list box from selections made in a multiselect listbox 1

[MrMode]

I know I need to use varItem to collect the selections in a multiselect list box in order to populate a second one, but I am lost...

Multiselect List2 fields are [MappingId], [Requirement]

List3 fields are [CompanyId], [CompanyName]

I want to populate List3 after selecting multiple [MappingId]'s from List 2.

me.list3.rowsource = _

...

Any help, most appreciated.

 

[trevil620]

I don't think you can simply ask a 'rowsource' to provide what you need.
One option is to do something like:

For Each varItem In Me.List2.ItemsSelected
me.List3.AddItem Me.List2.Column(1, varItem)
Next varItem

Sometimes computer problems are like genealogy... The answer to one problem leads to two more!

 

[trevil620]

What is the relationship between the column(s) in the two listboxes? I don't see a clue based on the names. What you would need to do if you want to pull data from a table, is use the selected item from List2 to CREATE a recordset that grabs data from a table based on the keys you provide by using the "For Each..."

Sometimes computer problems are like genealogy... The answer to one problem leads to two more!

 

[MrMode]

Simple sql that currently populates List3 is something like....

list2 afterupdate()

me.list3.rowsource = _
"SELECT tblCompany.[CompanyID], tblCompany.[CompanyName], tblMapping.[MappingId] " & vbCrLf & _
"FROM tblCompany INNER JOIN tblMapping ON tblCompany.[CompanyID] = tblMapping.[CompanyID] " & vbCrLf & _
"WHERE tblCompany.[Company ID])= " & me.list2

' it is a numeric and not text reference, hence no chr(34) enclosing the list reference

Where I am confused is how to use your syntax...

For Each varItem In Me.List2.ItemsSelected
me.List3.AddItem Me.List2.Column(1, varItem)
Next varItem

to complete the where (or whatever it needs to be) portion of the statement. Looking on Microsoft I found a reference to ACC: How to Create a Parameter In() Statement which suggested:Field: InStr([Last Names separated by commas,Blank=All],[LastName])
Criteria: > 0 Or Is Null
Show: False

But I cannot get that to work either...

 

[dhookom]

This is an example from the Northwinds where the first listbox displays the product categories and the second the products in that category. The linking field is the CategoryID.

Private Sub lboCategories_AfterUpdate()
    Dim strSQLRowSource As String
    Dim strWhere As String
    Dim varItem
    strSQLRowSource = "SELECT ProductID, ProductName FROM Products WHERE ~ ORDER BY ProductName;"
    strWhere = " CategoryID IN ("
    For Each varItem In lboCategories.ItemsSelected
        strWhere = strWhere & Me.lboCategories.ItemData(varItem) & ","
    Next
    strWhere = Left(strWhere, Len(strWhere) - 1) & ") "
    Me.lboProducts.RowSource = Replace(strSQLRowSource, "~", strWhere)
    Debug.Print Me.lboProducts.RowSource [COLOR=#4E9A06]'for troubleshooting[/color]
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

A starting point:

strIn = ""
For Each varItem In Me!List2.ItemsSelected
  strIn = strIn & "," & Me!List2.Column(1, varItem)
Next varItem
Me!list3.rowsource = _
 "SELECT tblCompany.[CompanyID], tblCompany.[CompanyName], tblMapping.[MappingId] " & vbCrLf & _
 "FROM tblCompany INNER JOIN tblMapping ON tblCompany.[CompanyID] = tblMapping.[CompanyID] " & vbCrLf & _
 "WHERE tblCompany.[Company ID] IN (" & Mid(strIn, 2) & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

Ooh

Will try it, looks promising! I will let you know how I get on.

Thanks

 

[MrMode]

dhookham how do I get a copy of the northwinds dbase?

 

[dhookom]

You might already have it on your hard drive. If not, try desktop version for 2007.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Hmm

that keeps telling me that the page has timed out.

Which form are you pulling the code from? I will see if I have it already and if so, need to see the form so I understand what is happening.

Thanks!

 

[dhookom]

I created my own form and code just to answer your question. I use the Northwind database as a sandbox for testing solutions prior to posting here and in other Access support sites.

To find the Northwind sample, just open Bing and enter "access 2007 northwind sample database".


Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Oh!

*embarrassed

I just tried it in northwind (I have found and saved the 2007 desktop version) and it is not returning any values...

Option Compare Database
Option Explicit

Private Sub Form_Load()

Me.lboCategories.RowSource = _
"select category from products;"

End Sub

Private Sub lboCategories_AfterUpdate()
Dim strSQLRowSource As String
Dim strWhere As String
Dim varItem
strSQLRowSource = "SELECT [Product Name] FROM Products WHERE ~ ORDER BY [Product Name];"
strWhere = " Category IN ("
For Each varItem In lboCategories.ItemsSelected
strWhere = strWhere & Me.lboCategories.ItemData(varItem) & ","
Next
strWhere = Left(strWhere, Len(strWhere) - 1) & ") "
Me.lboProducts.RowSource = Replace(strSQLRowSource, "~", strWhere)
Debug.Print Me.lboProducts.RowSource 'for troubleshooting

End Sub

The list box lboCategories is set to Multi Select - simple

 

[MrMode]

No, my bad. I did not have a space before the Where statement.

IT WORKS!

Thank you

 

[dhookom]

I always base my controls and queries on the primary/foreign key fields. There is no field named Category in the Products table. It is CategoryID.

The lboCategories Row Source is

SELECT Categories.CategoryID, Categories.CategoryName
FROM Categories
ORDER BY Categories.[CategoryName];

The bound column is 1 (CategoryID) and the column widths are 0";1".

My lboProducts list box is similar in columns and widths to the Categories list box.

Did you look in the debug window (press Ctrl+G) to see what SQL statements are being generated? It should look something like:

SELECT ProductID, ProductName FROM Products WHERE  CategoryID IN (1,4,6)  ORDER BY ProductName;

You can copy and paste a statement into a blank query window.




Duane
Hook'D on Access
MS Access MVP