[X]My Assistant

[chilladsi]

I have a continuous form which is bound to a recordset - the basic in built Access text filters accessible via right click just error with an "Enter a valid value" message. Is it not possible for this to work? Do I have to code something that accesses the Recordset.Filter or similar?

[DanielPineault]

It would be helpful if you gave us detail on the field you are filtering and exactly what filter you apply so we can try and reproduce the issue and offer options.

As for coding things, it truly isn't complicated you simply do something like

For a numeric field

CODE

Me.Filter = "[YourFieldName]=SomeValue"
Me.FilterOn = True

For a Text field

CODE

Me.Filter = "[YourFieldName]='SomeValue'"
Me.FilterOn = True

For a Date field

CODE

Me.Filter = "[YourFieldName]=#11/21/2019#'"
Me.FilterOn = True

To remove a filter, you can use

CODE

Me.FilterOn = False

Or

CODE

Me.Filter = ""
Me.FilterOn = True

[projecttoday]

Did you click on the field then click on the filter icon?

[arnelgp]

on table design of the form's recordsource, remove spaces on the field name.

[chilladsi]

There are no spaces in table names or field names - the recordset is derived from a SQL Stored Procedure and then the forms recordset is set at run time :

Set Me.subForm.Form.Recordset = oADoDBRecordSet.Clone

The fields I am trying to right-click filter on are string (varchar) fields - using the contains / equals or any of those availabel on the right click menu brings up the error message.

[projecttoday]

Oh, so you're getting a kind of datatype mismatch? Or has this stored-procedure continuous form filter never worked at all? What happens if you left-click on the field and then click on the filter icon (the funnel) above?

[dmhzx]

The only time I've seen that message is when trying to filter on "blanks" - that is trying to find null values, (but that is because I keep forgetting what I have to ask for in order to find nulls)

or if trying to find the wrong kind of data for the field.

It would help if you explained how you're trying to set the filter Where you're getting the dialog box from, and exactly what you're typing into it, and if there is a field in the data with that value.

[chilladsi]

Sorry - it's literally the right click shortcut menu that is built in to Access - I right click in a field that contains Alpha Numeric data (a varchar column in the table) and select Text Filters > Begins with... for example.

So essentially I am not trying to set anything in code, just let Access do the filtering - I am wondering if ican't because the form is not bound to a table, but instead has it's recordset set programatically to an ADO Recordset clone?

[cheekybuddha]

Try operating on the recordset directly.

See here: ADODB.Recordset.Filter

hth,

d

[projecttoday]

If you do an A-B comparison between a form bound to a table and the same form bound to an ADO recordset clone of the same table, searching on the same field with the same search value, and you get that result, then I would say yes, the right-click filter doesn't work on a form bound to an ADO recordset clone.