Re: Keyword Search

PieOPah wrote:

In SQL I would use like %red% to find anything that contained the
string red. How do I do a Like search of ths nature in Access, keeping
in mind that I still want the user to be able to type in their string.

What Inkman and MGFoster have suggested work and I have no variation on that.

What I would suggest, however, is possibly an alternative way to the way you are doing, ie, a means by which your results are changed as the user types in a keyword. I use it quite frequently and successfully, but if anyone else sees anything fundamentally wrong or dangerous with it, hopefully they will speak up! 8)

For this, on your form you need two text boxes. Say the first is UNBOUND and is called "txtSearchEnter" and is visible and indeed is the text box in which a user types in a keyword. The second is also unbound and we'll call it "txtSearch" and it's visible property is set to NO.

Set the rowsource of a list box or the record source for a subform the same as what inpen or MGFoster wrote... in this case:

SELECT Name, Category, Type, Ingredients, Instructions
FROM TblName
WHERE Name Like "*" & [Forms]![frmWithAboveTextBoxes].[txtSearch] & "*"

Note - I wouldn't use "Name" as a table field name - it's an Access reserved word that could well cause you lots of grief.

In the on change event of txtSearchEnter, set up the following VBA - I am using a list box called "lstResults". lstResults has the above described row source. The syntax will vary a little if you use a subform instead:

Private Sub txtSearchEnter_Change()

Me.txtSearch.Value = Me.txtSearchEnter.Text

Me.lstResults.Requery

End Sub

It's important that the text property is used. What this does is use whatever is typed in txtSearchEnter in the where clause.

As I said, I've used this a number of times successfully. I uaully include a button that clears stuff, ie, with the event procedure like so:

Private Sub btnClear_Click()

Me.txtSearchEnter.Value = Null

Me.txtSearch.Value = Null

Me.lstResults.Requery

End Sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
.

Relevant Pages

  • RE: Filter records based on user input
    ... Private Sub Text2_GotFocus ... I thought that the bookmark line would set the focus to the control in the ... which would filter your form based on the text in the textbox. ... It should filter the records as the user types the number. ...
    (microsoft.public.access.formscoding)
  • RE: Filter records based on user input
    ... Private Sub txtFilter_Change ... which would filter your form based on the text in the textbox. ... It should filter the records as the user types the number. ...
    (microsoft.public.access.formscoding)
  • RE: Filter records based on user input
    ... Private Sub txtFilter_Change ... which would filter your form based on the text in the textbox. ... It should filter the records as the user types the number. ...
    (microsoft.public.access.formscoding)
  • RE: Filter records based on user input
    ... Private Sub txtFilter_Change ... which would filter your form based on the text in the textbox. ... It should filter the records as the user types the number. ...
    (microsoft.public.access.formscoding)