VBA question: acListBox

[DB88]

Hello,

I'm writing a code to clear all the "search boxes" (unbound text fields, combo boxes etc used to search for records) in my form header. I'm very new to VBA and have mostly been altering example codes to suite my needs. Text fields, combo boxes and check fields are all clearing as expected but not the list box. My guess is that since it is multi-select, I need to clear each item selected individually. But my attempts have been unsuccessful. Below is my current working code (aka without acListBox). How should I modify to clear list boxes?

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control
Dim varItem As Variant

'Clear all the controls in the Form Header section.


For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

[ranman256]

Set the list.RowSource= "" then put it back the way it was.

[Xipooo]

Set the list.RowSource= "" then put it back the way it was.

Beat me to it.

[DB88]

Make sense, thanks. I'm still getting use to assignment and syntax of VBA so let me confirm I got this right.

Case acListBox
list.RowSource=""
End Select

[DB88]

I just tried adding the code above and it didn't work. I get an error saying "Object Required"

But then when I try ctl.list.RowSource="", I get the error "object doesn't support this property or method

[Xipooo]

Try:

Case acListBox
    ctrl.RowSource = ""

[ItsMe]

I think they are using ctl as Control

Select Case ctl.ControlType


so maybe
ctl.RowSource = ""

[DB88]

ctl.RowSource="" clears the list entirely instead of just removing the selection. So, I had a list of 10 items and how it is just a blank box.

I think this proves the RowSource is not the way to go. In the property table, RowSource is value list source aka the table that is populating the list.

[DB88]

I just tried the ItemsSelected property (ctl.ItemsSelected = Null) and I got the error message "Invalid Use of Property"

[DB88]

yes, ctl is a Control. Recall from my code above "Dim ctl As Control"

[ItsMe]

This should be working to clear the value.
Case acTextBox, acComboBox
ctl.Value = Null

Maybe you can test the combo with a temporary click event
Me.ComboName.Value = ""
or
Me.ComboName.Value = Null

[Xipooo]

DB88 you're going all over the place... so lets just settle on one solution here.

If you want to change the ItemsSelected property, understand that it is expecting a Collection OBJECT and therefore when you set the property you need to use the "Set" keyword.

Case acListBox
     Set ctl.ItemsSelected = Null

[Xipooo]

FWIW, if you wanted to clear out the listbox rowsource you would have to refill it after you clear it.

Case acListBox
     Dim Source as string
     Source = ctl.RowSource
     ctl.RowSource = ""
     ctl.RowSource = Source

But that is unnecessary if you just clear out the ItemsSelected property as I described above.

[ItsMe]

DB88 you're going all over the place... so lets just settle on one solution here.

If you want to change the ItemsSelected property, understand that it is expecting a Collection OBJECT and therefore when you set the property you need to use the "Set" keyword.

Case acListBox
     Set ctl.ItemsSelected = Null

I think the OP is talking about a combo box control. ItemsSelected is not in scope. Also, the For Each ctl in statement should set ctl =

[Xipooo]

This should be working to clear the value.
Case acTextBox, acComboBox
ctl.Value = Null

Maybe you can test the combo with a temporary click event
Me.ComboName.Value = ""
or
Me.ComboName.Value = Null

Will this work with a Multi-Select though?