I am trying to populate a list box based on selections from two combo boxes.
Code:
Private Sub cbxSearchBy_Change()
Dim strSQL As String
strSQL = ""
'Creates SQL Query
Select Case Me.cbxSearchFor.Value
Case "Work Order"
Select Case Me.cbxSearchBy.Value
Case "Order Number"
strSQL = "SELECT OrderNo FROM WOTracking ORDER BY OrderNo;"
Case "UPC"
strSQL = "SELECT DISTINCT UPC FROM WOTracking ORDER BY UPC;"
Case "Employee"
strSQL = "SELECT DISTINCT Employee FROM WOTracking ORDER BY Employee;"
End Select
Case "Defect Event"
Select Case Me.cbxSearchBy.Value
Case "Customer Name"
strSQL = "SELECT CustomerName FROM DefectEvents ORDER BY CustomerName;"
Case "UPC"
strSQL = "SELECT DISTINCT UPC FROM DefectEvents ORDER BY UPC;"
Case "Order Number"
strSQL = "SELECT OrderNo FROM DefectEvents ORDER BY OrderNumber;"
Case "Status"
strSQL = "SELECT DISTINCT Status FROM DefectEvents ORDER BY Status;"
Case "Defect Category"
strSQL = "SELECT DISTINCT Category FROM DefectEvents ORDER BY Category;"
End Select
End Select
'Populates Listbox
Me.lstSearchResults.Visible = True
Me.lstSearchResults.RowSource = strSQL
Me.lstSearchResults.Requery
End Sub
Currently, The header in the listbox is filling in with the SQL statement, and not populating. Assisting would be greatly appreciated.