Auto populate listbox with textbox

[PatAccess]

Hello Guys,
I am trying to get a listBox to auto-populate or change as I am typing in the textbox. but I type all my selections go away. I placed the code under _Change event AND _KeyPress but nothing. Am I doing this right?
I went online to research some codes but I want to believe it is not THAT complicated?
Here is what I have

Private Sub txtSearch_Change()
    Dim sqlTraining As String
    sqlTraining = "SELECT DISTINCT [Training] FROM QryTrainedEmployees WHERE [Training] LIKE "" * " & Me.txtSearch & " * "" ORDER BY [Training]"
    Me.lstTraining.RowSource = sqlTraining
    'Me.lstTraining.Refresh
    Me.lstTraining.Requery
End Sub

 

[Isaac]

If you use the textbox's Change event, you should refer to its .Text property vs. .Value, (you are implicitly referring to .Value by not specifying any property - which is precisely why I don't recommend people do this, it obfuscates and avoids learning the actual property usages.)

I'm also not sure about all your quotation marks.

Try

sqlTraining = "SELECT DISTINCT [Training] FROM QryTrainedEmployees WHERE [Training] LIKE '*" & Me.txtSearch & "*' ORDER BY [Training]"

 

[PatAccess]

If you use the textbox's Change event, you should refer to its .Text property vs. .Value, (you are implicitly referring to .Value by not specifying any property - which is precisely why I don't recommend people do this, it obfuscates and avoids learning the actual property usages.)

You don't recommend people do what?
And with the .Text Property, do you mean

sqlTraining = "SELECT DISTINCT [Training] FROM QryTrainedEmployees WHERE [Training] LIKE "" * " & Me.txtSearch.Text & " * "" ORDER BY [Training]"

Because I tried with that but it is still not working?
Thank you

 

[Pat Hartman]

Older versions of Access required that you use the .text property when your code was running in a control that had the focus. It is the property you want to use for example in the on Change event so you can check each character as it is entered. Once focus leaves a control, you would refer to it using the .Value property or since .Value is the default you could omit it. So,
Me.mycontrol
Me.mycontrol.Value
me.mycontrol.Column(0) (combo)

ALL refer to the same thing, assuming that the bound column of a combo is the first column in the RowSource)

Your version of Access might allow references to .Text from other events but it is almost certainly not allowed in a query. I also think that .Value is not allowed in a query. You just use the control name - Forms!myform!mycontrol

So, I think Isaac was saying that he always uses Me.mycontrol.Value. That's fine. It is explicit but I use the Me.mycontrol format.

 

[MajP]

Use the text box as isaac said. Use single quote inside the double quotes. Like "'*" & textSearch.text & "*'"
Double Single * Double & textSearch & Double * Single Double
like '*Abc*'

 

[MajP]

I do a lot of find as you type. Listboxes, comboboxes, forms. I want a lot of features so I have build a few classes. This way I can build any FAYT with a single line of code and do not have to mess with the sql string to avoid this problem.

Find as we typed

Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...

www.access-programmers.co.uk