Error "The Value you entered isn't Valid for this Field" (1 Viewer)

[Telecom]

Form Description:

Case "Price"

        strSQLSelect = "SELECT DISTINCT tbl_Contracts.fld_Price "
        strSQLFrom = "FROM tbl_Contracts "

Each of the 3 filters has three specific controls to set a particular filter. The Filter Field, the Filter Type, and the Filter Value. Controls (comboBoxes) named as:

me.cboxFilterField1
me.cboxFilterType1
me.cboxFilterValue1

Repeating for each of the 3 filter sets.

When you select a field from the Filter Field (ie Provider, Catagory, Description, or Price) and the Filter Type is set to "=" or "<>" then it will populate the Filter Value; using SQL to populate the RowSource.

The problem:
I open up the Form and select Price from me.cboxFilterField1. (The me.cboxFilterType1.value = "=".) I can then pull down info from me.cboxFilterValue1. I choose $9.43.

I press the Filter Button and it Filters my ListBox. I press the Clear Button and it Clears my Filter.

I choose Catagory from me.cboxFilterField1. (The me.cboxFilterType1.value = "=".) I can then pull down info from me.cboxFilterValue1. I choose "Frame Relay Port" and it immediately comes up with the following error:

I get a Microsoft Error:
The Value you entered isn't Valid for this Field.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.

It only does this after I choose Price from the Filter Field and then select a Text Type Field in Catagory or Description. I have a feeling it has something to do with the Currency Format of the Price Field. Where it's not fully clearing from the Filter Value ComboBox RowSource.

When I press the Clear Button and when I choose a new selection from the Filter Field it clears my Filter Value RowSource and other control default settings. I'm not sure what SQL code or VBA code I'm missing here...

Any help would be great!!!
Thanks,
Telecom

 

[Fernando]

If you dont need the $ on the prices this will fix it:

Case "Price"

        [B]strSQLSelect = "SELECT DISTINCT '' & tbl_Contracts.fld_Price, tbl_Contracts.fld_Price "[/B]
        strSQLFrom = "FROM tbl_Contracts "

For some reason the combobox remembers the first field type so if you use Currency it wont accept Strings after. So i casted your Price to String.
If you find a way to do it without this, post it. Im curious.

 

[Telecom]

Nice Fernando! That worked like a Charm! You saved me from a ton of aggravation.

That was driving me nutes. Even though I cleared the RowSource it was still looking at it as a currency. Wierd Access glitch.

Thanks!!
Telecom