INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Combox Box Search?

Combox Box Search?

(OP)
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

RE: Combox Box Search?

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.

CODE

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 

RE: Combox Box Search?

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

CODE -->

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:

CODE -->

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

RE: Combox Box Search?

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close