How to filter a form's underlying recordset?

[prabhafriend]

Me.Recordset.Filter = "Name='Murugan'" won't works. Can't we filter a form's underlying recordset?

[HiTechCoach]

Me.Recordset.Filter = "Name='Murugan'" won't works. Can't we filter a form's underlying recordset?

Try this:


Me.Filter = "[Name]='Murugan'"
Me.FilterOn = True


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.

[geekgirlau]

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.

[HiTechCoach]

geekgirlau makes an excellent point.

Here is what I would use:




Me.Filter = "[Name]=""Murugan"""
Me.FilterOn = True


If you are actually making a reference to a control on the form, use this:




Me.Filter = "[Name]=" & chr(34) & Replace(Me.txtMycontrolName,Chr(34) , Chr(34) & Chr(34) ) & Chr(34)
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.

[prabhafriend]

Batman! I don't want to filter the form but the underlying recordset only for a temporarily purpose. How to do that?

[OBP]

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

[HiTechCoach]

Batman! I don't want to filter the form but the underlying recordset only for a temporarily purpose. How to do that?

How you would do that will depend on the purpose. Will you please explain in more detail what you are needing.

[prabhafriend]

I want the easiest but perfect way to filter a form's recordset without filtering the form and avoiding much variables as we can.

[HiTechCoach]

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.

[geekgirlau]

I want the easiest but perfect way to filter a form's recordset without filtering the form and avoiding much variables as we can.

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?