Text Box Filter

[Guest]

Hi,
I have a form that has search criteria and results. I have a field that is
unique to each record, containing a 6 digit code. I need to create a text
box field with an "apply filter" button on the form that would allow users to
type the first digit or the first 2 digits, or the first 3 digits, etc... of
that code and attain results that match that criteria.

Freddie

 

[Guest]

Freddie,

The easiest way to do this is to put the textbox in the forms header. Then,
you have a couple of options (below).

1. You can put some code in the Change event of the textbox to apply the
filter every time a change is made in the text box. Disadvantage is that it
will try to refilter the form after each keystroke.

2. Add buttons to filter and clear the filter for the form. My preferred
solution. What I generally do is have two buttons to set and clear the forms
filter property.

Private sub cmd_Filter_Click

me.Filter = me.txt_Filter
me.filteron = True

End Sub

Private sub cmd_Filter_Clear_Click

me.filter = ""
me.filterOn = False

End Sub

HTH
Dale

 

[Guest]

Thanks! I used the second method. Now my only problem is that I have 3
textboxes for 3 criteria and 3 different filter buttons, and a clear all
filters button. What happens now is that after every time i hit "filter" for
each criteria, it filters the whole database. I want the users to have the
option to use either only one criteria, or update that filtered data by using
another criteria after the first filter has been
toggled.

 

[Guest]

Freddie,

Usually, when I do that, I still only have one set of Filter and Clear
Filter buttons. The Filter buttons code might look like:

Private sub cmd_Filter_Click

Dim varCriteria as variant

'This criteria is formatted to look for exact matches
if Len(me.txt_FirstFilter & "") > 0 then
varCriteria = "[Field1] = " & chr$(34) & me.txt_FirstFilter & chr$(34)
end if

'This criteria is formatted to look for fields that start contain data
'that has the same beginning characters as in the textbox.
if len(me.txt_SecondFilter & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[Field2] Like " & chr$(34) & me.txt_SecondFilter _
& "*" & chr$(34)
End if

'This criteria is designed to look at a numeric field
'note that the value in your text field is not wrapped in quotes in this
format
if Len(me.txt_ThirdFilter & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[Field3] = " & me.txt_SecondFilter
End if

me.Filter = varCriteria
me.filteron = true

endif

HTH
Dale

 

[Guest]

My filter works only if I enter the first text box, and then one of the other
text boxes. It does not work if I simply enter something in the second text
box, or the second and 3rd box. Here's my code:

Private Sub Command34_Click()
Dim varCriteria As Variant

If Not IsNull(Me.txtbox1) Then
varCriteria = varCriteria & "([Field1] Like ""*" & Me.txtbox1 &
"*"") AND"

End If

If Not IsNull(Me.txtbox2) Then
varCriteria = varCriteria & "([Field2] Like ""*" & Me.txtbox2 &
"*"") AND"
End If

If Not IsNull(Me.txtbox3) Then
varCriteria = varCriteria & "([Field3] Like """ & Me.txtFilterSIC &
"*"")"

Me.Filter = varCriteria
Me.FilterOn = True

End If


End Sub

 

[Barcode]

Hello

I would like to use the code you wrote
Me.Filter = Me.Search
Me.FilterOn = True
But it didn't work?

I have a form with name "Search1" and on with field name "search" on Detlj.

And other form called "Serv" that is linked inside form Search1.

So in MS Access 2003 I could search on diffrent querys, but on the MS Access
2007 it not work the code,

"Serv" is connected to query that check what charackter writting on Search
field then query update.

Do you know where is the worng on this code ?

NOTE: if I run Refresh the search will work, but it is not possible to write
next character, because the "Search" field will be marked.

Here is the code:


Private Sub Search_Change()

If Me.Option = 1 Then
Me.serv.Form.RecordSource = "SearchSv"
ElseIf Me.Option = 2 Then
Me.serv.Form.RecordSource = "SearchArb"
ElseIf Me.Option = 3 Then
Me.serv.Form.RecordSource = "SearchSv2"
ElseIf Me.Option = 4 Then
Me.serv.Form.RecordSource = "SearchArb2"
End If
Me.Requery
DoCmd.Requery "serv"
Dim dbs As Database, rs As Recordset
Set dbs = CurrentDb
Set rs = Me.serv.Form.Recordset

SendKeys "{F2 2}"
Me.Requery
End Sub