Combox Box Search?

[iamchemist]

In Access (I'm actually using Access2007), when I use a Wizard to creat a Combo Box on a form to look up a value in a table, Access creates the attached Macro to occur on the "On Update" event of the Combo Box. It directs Access to do its normal string search, starting at the beginning of the string. The Database that I am working on needs Access to look for ANY word in each string in a particular fiend of the form's Table. In other words if the word "tree" is the 3rd word in one of the strings in this particular table field, I want Access to find that string. In fact it would be preferable if Access were to either give me all the strings that contain "tree" and let me choose the correct one, or at least find the first string, that contains "tree", and let me keep telling it Next, until it gets to the string that I want.

I'm sure many of you have done this thousands of times, as it has to be a pretty common need. Would someone please tell me how to accomplish it via either a modified Macro or via VBA?

Thanks,

Ron

 

[MajP]

The below code is updated there was a bug if the text was not found
I would put in the footer or header a textbox and command button. you type in what you are searching for and hit the command button. The same button is used to unfilter.

Private Sub cmdFilter_Click()
  'Need a textbox called txtFilter and a commandbutton called cmdFilter
  '
 
  Const fieldName = "notes"
  Dim strFilter As String
  Dim recCount As Integer
  Dim rs As DAO.Recordset
  If Me.cmdFilter.Caption = "Unfilter" Then
    Me.Filter = ""
    Me.FilterOn = False
    Me.cmdFilter.Caption = "Filter"
    Me.txtFilter = Null
    MsgBox "Unfiltered"
  Else
    If Not Trim(Me.txtFilter & " ") = "" Then
       strFilter = fieldName & " like '*" & Me.txtFilter & "*'"
       Me.Filter = strFilter
       Me.FilterOn = True
       'needed only to get record count
       Set rs = Me.RecordsetClone
         If Not (rs.EOF And rs.BOF) Then
           rs.MoveLast
           rs.MoveFirst
           Me.cmdFilter.Caption = "Unfilter"
           recCount = rs.RecordCount
           MsgBox "There was " & recCount & " records found matching '" & Me.txtFilter & "'"
         Else
          Me.FilterOn = False
          Me.Filter = ""
          Me.cmdFilter.Caption = "Filter"
          Me.txtFilter = Null
          MsgBox "There was " & recCount & " records found matching '" & Me.txtFilter & "'"
        End If
    Else
       MsgBox "No text to filter"
    End If
  End If
End Sub

 

[MajP]

Here is a demo file. This searches the note field of the employee table.

 

[MajP]

If you want to search for whole words only replace the current strFilter with these lines.

 strFilter = fieldName & " like '*[!a-z]" & Me.txtFilter & "[!a-z]*'"
 strFilter = strFilter & " OR " & fieldName & " like '" & Me.txtFilter & "[!a-z]*'"
 strFilter = strFilter & " OR " & fieldName & " like '*[!a-z]" & Me.txtFilter & "'"

to see what the filter string would look like:

notes like '*[!a-z]Janet[!a-z]*' OR notes like 'Janet[!a-z]*' OR notes like '*[!a-z]Janet'

where 'notes' is the field name and 'Janet' is the search string

 

[iamchemist]

MajP,

Thank you SO MUCH for your help. I played with the initial code that you suggested for quite a while, but I only got a count of matching DVD Titles, and could not see the titles.

I finally figured out that all of your found titles are there in your cloned RecordSet, so all I have to do is look through the last few records of my main form, and the titles are there. So, I just added a command button to my form to move through records in the Form RecordSet, and it works great!

Perfect! Thanks again!

Ron