Me.Recordset.Filter = "Name='Murugan'" won't works. Can't we filter a form's underlying recordset?
Me.Recordset.Filter = "Name='Murugan'" won't works. Can't we filter a form's underlying recordset?
Try this:Originally Posted by prabhafriend
[VBA]
Me.Filter = "[Name]='Murugan'"
Me.FilterOn = True
[/VBA]
Note: name is a reserved word (a property) and should be avoid in naming your objects. So taht Access does not get confused, I would recommend always using [ and ] to wrap it.
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
I'd also suggest avoiding ' as a text delimiter - this will fall in a big heap if you are trying to filter for O'Brien.
geekgirlau makes an excellent point.
Here is what I would use:
[vba]
Me.Filter = "[Name]=""Murugan"""[/vba]
Me.FilterOn = True
If you are actually making a reference to a control on the form, use this:
[vba]
Me.Filter = "[Name]=" & chr(34) & Replace(Me.txtMycontrolName,Chr(34) , Chr(34) & Chr(34) ) & Chr(34)[/vba]
Me.FilterOn = True
What this does is handle a " in the middle of a string when contactinating text. By placing double up the " ( "" ) in the string, Access will understnd it.
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
Batman! I don't want to filter the form but the underlying recordset only for a temporarily purpose. How to do that?
I think that the best way to filter the recordset is in the Query as it is more versatile.
The other method of VBA Generated SQL replacing the existing query using the QueryDef is also very good
How you would do that will depend on the purpose. Will you please explain in more detail what you are needing.
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
I want the easiest but perfect way to filter a form's recordset without filtering the form and avoiding much variables as we can.
Easy and perfect for the user almost always means for the programmer/developer/designer that it will be a lot more difficult/work in time, planning, writing VBA code, etc.
Since I do not know exactly what you are trying to do, my guess for you is that the perfect way would probably be to create a new query. This will be independent of the form.
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
But WHY do you want to filter the recordset - what do you want to do with it once it's filtered? How do you determine the criteria for the filter?
We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
Aristotle
Posting Permissions
Reply With Quote
