Access: Textbox find ans start typing at last position

I am trying to create a textbox that when the user types in it ...the records get searched/queried by it's value.  I am pretty close but it is having trouble if I type a "SPACE" it doesn't count it, or removes it or something.

example:  A B C  House never works.   ABCHouse works.

Anyway:  below is what I have, positioning the mouse is where I am having the issue.


Just to be clear the query works...don't need help there yet ;).

Thanks experts hope I was clear.


Private Sub txtSearch_Change()
    With Me.txtSearch
        Me.Requery
        .SetFocus
        .SelStart = Nz(Len(.Value), 0)
    End With
End Sub

Open in new window

skillileaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Where is this textbox located?  It should be unbound, and is normally placed in either the form header or footer.

When I do this, rather than requerying the form, I use the forms Filter property, something like:

Private Sub txtSearch_Change

    if Len(me.txtSearch.Text) = 0 then
        me.filter = ""
        me.filterOn = false
    else
        me.filter = me.txtSearch.text
        me.filteron = true
    endif

end sub
0
skillileaAuthor Commented:
It is in the header and unbound.

I'll try it...tnx
0
Rey Obrero (Capricorn1)Commented:
to filter the records, you need to specify the name of the field
also it will be nicer to use "Like" to see the result as you type


me.filter="[NameOfField] like '" & me.txtSearch.text & "*'"


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
good catch, capricorn1.

I must have had a brain cramp.
0
skillileaAuthor Commented:
OK...I tried both TEXT and VALUE... the name of the field is [VendorName].

the field still goes back to highlighting the first keystroke and then ultimately replaces the entire value on the next keystroke. And SPACES are still removed.

thoughts...



Private Sub txtSearch_Change()
    With Me.txtSearch
        'Me.Requery
        '.SetFocus
        '.SelStart = Nz(Len(.Value), 0)
       
   
        If Len(.Text) = 0 Then
            Me.Filter = ""
            Me.FilterOn = False
        Else
            Me.Filter = "[VendorName] LIKE '" & .Text & "*'"
            Me.FilterOn = True
        End If



    End With
End Sub
0
Rey Obrero (Capricorn1)Commented:
try this


Private Sub txtSearch_Change()
dim str as string
    With Me.txtSearch
   str=str & .text
        If Len(.Text) = 0 Then
            Me.Filter = ""
            Me.FilterOn = False
        Else
            Me.Filter = "[VendorName] LIKE '" & str & "*'"
            Me.FilterOn = True
        End If

    End With
End Sub
0
Dale FyeCommented:

Get rid of the With/End With lines.  Then try:

    If Len(Me.txt_Search) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = "[LD_Desc] Like '*" & Me.txt_Search & "*'"
        Me.FilterOn = True
    End If
    Me.txt_Search.SelStart = Len(Me.txt_Search.Text)
0
Dale FyeCommented:
Sorry, that should have been:

    If Len(Me.txt_Search) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = "[VendorName] Like '*" & Me.txt_Search & "*'"
        Me.FilterOn = True
    End If
    Me.txt_Search.SelStart = Len(Me.txt_Search.Text)
0
skillileaAuthor Commented:
This is what I have for code but,

The SPACE still goes a away if I try and seperate words.
ie.
Burger King
SPACE is removed



Private Sub txtSearch_Change()
    If Len(Me.txtSearch) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = "[VendorName] LIKE '*" & Me.txtSearch & "*'"
        Me.FilterOn = True
    End If
    Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
End Sub


Thanks for the help!
0
Rey Obrero (Capricorn1)Commented:
try this one


Private Sub txtSearch_Change()
dim str as string
str=str & me.txtSearch.text

    If Len(Me.txtSearch) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = "[VendorName] LIKE '*" & str & "*'"
        Me.FilterOn = True
    End If
    Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
End Sub
0
skillileaAuthor Commented:

It still doesn't like the SPACE.

Private Sub txtSearch_Change()
    Dim str As String

    str = str & Me.txtSearch.Text
    If Len(Me.txtSearch) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = "[VendorName] LIKE '*" & str & "*'"
        Me.FilterOn = True
    End If
    Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
End Sub
0
Dale FyeCommented:
I understand the problem now.  For some reason, between the time you enter the Changed event and exit it, me.txtSearch.text drops the space (" ") from the end.  I tried resetting it just before exiting the subroutine using the following line, but that kicks off the change event again, an infinate loop.

   me.txtSearch.text = str

Cannot say I have ever noticed this behavior before, but that's because I generally use a command button to set the filter, rather than the change event.
0
Rey Obrero (Capricorn1)Commented:
can you check the text validation of the txtSearch, or

better create a new textbox for this purpose  txtSearchA

then use this codes


Private Sub txtSearchA_Change()
    Dim str As String

    str = str & Me.txtSearchA.Text
    If Len(Me.txtSearchA) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = "[VendorName] LIKE '*" & str & "*'"
        Me.FilterOn = True
    End If
    Me.txtSearchA.SelStart = Len(Me.txtSearchA.Text)
End Sub
0
skillileaAuthor Commented:
I zipped up a subset of the file

1 form
1 table

It is still not allowing me to add a space to the txtSearch field.

thanks for the help test.zip
0
Rey Obrero (Capricorn1)Commented:
test this


Database9.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skillileaAuthor Commented:
Thanks Experts!

You guys are awesome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.