Search as you type 1

[Mke85]

Hello,

I have a problem here that I can't quite figure out as I'm no VBA expert. I have setup a search as you type engine on a form and it is currently working but the I cannot type any spaces to search for multiply words. Maybe I have approched this incorrectly from the start but here is my code:

Private Sub txtSearchDescription_Change()
'Search as you type code.
Dim SCriteria As String

'Check if Search Field drop down menu is empty. If empty produce error message and exit sub.
If IsNull(Me!cboSearch) = True Or Me!cboSearch = "" Then
MsgBox ("Please select a Search Field before entering a Search Description.")
cboSearch.SetFocus
Exit Sub
End If

'Check to see if form is in Active or History pubs mode.
If cmdModeSwitch.Tag = "Active Pubs" Then
'Statement to create filter based on Search Field drop down menu and Search Description text box.
SCriteria = "(CFTO.[" & Me!cboSearch & "] LIKE '*" & txtSearchDescription.Text & "*')"
ElseIf cmdModeSwitch.Tag = "Pubs History" Then
'Statement to create filter based on Search Field drop down menu and Search Description text box.
SCriteria = "([CFTO - History].[" & Me!cboSearch & "] LIKE '*" & txtSearchDescription.Text & "*')"
End If

Me.Filter = SCriteria
'Turns filter on.

Me.FilterOn = True
'Statement to place the cursor correctly in the Search Description textbox.
Me.txtSearchDescription.SelStart = Len(Me.txtSearchDescription.Text)

'Statement to remove filter if Search Description textbox is empty.
If Len(txtSearchDescription) = 0 Or IsNull(txtSearchDescription) = True Then
Me!txtSearchDescription.SetFocus
Me.FilterOn = False
End If

Me!txtSearchDescription.SetFocus
End Sub

I know my problem is with this statment "Me.txtSearchDescription.SelStart = Len(Me.txtSearchDescription.Text)" but the search as you type feature will not work without it.

Thanks for help,

 

[TheAceMan1]

How are ya Mke85 . . .

[blue]I have setup a [purple]search as you type[/purple] engine on a form and it is currently working but the [purple]I cannot type any spaces to search for multiply words.[/purple][/blue]

[purple]Search as you type is not the same as multiple words.[/purple] You really need a seperate query/SQL for both.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Mke85]

Do you have a suggestion or an example about how I might go about this?

Thanks,

 

[Andrzejek]

How about:

Dim arySearch() As String

If InStr(txtSearchDescription.Text, " ") = 0 Then[green]
    'No Spaces[/green]
    SCriteria = "([CFTO - History].[" & Me!cboSearch & "] LIKE '*" & txtSearchDescription.Text & "*')"
Else[green]
    'We have some spaces in txtSearchDescription.Text[/green]
    arySearch = Split(txtSearchDescription.Text, " ")
    SCriteria = "([CFTO - History].[" & Me!cboSearch & "] LIKE '*" & arySearch(0) & "*')"
    For i = 1 To UBound(arySearch)
        SCriteria = SCriteria & " Or LIKE '*" & arySearch(i) & "*' "
    Next i
End If

Debug.Print SCriteria

Code not tested.
Check what you get at the last line of this code.
You may need to adjust "(" and ")"

Have fun.

---- Andy

 

[Mke85]

Thanks for the example Andy. I've been playing around with my original code more to try and figure this out.

I have narrowed down exactly which peice of code isnt allowing spaces.

Me.FilterOn = True
'Statement to place the cursor correctly in the Search Description textbox.
Me.txtSearchDescription.SelStart = Len(Me.txtSearchDescription.Text)

For some reason which I dont understand yet, turning the form filter on does not allow for spaces to be present in my search box.

Any suggestions as to why this might be happening?

Thanks for the help,
Mike.

 

[lameid]

A thought... If you applied the filter to a sub-form and had your textbox in your main form then you would not need to set the cursor position as the main form would not be refreshed.

Also I suspect you are using the text property instead of value because the text is not committed to the control (I usually don't use the change event so I don't know). This and the fact you are not explicitly setting focus to your textbox before using SelStart are the other thoughts I have as to why it doesn't work as-is.

 

[Mke85]

The subform idea is a good idea, I will try that and get to you with hopefully some result haha.

Thanks,
Mike.

 

[Craig0201]

To do a proper search function, the most effective ways is to create an inverted index of the items you wish to search on and use the results of the search on this to link back to the items. It's pretty standard stuff which you should be able to find on Google.

 

[Mke85]

lameid's idea worked perfectly! Thanks for the help all! Here is the changed that changed resulting from adding the subform.

Forms![CFTO - Search Publications].subCFTOSearch.Form.Filter = SCriteria
Forms![CFTO - Search Publications].subCFTOSearch.Form.FilterOn = True

'Statement to place the cursor correctly in the Search Description textbox.
Me.txtSearchDescription.SelStart = Len(Me.txtSearchDescription.Text)

'Statement to remove filter if Search Description textbox is empty.
If IsNull(Me!txtSearchDescription.Text) = True Then
Me!txtSearchDescription.SetFocus
Forms![CFTO - Search Publications].subCFTOSearch.Form.FilterOn = False
End If

Cheers,
Mike.

 

[1DMF]

I also use a form / sub form method for performing 'as you type' searches, but be careful, depending on how your recordset is produced and bound to the sub form, using the .Filter can sometimes crash!

I built a recordset class for executing stored procedures which includes a search method (utilising the recordset filter) if you should need it.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads