selecting a row from combobox by using a key word

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combobox on a form. A user can select a row by selecting the row
from the combobox list or entering the first letters/word.

But, the key word can be first or can be second, etc.

Is it possible to enter a key word that can be second, etc. and get the row?
Some thing similar to Like "*" & [Enter Name] & "*".
And how to do it?

Thanks
 
That is rather an interesting problem; I've not heard it posed before.

One way I can think of to do it is to parse the words into separate
keywords, and do a Union query on the results:

1st Query (qryParse1), operating on a field called TestField:

SELECT Left([TestField],InStr([TestField]," ")-1) AS FirstWord
FROM Parse;

2nd Query (qryParse2):

SELECT Right([TestField],Len([TestField])-InStr([TestField]," ")) AS
SecondWord
FROM Parse;

Union Query:

SELECT qryParse1.FirstWord From qryParse1
UNION
SELECT qryParse2.SecondWord FROM qryParse2;

This will generate a list of all keywords. Once one is selected, you could
use it to filter a 2nd combo box finding all records that have that keyword
in the string:

Me.SecondComboBox.RowSource = "SELECT Parse.TestField FROM Parse
WHERE ((InStr([TestField],[Forms]![YourFormName]![Your1stCB])<>"0"));"

HTH
Sprinks

Good luck. Hope that helps.
Sprinks
 
Thank you very much, Sprinks.

How about if I'm using only one combobox.
If I have WHIP CREAM and user type whip the row with whip will be gotten.
However, if it's WHITE CHOC. WHIP and user type whip, which is the main word
there, he/she cannot get this row.

I'm trying to use the following but it's not working:

Dim rs As Object

Set rs = Me.cboBox.Recordset.Clone
rs.FindFirst "[qryField] LIKE '* " & Me.cboBox & " *'"
If Not rs.EOF Then
Me.cboBox.Recordset.Bookmark = rs.Bookmark
End If

Thanks

Sprinks said:
That is rather an interesting problem; I've not heard it posed before.

One way I can think of to do it is to parse the words into separate
keywords, and do a Union query on the results:

1st Query (qryParse1), operating on a field called TestField:

SELECT Left([TestField],InStr([TestField]," ")-1) AS FirstWord
FROM Parse;

2nd Query (qryParse2):

SELECT Right([TestField],Len([TestField])-InStr([TestField]," ")) AS
SecondWord
FROM Parse;

Union Query:

SELECT qryParse1.FirstWord From qryParse1
UNION
SELECT qryParse2.SecondWord FROM qryParse2;

This will generate a list of all keywords. Once one is selected, you could
use it to filter a 2nd combo box finding all records that have that keyword
in the string:

Me.SecondComboBox.RowSource = "SELECT Parse.TestField FROM Parse
WHERE ((InStr([TestField],[Forms]![YourFormName]![Your1stCB])<>"0"));"

HTH
Sprinks

Good luck. Hope that helps.
Sprinks

Alex said:
I have a combobox on a form. A user can select a row by selecting the row
from the combobox list or entering the first letters/word.

But, the key word can be first or can be second, etc.

Is it possible to enter a key word that can be second, etc. and get the row?
Some thing similar to Like "*" & [Enter Name] & "*".
And how to do it?

Thanks
Click to expand...
Click to expand...
 
Back
Top