Solved Help with text box filter

[slharman1]

I have an unbound text box to filter records on a continuous form. I need to be able to type a string that includes spaces in the unbound text box. I type a space but a space is not inserted in the box.
Here is the code:

Private Sub txtModelFilter_Change()

 Dim strFilter As String
    On Error GoTo ErrHandler
    If Me.txtModelFilter.Text <> "" Then
        strFilter = "[ModelNumber] Like '*" & Me.txtModelFilter.Text & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .SelStart = Len(Me.txtModelFilter.Text)
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub

 

[Uncle Gizmo]

Re:-

strFilter = "[ModelNumber] Like '*" & Me.txtModelFilter.Text & "*'"

See:-

TextBox.Text property (Access)

Office VBA reference topic

docs.microsoft.com

To use the text property of the textbox the textbox needs to have the focus. If the focus is moving away for some reason, then maybe that's why it's failing. You might be better off using ".value" instead of ".text"

 

[arnelgp]

Private Sub txtModelFilter_Change()
 Dim sText As String
 Dim strFilter As String
    On Error GoTo ErrHandler
   sText = Me!txtModelFilter.Text
   If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub

 

[slharman1]

Re:-

strFilter = "[ModelNumber] Like '*" & Me.txtModelFilter.Text & "*'"

See:-

TextBox.Text property (Access)

Office VBA reference topic

docs.microsoft.com

To use the text property of the textbox the textbox needs to have the focus. If the focus is moving away for some reason, then maybe that's why it's failing. You might be better off using ".value" instead of ".text

Re:-

strFilter = "[ModelNumber] Like '*" & Me.txtModelFilter.Text & "*'"

See:-

TextBox.Text property (Access)

Office VBA reference topic

docs.microsoft.com

To use the text property of the textbox the textbox needs to have the focus. If the focus is moving away for some reason, then maybe that's why it's failing. You might be better off using ".value" instead of ".text"

I believe the focus moves away every time I type a key. This code is in the on change event.

 

[slharman1]

Private Sub txtModelFilter_Change()
Dim sText As String
Dim strFilter As String
    On Error GoTo ErrHandler
   sText = Me!txtModelFilter.Text
   If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub

Will this code work dynamically and let me use spaces in my unbound box?
Such as: stainless steel

 

[arnelgp]

i have tested it, so you need also.

 

[slharman1]

i have tested it, so you need also.

I’m thinking you have tested it and I need to as well?

 

[slharman1]

Private Sub txtModelFilter_Change()
Dim sText As String
Dim strFilter As String
    On Error GoTo ErrHandler
   sText = Me!txtModelFilter.Text
   If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub

Works Perfect! Thanks again arnelgp!

 

[slharman1]

Private Sub txtModelFilter_Change()
Dim sText As String
Dim strFilter As String
    On Error GoTo ErrHandler
   sText = Me!txtModelFilter.Text
   If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub

I need some kind of error handling, I am getting this message when I type something that is not on the list:
"You can't reference a property or method for a control unless the control has the focus"
Then all I can do is close the form.