Code Not Populating Listbox

[CharlesWhiteman]

I'm using the following code to allow users to populate a list box. It works and performs as expressed when the txt box in blank but doesnt populate the list box when it should be. Any advice? Much appriciated.

Private Sub cmdSearchCust_Click()
Dim strSql As String
Dim strCriteria As String
strCriteria = "WHERE "
If Not IsNull(Me.txtCustName) Then
strCriteria = strCriteria & Me.txtCustName.Value

strSql = "SELECT CustID, CustName FROM TblCust" & strCriteria
Forms("FrmDoSearchOne").LbResults.RowSource = strSql
Else
MsgBox "Enter Criteria", vbExclamation, "No Criteria"
End If

End Sub

 

[NigelShaw]

Hi,

from my experience with list boxes, you have to requery the form and or the listbox to see any changes for example

Me.Requery ( will requery the object you are in )
Forms!YourForm.Requery ( will requery the form from another object )
Me.ListBoxName.Requery will requery your listbox from the object you are in )
forms!YourForm!ListBoxName.Requery ( will requery you listbox on a form from another object )

Nidge

 

[CharlesWhiteman]

Hi and thanks for replying. Id already try that but it made no difference. Interestingly, if I remove strCriteria from the end of the line of code the listbox shows all customers.

(strSql = "SELECT CustID, CustName FROM TblCust" & strCriteria)

Its only not working with that strCriteria. No error message just nothing.

 

[NigelShaw]

Hi,

have you tried putting the "WHERE " into the sql string and tagging strCriteria on the end on its own?

you could test the sql value by putting a msgbox underneath to see what it is calling

Private Sub cmdSearchCust_Click()
Dim strSql As String
Dim strCriteria As String
If Not IsNull(Me.txtCustName) Then
strCriteria = Me.txtCustName.Value

strSql = "SELECT CustID, CustName FROM TblCust [COLOR="red"]WHERE[/COLOR] " & strCriteria
[B][COLOR="Red"]MsgBox strSql[/COLOR][/B]
Forms("FrmDoSearchOne").LbResults.RowSource = strSql
Else
MsgBox "Enter Criteria", vbExclamation, "No Criteria"
End If

End Sub

at least you will see what the Sql string is trying to call



Nidge.

PS. what part of the midlands? im in Leicester.

 

[CharlesWhiteman]

Hi Nidge, thanks for that and have ran some tests. the msg box shows that the select statement is trying to call the correct data but its just not showing up in the listbox.

 

[NigelShaw]

Hello mate

would it be anything to do with the way the SQL is called? As I think anyway, SQL string will run anything inside quotes so while the test message box shows you the true string, when it's called it only gives what is contained inside the string as it is called direct

have you tried setting a dim to hold the full SQL value and then setting this as the SQL statement?

Just a thought


Nidge

 

[NigelShaw]

Do you have a sample db you can post?

Nidge

 

[datAdrenaline]

I beleive the issue you are experiencing is due to the value that you do not have a space between the WHERE and the table name in the code you have posted, in addition, I assume the value you type in txtCustomerName is a TEXT value, and as such needs to be delimited in the SQL statment you set in the RowSource property. BUT here is the big kicker, you have not specified a field to filter ...

Give the following a go and see if it helps you out:

Private Sub cmdSearchCust_Click()
    
    Dim strSql As String
    
    'Build SQL for the new list
    If Len(Me.txtCustName & "") = 0 Then
        MsgBox "Enter Criteria", vbExclamation, "No Criteria"
    Else
        strSql = "SELECT CustID, CustName" & _
                 " FROM TblCust" & _
                 " WHERE CustName = '" & Me.txtCustName & "'"
    End If
    
    'Set or Clear the SQL statement for the RowSource
    Forms("FrmDoSearchOne").LbResults.RowSource = strSql
    
End Sub

 

[CharlesWhiteman]

That worked! Great and thanks will have a look into that and also post a copy of the Db which might be of use to other users.