2007 - filter using text boxes

[hae]

I have a continuous form with several columns of data. I have put a unbound
text box at the top of each column to use as an input for what the user wants
to filter by on that column. I want the field to act just like the Text
filter "contains". I had this working in Access 2003 but can't seem to get
it working running in Access 2007. Any suggestions for the code behind the
text box to enable this filtering. Each time something is keyed into a text
box, any previous filtering should be cleared first.

Suggestions welcome!

 

[strive4peace]

Hi hae,

on the AfterUpdate event of each textbox:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

dim strFilter as string
if IsNull(me.activecontrol) then
'show all records
me.filterOn = false
else
'---------- pick ONE and delete the rest ----------

'---- if numeric:
if not isNumeric(me.activecontrol) then
msgbox "You must enter only numbers",,"Cannot filter"
exit sub
end if
strFilter = "[fieldname]= " & me.activecontrol

'--- if text:
strFilter = "[fieldname] Like '*" & me.activecontrol & "*'"

'--- if date:
if not isDate(me.activecontrol) then
msgbox "You must enter a valid date",,"Cannot filter"
exit sub
end if
strFilter = "[fieldname] = #" & me.activecontrol & "#"
'-----------------------------

me.filter = strFilter
me.FilterOn = true
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

WHERE
fieldname is the name of the field to filter on

Note: you cannot use 'Like' on non-text data types, you must use equal
to ...

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*