Populating / unpopulating a list box based on another list box 1

[Welshbird]

Hi All,

I have two list boxes on a form - I'm currently populating the second list qwhen the first changes. Howewver, that means if you deselect the item from the first listbox it doesn't remove the items from the second; which is the behavour I'm aiming for. My current code looks like this:

Private Sub lstPCO_Change()
'clear the list of practices we choose
Dim i, a, b As Integer
a = Worksheets("Chosen").Range("B1").Value
Dim strCCG1, strfile, strCon As String
i = Me.lstPCO.ListIndex
strCCG1 = Me.lstPCO.List(i)
If Me.lstPCO.Selected(i) = True Then
'Use SQL to list the practices
OpenDB
strSQL = "Select [gpCode] , [gpName] , [gpPostCode], [gpDispensing] from [Address$] where [ccgName] = '" & strCCG1 & "'"
rs.Open strSQL, cn, adOpenStatic
With Me.lstPractices
    Do
        .AddItem
        .List(a, 0) = rs![gpCode]
        .List(a, 1) = rs![gpName]
        .List(a, 2) = rs![gpPostCode]
        .List(a, 3) = rs![gpDispensing]
        a = a + 1
rs.MoveNext
Loop Until rs.EOF
Worksheets("Chosen").Range("B1").Clear
Worksheets("Chosen").Range("B1").Value = a
End With
i = Me.lstPCO.ListIndex
strCCG1 = Me.lstPCO.List(i)
Else
End If
End Sub

What should I be trying to do in order to get it to dynamically know which items should be in the second list box?

Do I need my Trues and Falses in an array or summat? Brain freeze....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[PHV]

Remove all the items of your second listbox before to populate it.

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

 

[Andrzejek]

Dim [red]i, a[/red], b As Integer
...
Dim [red]strCCG1, strfile[/red], strCon As String
...
With Me.lstPractices[blue]
    .Clear[/blue]
    Do
        .AddItem

You do know that all your [red]RED[/red] variables are Variants, right?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

I often use cascading combo boxes using a selection from one as a parameter for a query to generate another list, simply using the List Range Name as the source for the next control. No removing or adding required!

 

[Welshbird]

THe data I use is in a specific format hence using SQL to populate the second box. I can't simply empty it as the user may select multiple items from Box 1.

The data is provided in the format:
Boxitem1

FirstColumn Box1​

hence using the SQL as I do.

So maybe I need to have the SQL check which boxes are ticked in the first in order to populate the second??

Typing aloud... I could change the SQL to a 'where CCG IN' rather than 'Where CCG ='... but then that won't 'remember' any items already selected in the second box. Unless I only clear 'unselected' items?

Hmm....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Loop thru box1 to construct an "IN" list: 'selection1','selection2' etc.

Construct your IN statement using you IN list. Use the Resultset as the list fill range of the next selection control.

So I don't understand why you would need to keep anything that was previously selected in the second box. Can you explain what you're trying to accomplish?

 

[Welshbird]

Basically the first box is a list of organisations in the UK NHS. as one is selected then the doctors within that organisation populate the second box. The idea is that the user can selected several doctors across various organisations.

I was try to avoid them selected an organisation and then having to 'click' on something else to populate the second box.

I guess though taht maybe if the remove an organisation I should just remove the doctors from the right hand box as they have decided not to include those.

If I can clear and repopulate it makes another part easier - which is working out the list index to use to decide where to populate from now on?

Thanks Skip - Good suggestion - I'll give it a go.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Skip - as always - I should just listen you!

So - have followed your advice.Now my 'OnChange' I clear the second list box and then read the first and use it via the SQL to populate the second. I had a bit of trouble with error trapping (if you click but don't manage to get a tick in the box) but am just giving the user a message that they haven't selected anything.

All fab.

Thank you!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Andrzejek]

I usually – when populating a list box and have none of the items selected – set Enable property of the button to False. Than in Click event of the list box, I set it back to False and I loop thru the items and as soon as I find an item with Selected = True, I set the button’s Enable property to True.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.