How to clear combobox values properly.

[bubai]

Hi,


In my Search form which I use to open filtered query I have placed a "Clear All" button to remove all the combobox values and start a fresh query. However, if I click the button combobox values get clear but the query fails. But if I don't use the button the query works. In the following pic I showed my form in Pic 1 and the error massage in Pic 2.
The code behind the "Clear All" (named Command6) button is

Private Sub Command6_Click()
Me.cboBrandID.Value = ""
Me.cboCatagory.Value = ""
Me.cboDes.Value = ""
Me.cboFiscalYear.Value = ""
Me.cboStation.Value = ""
Me.cboSupplierID.Value = ""
Me.txtBankValue.Value = ""
Me.cboCNF.Value = ""
End Sub

However, if I set control's .RowSource property to "", the combobox doesn't show anything at all.

[ranman256]

Don't use .value
just: cboBox=""

[bubai]

Don't use .value
just: cboBox=""

Still the same problem. Error no 2950.

[moke123]

I usually set the values to null - me.cboWhatever = null
If you are constructing the query or filter with code be careful to test for the right value, either a null or a ZLS whichever you use.

edit: show us the macro or code your using

[bubai]

I usually set the values to null - me.cboWhatever = null
If you are constructing the query or filter with code be careful to test for the right value, either a null or a ZLS whichever you use.

Cheers mate!
It's working now. But I didn't understand what you meant by ZLS.
Also can you answer me one more thing? That is - What is the data type that comes from the combobox? It seem that it fits in both numeric and string fields.

[moke123]

ZLS is a Zero Length String. It is not the same as a null. If you look in the field properties of your table there is an entry for allowing zero length strings.
You can test for both null and ZLS with something like nz(MyValue,"") <> ""

As far as the datatype returned by a combobox I'm not really sure. I know that I have run into situations where I've gotten Data mismatch errors but they are easy to fix by coercing the correct datatype - ie. Clng(me.MyCombo.Column(2)) or cDate(me.MyCombo)

[bubai]

But is it a good idea to allow for zero length string?

[moke123]

But is it a good idea to allow for zero length string?

Depends.
I think thats one of those questions where you may get several differing opinions.
I usually avoid them if I can.
Heres a link to one discussion, there are many https://social.msdn.microsoft.com/Fo...orum=accessdev

for a pretty good primer on nulls https://www.utteraccess.com/topics/1553733?post=unread