2 LISTBOXES 2

[ZaZa]

Hi folks,
I have a question, I hope that you good people out there can help me.

I have a list box ( called SYSTEMS) which I use to limit the contents of another list box ( called Valves). The row source of the listbox "VALVES" is based on a query that relates the valves to the available systems. After the "SYSTEMS" listbox is updated, the "VALVES" listbox is requerried to show all the valves in the selected system.

This all works fine but now I want to be able to do MULTIPLE SELECTIONS of systems and have the "VALVES" list box show all the vavles in the systems selected.

Changing the "SYSTEM" list box to multi-select does not do the trick.

I know I will have to do some VBA coding but I don't know where to sart.


Please help,
ZaZa

 

[RickSpr]

Make your SYSTEMS list box multi-select, either Simple or Extended.

In the AfterUpdate event procedure for the SYSTEMS list box, you'll need to collect the selected system names in a VBA String variable, separated by commas. You can then use that string to build an SQL statement that uses the IN operator in the WHERE clause, such as "IN ('System1', 'System2', 'System3')". That SQL statement will become the Row Source for your VALVES list box.

Collecting the names from the SYSTEMS list box is a little tricky. See the help file topic on the ItemData property of a ListBox control for an example. Remember to put each system name in 'single quotes', and separate them with commas.

Once you've built the SQL statement, just assign it to the VALVES list box's RowSource property. That will immediately rebuild the list to contain the valves for each of the systems. Note that you might get duplicate valves, if one valve is used in multiple systems. If you want to avoid that, include the DISTINCT keyword in your SQL statement.

Your SQL statement should wind up looking something like this:
SELECT [DISTINCT] ValveName
FROM SystemValves
WHERE SystemName IN ('System1', 'System2', 'System3')
ORDER BY ValveName Rick Sprague

 

[jebry]

Hi ZaZa!

You can use the following code to get the selected systems out of the list box:

Dim varRow As Variant
Dim strSystems As String

strSystems = ""
For Each varRow In Me!Systems.ItemsSelected
strSystems = strSystems & "'" & Me!Systems.Column(0, varRow) & "',"
Next varRow

strSystems = Left(strSystems, Len(strSystems) - 1)

Then put together the SQL statement like Rick showed you above, putting strSystems inside the () of the In statement.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ZaZa]

Hi ,

RickSpur and Jerby, Thanks a lot for responding.

I am attempting to do what you two have advised and so far in the debug window, I get what looks like an appropriate string .

I think my problem starts when I try to write the SQl statement becuase the VAlUES list box does not get updated!! Maybe I am confused as to where to write this SQL CODE. You have said to put it the row source of the VALVES listbox.
Q1) Do I just type it in the line that says "row source"?
Q2) WHat do I set the Row Source Type to? My options are TAble/Query, Value list and Fieldlist.

I'd really appreaciate a little more guidance on this as I have not ever typed an SQl command anywhere before, just VBA ( with lots of help from Tek-tips).

Thanks,
ZaZa

 

[jebry]

Hi ZaZa!

Try this in the exit event of the list box or the after update event, or possibly the click event of a command button:


Dim varRow As Variant
Dim strSystems As String
Dim strSQL As String

strSystems = ""
For Each varRow In Me!Systems.ItemsSelected
strSystems = strSystems & "'" & Me!Systems.Column(0, varRow) & "',"
Next varRow

strSystems = Left(strSystems, Len(strSystems) - 1)

strSQL = "SELECT [DISTINCT] ValveName FROM SystemValves WHERE SystemName IN (" & strSystems & &quot ORDER BY ValveName"

Me!Valves.RowSource = strSQL
Me!Valves.Requery

The RowSourceType should be Table/Query

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ZaZa]

Jerby,

Thanks for yout last post! I finally got it to work with your and Rick Spur's help and adding that last piece of code to the after update event of the "Systems" listbox.

Only thing is that I had to omit the word [DISTINCT]from the sql statement for it to work. I don't know why but I'm not quibbling over it. But if any one knows why this is so I'd love to know!

Thanks again both of you ( Jerby and Rickspr) for you help and patience.

Zaza

 

[FoxProProgrammer]

The DISTINCT clause filters duplicate records from the query result. I am assuming that your table has at least two fields: valve and system. Let's say that your table contained the following data:

valve system fieldA fieldB
1 1 aaa bbb
1 1 ccc ddd
1 2 eee fff

With the DISTINCT clause, the query would return:

valve system
1 1
1 2

Without the DISTINCT clause, the query would return:
valve system
1 1
1 1
1 2

Without the DISTINCT clause, you would end up with a duplicate record in the result. When you say it doesn't work with the DISTINCT clause, what do you mean? Were records missing from the result? Depending on which fields is the key field and which fields are involved in the query, you could get the same result with and without the DISTINCT clause.

dz

 

[RickSpr]

I'm sorry, that [DISTINCT] problem was my fault. I put it in brackets to indicate that it was optional, that is, you might or might want it. The brackets weren't actually meant to be part of the statement. (In syntax diagrams, such as in the VBA help file, brackets are typically used for optional parts.)

If you get the same valve listed more than once, and you don't want that to happen, put the word DISTINCT back in, without the brackets. Rick Sprague

 

[jebry]

Hi ZaZa and Rick!

That problem was my fault as well, I should have removed the brackets in my final post because I was trying to give ZaZa usable code. As Rick says, it is so common to see optional words that way in Access help that I didn't give it a second thought!

Sorry!
Jeff Bridgham
bridgham@purdue.edu