UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Based On Recordset - Basic Filters Not Working, Access 2016    
 
   
chilladsi
post Oct 21 2019, 06:15 AM
Post#1



Posts: 21
Joined: 10-May 18



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?
Go to the top of the page
 
DanielPineault
post Oct 21 2019, 06:24 AM
Post#2


UtterAccess VIP
Posts: 6,959
Joined: 30-June 11



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


--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
projecttoday
post Oct 21 2019, 06:30 AM
Post#3


UtterAccess VIP
Posts: 11,181
Joined: 10-February 04
From: South Charleston, WV


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

--------------------
Robert Crouser
Go to the top of the page
 
arnelgp
post Oct 21 2019, 06:42 AM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


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

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
chilladsi
post Oct 21 2019, 07:15 AM
Post#5



Posts: 21
Joined: 10-May 18



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.
Go to the top of the page
 
projecttoday
post Oct 21 2019, 07:33 AM
Post#6


UtterAccess VIP
Posts: 11,181
Joined: 10-February 04
From: South Charleston, WV


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?

--------------------
Robert Crouser
Go to the top of the page
 
dmhzx
post Oct 21 2019, 07:42 AM
Post#7



Posts: 7,112
Joined: 22-December 10
From: England


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.

Go to the top of the page
 
chilladsi
post Oct 21 2019, 07:58 AM
Post#8



Posts: 21
Joined: 10-May 18



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?
Go to the top of the page
 
cheekybuddha
post Oct 21 2019, 08:12 AM
Post#9


UtterAccess VIP
Posts: 11,675
Joined: 6-December 03
From: Telegraph Hill


Try operating on the recordset directly.

See here: ADODB.Recordset.Filter

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Oct 21 2019, 08:13 AM
Post#10


UtterAccess VIP
Posts: 11,181
Joined: 10-February 04
From: South Charleston, WV


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.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 04:31 AM