List box updated from Combo Box - still showing previously selected items

[BigRods]

Hi,

I've got an issue with a list box, not sure if it's the functionality of how it's supposed to work, or if it's actually a problem BUT....

My list box is populated from the value of a combo box - the problem being that if I select items on the list box, when changing the combo box selection, the previously ticked items still appear in the list box.

e.g. Lets say my combo box has 3 items - Fruit, Cars, Pets. Fruit is the current value

My list box displays:

Apples
Oranges
Bananas

I select Apples from the List Box, then change the combo box to Cars.

My list box then displays:

Ford
Vauxhall
Porsche
Apples

As I've ticked Apples, it still shows this when I change the combo box?

Please help?!

 

[CalcSux78]

Sounds simple enough. Here's an example with a Table method.

Excel 2010

	A	B	C	D
1	List	Fruit	Cars	Pets
2	Fruit	Apples	Ford	Dog
3	Cars	Oranges	Vauxhall	Cat
4	Pets	Bananas	Porshe	Fish
5			Chevrolet	Bird
6				Rodent

<tbody>
</tbody>

​

Each column was set up as a table (1 column, # rows) and named whatever the row1 value was (i.e. table in column C was labeled "Cars")

I added a userform with two listboxes and used the following code to program them:

Private Sub ListBox1_Click()
Dim N As Integer
Dim Opt As String
For N = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(N) = True Then
        ListBox2.Value = ""
        Opt = ListBox1.List(N, 0)
        ListBox2.RowSource = Opt & "[" & Opt & "]"
    End If
    
Next N
End Sub

With this in place, if Cars is selected in ListBox1, the ListBox2 rowsource is changed to Cars[Cars] and shows the table values as options (regardless of what previous ListBox2 selection or source was)


There's a bunch more info here: Excel UserForm Controls - ComboBox and ListBox: AddItem Method, BoundColumn, List Property, ListIndex, RowSource, Selected Property, ...

 

[BigRods]

Thanks - would this work with Access form controls?
My listbox is bound, as a select query from the database with the parameter being whatever is selected in the combo box. Would that be possible with the code shown above?
Thanks - does that make sense?

 

[Micron]

Yes.
I suspect your combobox code is the issue. On the combobox After_Update event, you have to requery the listbox. Either pass the combo value to a sql statement that is the recordsource for the listbox and requery it just requery it if the recordsource is a query that uses the combobox value as criteria. If you are already doing this, perhaps something is wrong with the sql or query that allows values to be added instead of replaced. If you cannot solve with this info, show us your code that runs when the combobox is updated.

 

[BigRods]

Thanks - just to point out, I have "inherited" this database from someone who got to this point and then got stuck

When the combo box item is selected, the following code runs:

Private Sub cboGPDomainName_AfterUpdate()
Me.list_GPDomainName.RowSource = "select GoodPracticeDomaimNameOptions.Options FROM GoodPracticeDomaimNameOptions WHERE GoodPracticeDomaimNameOptions.[Domain Name]=[Forms]![frmNavigationForm]![SubMainFormInput]![cboGPDomainName]"
Me.list_GPDomainName.Requery
End Sub

PS I realise that "Domaim" is spelled incorrectly...

Thanks for any help

 

[Micron]

I don't know your form structure, but the reference to what I think is a control on a subform looks wrong, which would not supply the criteria you want to use in the query. You could try copying and pasting your sql into a new query in sql view and run it with the form open, and I think you'll get a message about not being able to find cboGPDomainName. Or you could compare your structure with this syntax:
referencing CONTROLS on subform:
[Forms]![Main form name]![subform control name].[Form]![control name on subform]

referencing subform PROPERTY (e.g. recordset property such as Recordcount):
forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount
NOTE: subformControlName is NOT the subform name.