Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Form Based On Recordset - Basic Filters Not Working

Posted by: chilladsi Oct 21 2019, 06:15 AM

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?

Posted by: DanielPineault Oct 21 2019, 06:24 AM

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


Posted by: projecttoday Oct 21 2019, 06:30 AM

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

Posted by: arnelgp Oct 21 2019, 06:42 AM

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

Posted by: chilladsi Oct 21 2019, 07:15 AM

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.

Posted by: projecttoday Oct 21 2019, 07:33 AM

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?

Posted by: dmhzx Oct 21 2019, 07:42 AM

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.


Posted by: chilladsi Oct 21 2019, 07:58 AM

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?

Posted by: cheekybuddha Oct 21 2019, 08:12 AM

Try operating on the recordset directly.

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/filter-property-ado

hth,

d

Posted by: projecttoday Oct 21 2019, 08:13 AM

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.