Search Filter

[Steven19]

I have the following code,

Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler

Dim filterText As String

'Apply or update filter based on user input
If Len(txtNameFilter.Text) > 0 Then
 filterText = txtNameFilter.Text
   Me.Form.Filter = "[CompanyName] LIKE '*" & filterText & "*'OR [TradingCompanyName] LIKE '*" & filterText & "*'"
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   txtNameFilter.Text = filterText
   txtNameFilter.SelStart = Len(txtNameFilter.Text)
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   txtNameFilter.SetFocus
End If

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub

However if I search for: 5 Star because there is a space I get an error, however I have tried:

txtNameFilter.SelStart = Len(Replace(txtNameFilter.Text, " ", "."))

Instead of

txtNameFilter.SelStart = Len(txtNameFilter.Text)

However still get Error 2185 You can't reference a property or method for a control unless the control has focus

I have the unbound control on the form I am searching, its a continuous form which has a Query Control Source.

Does anyone have any suggestions?

[June7]

Does that code work at all?

Need a space between apostrophe and OR: "*' OR

[Micron]

the message means exactly what it says - it just doesn't report what property or method you are trying to reference because it's generic.

Set focus to the control before trying to use its Text property. The reason you don't have to do this to get a control value is because .Value is the default. Anything else requires that you set focus to the control first, or if you can guarantee that it has the focus (because the event you're coding for makes that a sure thing) then you can also use Screen.ActiveControl.

[Steven19]

Hi June

Thanks for the space hint.

The code searches, filters and returns results, it just doesn't allow for spaces in the user input. If you type a space, then the cursor goes back to the preceding character as though you never entered a space.


Regards

[Micron]

Access will strip leading and trailing spaces from fields if the control is bound. I suspect yours is and that you'll have to un-bind it (remove its recordsource property). If you need to have the control bound to the field, create an unbound textbox (can be hidden when you get it working but only by setting its width to 0), make it equal the text of the bound control and get your text value from this unbound control. You will want to delete the label if you do that. Note that you should remove this control from the tab order as well, otherwise a user could tab into it. Generally speaking, controls that filter or search should not be bound anyway.

[June7]

If you want 'filter as you type' functionality, review http://allenbrowne.com/AppFindAsUType.html