Have a text box act as an auto complete search function for a subform

[Gman063 via AccessMonster.com]

I have a form (frmMyForm) with a text box (txtLastname) that I am using to
filter records in a subform (frmMy subform) that is based on a query. I tied
the field in the subform that I would like to filter to the text box using
the “Like” function in the criteria area. It works great when I requery the
subform using the after update function.

What I would like to do is as characters are entered into the textbox it will
continue to filter the subform. (i.e. Lastname is the field being queried….
user enters “b” and it filters the subform….user enters another character
after the “ba” and it continues to filter. If user backspaces it continues to
filter.) Basically, I would like the text box to act as an auto complete
function. I am lost at how to do this or whether or not it is even possible.
I have tried using the onkeypress property but I do not think I am smart
enough yet to effectively use this function. Any help would be much
appreciated.

 

[roadie.girl]

have you tried using this with a combo box and just binding the combo
box to the LastName field in your table? this will accomplish what you
are trying to do

 

[Gman063 via AccessMonster.com]

have you tried using this with a combo box and just binding the combo
box to the LastName field in your table? this will accomplish what you
are trying to do

Not really what I am tring to do. The form is not bound to a table. The
subform is, and based on a query.
What I am trying to do is use the onkeypress event to requery the subform
based on what is in the textbox.
In the query for the subform I have the following ' Like [Forms]![frmMyTest]!
[txtLastname] & "*" '. When I enter a letter in the textbox it will not
requery the subform until I activate the after update property by pressing
the enter key or changing the focus of the textbox. What I would like it to
do is onkeypress of a letter requery the subform. If I enter another letter
after that it will requery the subform based on the combined entry. I hope
this explains what I am trying to do.

Thanks for your reply.

 

[Ron2005]

try the onchange instead of keydown. on change in triggered after every
keystroke.

 

[Gman063 via AccessMonster.com]

try the onchange instead of keydown. on change in triggered after every
keystroke.

I thought of that to....Just can't figure out the code to get it to work. So
far everything I have tried either errors or does nothing at all.

This is works everytime as long as I hit the enter key.

Private Sub txtLastname_AfterUpdate()
Me.frmBSCName_subform.Requery
End Sub

This is what I tried witht the onchange event. It does nothing.

Private Sub txtLastname_Change()
If Len(Me.txtLastname.Text) > 0 Then
Me.frmBSCName_subform.Requery
End If
End Sub


Any suggestions would be much appriciated.

Thanks for you for reply.

 

[Ron2005]

Have you tried adding, for purposes of a test, a msgbox in the on
change event to see if it is even being triggered. (it should be).

I was thinking of a refresh, but think I tried that one time and it
kept re-selecting all of the field so I could not really type. Make
sure the event is really being triggered. If it is not you may have a
corrupted form and my experience has been that you then basically need
to remake the form.

Ron