Populate listbox with sql like clause

[guanfl]

I am trying to populate a listbox through its rowsource but i'm not sure if my syntax is right for the like clause.

Not showing any results:

me.list.rowsource = "SELECT tblProducts.ProductID, tblProducts.ProductName, qryTotalProducts.Qty
FROM tblProducts INNER JOIN qryTotalProducts ON tblProducts.ProductID = qryTotalProducts.ProductID
WHERE (((tblProducts.ProductName) Like "*" & [forms]![frmmain]![txtSearch] & "*"));"

 

[DavidAtWork]

The only way I've success with this scenario is to build a string variable using this method. Inside the local/private sub declare a variable
Dim likeStr as String
likeStr = "*" & [forms]![frmmain]![txtSearch] & "*"

and change the line
WHERE (((tblProducts.ProductName) Like "*" & [forms]![frmmain]![txtSearch] & "*"));"
to
WHERE tblProducts.ProductName Like '" & likeStr & "'"

David

 

[guanfl]

Thanks for the reply. Its showing data but the likestr has no effect on the query.

 

[pr2-eugin]

Hello guanfl, If you copy the SQL code above into a new Query.. i.e. in the Query Builder in SQL view, are you getting the desired result?

PS : you will get the Enter Parameter when you try to run the SQL, just simply type in the Search phrase in the box and see if you get the result..

 

[guanfl]

Yeap it has the desired effect when i just copy and a paste the original code directly into the listbox rowsource and a query in sql view

Original code:

"SELECT tblProducts.ProductID, tblProducts.ProductName, qryTotalProducts.Qty
FROM tblProducts INNER JOIN qryTotalProducts ON tblProducts.ProductID = qryTotalProducts.ProductID
WHERE (((tblProducts.ProductName) Like "*" & [forms]![frmmain]![txtSearch] & "*"));"

 

[DavidAtWork]

There may be a refresh/requery problem depending on when there is text in your txtSearch and at which point the rowsource for the list box is loaded

David

 

[guanfl]

Doubt it there is a requery action on txtsearch change. Like i said it works fine when i copy and paste the query directly into rowsource. Its just that in vba the like clause isnt biting. Maybe it has something to do with "*" in vba? are wildcards recognized in code?

 

[DavidAtWork]

Can you explain how it works, ie the order of events re when text is entered in txtSearch and when the listbox is selected

 

[pr2-eugin]

Is the ListBox also located on frmMain?

If so try using Me.txtSearch instead of Forms!FrmMain!txtSearch..

 

[guanfl]

1. User selects a value from combo box
2. If value of combo = x then rowsource of listbox is "that query"

The text in txtsearch basically utilises the query to dynamically change the results of the listbox with the "Like *txt*" clause.

The code for the txtsearch change event is:

'Create a string (text) variable
    Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = txtSearch.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    txtSearchWord.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.listProducts.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.txtSearchWord) <> 0 And InStr(Len(txtSearchWord), txtSearchWord, " ", vbTextCompare) Then
        Exit Sub
    End If

'Set the focus on the first item in the list box
    Me.listProducts = Me.listProducts.ItemData(1)
    Me.listProducts.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box

    

'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.txtSearch.SetFocus

    If Not IsNull(Len(Me.txtSearch)) Then
        Me.txtSearch.SelStart = Len(Me.txtSearch)
    End If

 

[MSAccessRookie]

I am trying to populate a listbox through its rowsource but i'm not sure if my syntax is right for the like clause.

Not showing any results:

me.list.rowsource = "SELECT tblProducts.ProductID, tblProducts.ProductName, qryTotalProducts.Qty
FROM tblProducts INNER JOIN qryTotalProducts ON tblProducts.ProductID = qryTotalProducts.ProductID
WHERE (((tblProducts.ProductName) Like "*" & [forms]![frmmain]![txtSearch] & "*"));"

If your code is exactly what you have posted, then It contains at least the following two problems. Correct them and see what happens.

• It has three separate lines (SELECT, FROM, and WHERE), which would not be allowed in VBA. The NewLine character is not legal inside of a String, and a String Connect Sequence "& _" should be used instead (See Below).
• In VBA, Quoted SubStrings need Single Quotes instead of Double Quotes, since the Double Quote represents a String Terminator.

me.list.rowsource = "SELECT tblProducts.ProductID, tblProducts.ProductName, qryTotalProducts.Qty[B][COLOR=green]" & _[/COLOR][/B]
[B][COLOR=seagreen]"[/COLOR][/B]FROM tblProducts INNER JOIN qryTotalProducts ON tblProducts.ProductID = qryTotalProducts.ProductID[COLOR=green]"[B] & _[/B][/COLOR]
[B][COLOR=seagreen]"[/COLOR][/B]WHERE (((tblProducts.ProductName) Like [COLOR=red][B]'[/B]*[B]'[/B][/COLOR] & [forms]![frmmain]![txtSearch] & [COLOR=red][B]'[/B]*[B]'[/B][/COLOR]));"

 

[guanfl]

Awesome! thanks MSAccessRookie. Don't you just hate it when the solution is so simple.

 

[DavidAtWork]

Have you tried removing the rowsource setting via the Properties method and add this code immediately before your line above:
Me.listProducts.Requery

Dim likeStr as String
likeStr = "*" & Me.txtSearch & "*"
Me.listProducts.rowsource = "SELECT tblProducts.ProductID, tblProducts.ProductName, qryTotalProducts.Qty
FROM tblProducts INNER JOIN qryTotalProducts ON tblProducts.ProductID = qryTotalProducts.ProductID
WHERE tblProducts.ProductName Like '" & likeStr & "'"

David