find part of a record Help

[Travis33]

I have a combo box that finds the record that I type in
this is the code I placed in the after update event

dim rs as object
set rs = me.recordset.clone
rs.findfirst "[Item number] = ' "&me! [combo2]& "'"
me.bookmark = rs.bookmark

This code works fine but I want to be able to type in part of the string and have it display alist of records that have
this substring in it.

can someone please help.

Thank You,

Travis

 

[Katerine]

Hi Travis,
Quick question: is Item Number a string? I'm going to assume it's a number, and there's another string which you'd like to search against, which I'll call "Item String".

Assuming the substring is typed in a textbox called.. oh.. "txtSearch", and the results go in a listbox called "lstSearchResults", the following should work:

Set the control source to a query, with the following SQL (paraphrased):
SELECT [whatever]
FROM [whatever]
WHERE [Item String] LIKE "*" & Forms!FormName![txtSearch] & "*"

In the form (called frmFormName in the example above), place the following code:

txtSearch_AfterUpdate()
lstSearchResults.Requery
end sub

lstSearchResults_DoubleClick
if lstSearchResults.ItemsSelected.Count > 0 Then
dim rs as object
set rs = me.recordset.clone
rs.findfirst "[Item number] = ' "& _
me! [lstSearchResults]& "'"
me.bookmark = rs.bookmark
end if
end sub


This will update the listbox to the search results whenever the search string is changed, and it will go to the record selected in the listbox (make sure the bound field in the listbox is [Item Number] in the above example) when the user double-clicks an item in the list.

Note the use of the operator "Like" in the SQL. This is the key searching for substrings.

Hope that helps. Please let me know if you have any questions.

Katie