Keyword search!

[dragginthru]

Im trying to add a keyword search to my page - I've added a button and it works fine in bringing up the "find" dialogue box.

However this defaults always to the "whole field" option, whereas what I want is it to default to "any part of field".

Can this be achieved, if not, is there another way of doing this?

Thanks
Gazz

 

[Kempes]

Yep,

I've got a seperate unbound search form where you can search by various field.
I have a free text box where the user keys in a word. This enables the search button and find the records containing that word regardless of where it is in the field (Part of field).

The code I have is as follows but feel free to butcher it to suit your needs.

Private Sub ChangeTitleButton_Click()
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([Change Title]) Then
MsgBox "Please key some text before searching", vbOKOnly + vbCritical, "Free Text Search Missing"
[Change Title].SetFocus

Else

stDocName = "View All"

stLinkCriteria = "[Change Title]LIKE" & "'*'&" & "'" & Me![Change Title] & "'" & "&'*'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "Search"

Exit_ChangeTitleButton_Click:
Exit Sub

Err_ChangeTitleButton_Click:
MsgBox Err.Description
Resume Exit_ChangeTitleButton_Click
End If
End Sub

 

[dragginthru]

Is there any chance of you posting an example of the form?

I can vaguely work VBA but am not clear where it would go etc...

thanks!

 

[Kempes]

Probably best to explain step by step as should be quicker.

Step 1
Open a blank unbound form and and a text box. Name it whatever you like.
Add a command button to your form but cancel out of the wizard so it gives you a blank button. name it whatever you like.

Step 2
Right click on the command button and go to properties/ Events tab.
Where it says on click, select the three dots and choose code builder.

Step 3
Here, cut and paste the following underneath the Private Sub (Your command button name)_Click and change fields/tables where specified.

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([YourFieldName]) Then
MsgBox "Please key some text before searching", vbOKOnly + vbCritical, "Free Text Search Missing"
[YourFieldName].SetFocus

Else

stDocName = "YourFormNameContainingallRecords"

stLinkCriteria = "[FieldNametolookup]LIKE" & "'*'&" & "'" & Me![YourFieldName] & "'" & "&'*'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "YourSearchFormName"

Exit_YourButtonName_Click:
Exit Sub

Err_YourButtonName_Click:
MsgBox Err.Description
Resume Exit_YourButtonName_Click
End If
End Sub

Hope this helps. Sounds complicated but should work.

 

[authorsami]

Thank you

This was a life saver. Simple, easy instructions that got the job done.

Thank you so much.

Sam

 

[methodmza_uk]

That search is spot on, your a legend!!!

 

[Kempes]

Glad it helped.