SEARCH RECORDS USING FORM FIELD

  • Thread starter Thread starter idanovic
  • Start date Start date
I

idanovic

I think what I'm trying to do is very simple. I want to add a
seach/filter field to my main form to allow users to search for records
based on key words or word. I'm looking for something simple. I'm
pretty new to vba so please explain things out.

This is what I've come up with so far but I could be totally off track.
Text35 is the text box in my form (called MainForm). Project is both
the name of the table and the name of the field I'm trying to seach.
Any help would be really appreaciated! Thanks!

Private Sub Text35_AfterUpdate()
If Not IsNull(Me!Text35) Then
Me!MainForm.Form.Filter="[Project] Like '*"&me!Project&"*'"
Me!MainForm.Form.Filter = True
Else
Me!MainForm.Form.Filter = False
End If
End Sub
 
The approach you suggest should work okay if the user enters just one
keyword (or one phrase), but it will not identify matches based on ANY of
the words you enter.

The example below parses each of the words into an array, and builds the
filter string so that it matches any words. The text box is named
txtKeywords (not Text35.)

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Project] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
 
Thanks for the help. I'd like to try to make my example work but for
some reason it doesn't. The error is on the line
"Me!MainForm.Form.Filter="[Project] Like '*"&me!Project&"*'" "

do you know why?
 
Should the filter be based on the contents of the Text35 box, not Project?

What is the data type of the Project field? If Text, the delimiters are
correct, but if Number (perhaps masked by a combo box), you need a different
approach.

Is the subform control really called "MainForm"?

Some spaces are required.

It may help to break the line down so you can determine where the error is.

What error message are you receiving?

You can use Debug.Print to check that the filter string is making sense.
(Remove the single quote at the start of that line, and after it runs press
Ctrl+G to check what came out.)

Try:

Private Sub Text35_AfterUpdate()
Dim strFilter As String
If Not IsNull(Me!Text35) Then
strFilter = "[Project] Like '*" & me.Text35 & "*'"
'Debug.Print strFilter
Me!MainForm.Form.Filter= strFilter
Me!MainForm.Form.Filter = True
Else
Me!MainForm.Form.Filter = False
End If
End Sub
 
Back
Top