Help with creating search forms resulting all matches

[QezB]

Hi,

I have created a search form which returns only exact matches:

Private Sub cmdSearch_Click()
Dim strItemNameRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter an Item Name!", vbOKOnly, "Invalid Search Criteria!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strItemName

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strItemName")
DoCmd.FindRecord Me!txtSearch

strItemName.SetFocus
strItemNameRef = strItemName.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strItemName and shows msgbox
'and clears search control

If strItemNameRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Search Complete"
strItemName.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "No Media Found For: " & strSearch & " - Please Try Again.", _
, "Search Complete"
txtSearch.SetFocus
End If
End Sub

I need help to modify it so that it returns all matching variants. For example if I have items called 'Windows XP, Windows Server, Windows ME' & did a search for 'Windows' I want it to return all three items.

At them moment I have to type in the exact name to get a match. Please Help . . . . .

 

[DCrake]

Simple Software Solutions

Instead of using = (equal to) change it to Like *Windows* this way it will find the word Windows anywhere in the string.

 

[QezB]

Thanx

I understand what you mean but I am new to this and don't know exactly where to implement the 'Like' statement.

Please could you inform me exactly which part of the code needs modifying?

Your help is much appreciated.

 

[QezB]

--------------------------------------------------------------------------------

I understand what you mean but I am new to this and don't know exactly where to implement the 'Like' statement.

Please could you inform me exactly which part of the code needs modifying?

Your help is much appreciated.

 

[DCrake]

Simple Software Solutions

The way you have coded it is correct however, it could be improved upon to give you the results you require.

On the AfterUpdate of your Textbox that contains the filter string enter the following code

If Me.TxtSearch = "" Then
Exit Sub
Endif

Me.Filter = "[strItemName] Like *'" & Me.TxtSearch & "*'"
Me.FilterOn = True

The asterisks mean that anything before or after the search string will be found. Thus:

XP Windows OS = True
Windows XP = True
Vista Windows = True

 

[QezB]

Thank you for your help.

I have tried the code but keep getting errors. I think I may be putting it in the wrong place. When you say 'AfterUpdate of your Textbox' where exactly are you referring to?

I would really appreciate it if you could quote the exact line of coding before & after.

 

[DCrake]

Simple Software Solutions

Lets say you have a text box on the screen that the user types in a word, say Windows. Now what you want it to do is to create a filter of the records based ojn what they have typed in.

First step create a new command button, if you don't already have one on the form called CmdFilter.

Next go to the properties of the button and click on the OnClick Event and select Stored procedure. This will take you into the vba code window.

Sub CmdFilter AfterUpdate()
   If Nz(Me.TextToFilterFor,"") <> "" Then
       Me.Filter = "[FieldToFilterOn] Like "'*" & Me.TextToFilterFor & "'*"
       Me.FilterOn = True
  End If
End Sub

Return to your screen and type in a word or phrase then click on the button to see the results.

David

 

[QezB]

Thank you for your help!

I managed to follow your advice and the search form works. Brilliant!

 

[DCrake]

Simple Software Solutions

Glad to be of help