clearing a XP version combobox

[rmatteson]

I am trying to figure out how to clear all items from a combobox
(Access 2002).

On my form, I have to comboboxes. Combobox 2 is populated with a set
of child data dependent on the parent data item selected in Combobox 1.
This is done through the .AddItem method of Combobox 2 looping through
the recordset.

This works fine, until --> the data item in the Combobox 1 is changed,
then Combobox 2 just appends the "new" related child data to the what
was previously there. I need it to contain only the data items that
are related to the item currently selected in the Combobox 1.

Apparently, older versions of Access' Combobox had a .Clear method
(???) -- but this seems to be removed from later versions of Access.
The code to fill combobox 2 is as follows:
strSpecies = "SELECT Common_Name FROM Taxa WHERE Taxa.TaxaName = '" &
[Forms]![frm_SelectforEdit]![Combobox_1] & "'"

rst.Open strSpecies, CurrentProject.Connection
Combobox_2.RowSourceType = "Value List"

Do Until rst.EOF
Combobox_2.AddItem rst!Common_Name
rst.MoveNext
Loop

Any help would be appreciated

[Salad]

rm*******@operamail.com wrote:

I am trying to figure out how to clear all items from a combobox
(Access 2002).

On my form, I have to comboboxes. Combobox 2 is populated with a set
of child data dependent on the parent data item selected in Combobox 1.
This is done through the .AddItem method of Combobox 2 looping through
the recordset.

This works fine, until --> the data item in the Combobox 1 is changed,
then Combobox 2 just appends the "new" related child data to the what
was previously there. I need it to contain only the data items that
are related to the item currently selected in the Combobox 1.

Apparently, older versions of Access' Combobox had a .Clear method
(???) -- but this seems to be removed from later versions of Access.
The code to fill combobox 2 is as follows:
strSpecies = "SELECT Common_Name FROM Taxa WHERE Taxa.TaxaName = '" &
[Forms]![frm_SelectforEdit]![Combobox_1] & "'"

rst.Open strSpecies, CurrentProject.Connection
Combobox_2.RowSourceType = "Value List"

Do Until rst.EOF
Combobox_2.AddItem rst!Common_Name
rst.MoveNext
Loop

Any help would be appreciated

I am not sure why you need the AddItem process. Why not set the
RowsourceType to Table/Query? And the Rowsource be your SQL statement?

In the ComboBox1 AfterUpdate event enter/do something like
Me.ComboBox2.Requery
Me.ComboBox2 = Null

This will force the values in the combo box to reflect/show all values
that are related to the value in Combo1 and sets the current selection
to nothing.

In regards to your question, maybe the Requery is all you need.

[raydelex]

Thanks for the advice. I had tried the RowsourceType and Rowsource
before and they did not work. But after your reply I started from
scratch and tried them again -- it worked great. Not sure what I did
wrong the first time.

Thanks again.

[raydelex]

Thanks, I had tried using RowsouceType and Rowsource before, but it did
not work. After your reply, I tried it again from scratch -- and it
worked as it was supposed to. Not sure why I did wrong the first time.
BTW, I need both the combobox.Requery and = Null to get the combobox
to completed clear.

Thanks again......