Combobox - Search using "contains" not "begins with"

[draz]

hi all,

as i wrote in the title i want my combo box to be able to find a record using the "contains" criteria. at the moment it only looks for records that begin with the criteria.



the code is:

 
Private Sub Combo52_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & str(Nz(Me![Combo52], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

thanks!

[jfbuller]

I've had luck using this at least for SQL.

Like "*" & [input location] & "*"

Where [input location] the name of your text box, list box, combo box, query prompt or whatever. It will return "car", "carport", "racecar", "sports_car_page" etc. from that input source.

Note: Like [input location] & "*" only returns matches at the beginning of your search. car and carport but not racecar.

[draz]

Thanks for the reply, but i cannot figure it out where to insert it into my code

[jfbuller]

I'm very new to access myself, and am a bit more familiar with SQL than VBA.

You're asking your combo box to return values that contain "something", where are you entering that something, and what is it's source?

In a combo box, you can set up the row source to display all the possible results and choose from those, or set up a query so that it only displays some.

You can also run a query from a combo or text box that will display things similar to what you typed in it.

Like I said, I'm new to this and not sure what your goals are.

[boblarson]

You would use

rs.FindFirst "[ID] LIKE *" & Nz(Me![Combo52], 0) & "*"

And there is no reason, to use STR around the code for the find first.

[michael.legge@rbc.com]

Could anyone provide any further clarity. I am very new to access. Where would I go about placing the code in the previous post?

[orange]

Do you realize that the thread you have joined is almost 2 years old?

As for your question
This line

rs.FindFirst "[ID] LIKE *" & Nz(Me![Combo52], 0) & "*"

replaces this line

rs.FindFirst "[ID] = " & str(Nz(Me![Combo52], 0))

in the AfterUpdate event procedure list in post #1

Private Sub Combo52_AfterUpdate()
 ' Find the record that matches the control.
 Dim rs As Object 
Set rs = Me.Recordset.Clone
 rs.FindFirst "[ID] = " & str(Nz(Me![Combo52], 0))
 If Not rs.EOF Then Me.Bookmark = rs.Bookmark 
End Sub

For more ino on comboboxes see http://www.techonthenet.com/access/comboboxes/

[michael.legge@rbc.com]

Yes I realized the post was quite old. I figured no point starting a new thread as this was my exact question.

Thank you for the help!

[orange]

You're welcome. Hope you have solved the issue.

[michael.legge@rbc.com]

It does not appear for work for me. I was wondering if you could take a look. My code is similar to the initial posting and I changed the appropiate line as suggested above altering the combo box name and [ID] field. Would my code need a different approach?

Sub CboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.CboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[QstnID] = " & Me.CboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

[orange]

Did you want to find things LIKE something or = something?

Perhaps you could tell the reader in plain English, independent of Access and comboboxes, WHAT exactly you are trying to do.
The other option is to post a copy of your database, with instructions of what we should be looking at specifically.

[michael.legge@rbc.com]

Unfortunatly I cannot post my database as it has sensitive information. I will do my best to explain.

I have a combobox located on the footer of a subform. The subform contains a scrolling view of all my questions asked and a text box for the entering or editing of the responses for each question. My combo box is populated using a query of the questions displayed on the combobox. As of now when selecting a question from the combo box it jumps to that record. The combobox has the functionality to recognize the data fields it is populated with but using a "begins with" functionality.

For Example, Question: "State you name"
When i begin typing "State" it suggests the above question in which I can hit enter to jump to.

What I would like to build in is the option to start typing "name" and it would recognize to keep the above question in the drop down of the combobox.

Hopefully I have provided some clarity. Sorry if I am not using the most accurate terms I am very new to access.

Thank you for your help! It is much appreciated!
Michael