Hi all,
I'm struggling with the sortable feature applies to my listbox.
I have a search form to filter unbound text boxes then populated the result into a listbox:
unbound text boxes: txtName,txtCity, txtState
unbound listbox: lboCustInfo
The code for searching work nicely.
I have the following sorting function, but it's not working as I wanted. When I click on column headers to sort, I lost my search result and it sorted the full list of records instead.
Any help please?
I'm struggling with the sortable feature applies to my listbox.
I have a search form to filter unbound text boxes then populated the result into a listbox:
unbound text boxes: txtName,txtCity, txtState
unbound listbox: lboCustInfo
The code for searching work nicely.
Code:
Dim strSQL As String, strOrder As String, strWhere As String
strSQL = "SELECT qryInfo.CustID, qryInfo.Name, qryInfo.City, qryInfo.State " & _
"FROM qryInfo"
strWhere = "WHERE"
strOrder = "ORDER BY qryInfo.CustID;"
If Not IsNull(Me.txtName) Then
strWhere = strWhere & " (qryInfo.Name) Like '*" & Me.txtName & "*' AND" '
End If
If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " (qryInfo.City) Like '*" & Me.txtCity & "*' AND"
End If
If Not IsNull(Me.txtState) Then
strWhere = strWhere & " (qryInfo.State) Like '*" & Me.txtState & "*' AND"
End If
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
Me.lboInfo.RowSource = strSQL & " " & strWhere & "" & strOrder
Any help please?
Code:
Const mcRowSourceSortCol1 = mcRowSourceBasis & " Order By 1;"
Const mcRowSourceSortCol1Desc = mcRowSourceBasis & " Order By 1 Desc;"
Const mcRowSourceSortCol2 = mcRowSourceBasis & " Order By 2;"
Const mcRowSourceSortCol2Desc = mcRowSourceBasis & " Order By 2 Desc;"
Const mcRowSourceSortCol3 = mcRowSourceBasis & " Order By 3;"
Const mcRowSourceSortCol3Desc = mcRowSourceBasis & " Order By 3 Desc;"
Const mcRowSourceSortCol4 = mcRowSourceBasis & " Order By 4;"
Const mcRowSourceSortCol4Desc = mcRowSourceBasis & " Order By 4 Desc;"
Private Sub cmdSortCol1_Click()
If lboInfo.RowSource = mcRowSourceSortCol1 Then
lboInfo.RowSource = mcRowSourceSortCol1Desc
Else
lboInfo.RowSource = mcRowSourceSortCol1
End If
End Sub
Private Sub cmdSortCol2_Click()
If lboInfo.RowSource = mcRowSourceSortCol2 Then
lboInfo.RowSource = mcRowSourceSortCol2Desc
Else
lboInfo.RowSource = mcRowSourceSortCol2
End If
End Sub
Private Sub cmdSortCol3_Click()
If lboInfo.RowSource = mcRowSourceSortCol3 Then
lboInfo.RowSource = mcRowSourceSortCol3Desc
Else
lboInfo.RowSource = mcRowSourceSortCol3
End If
End Sub
Private Sub cmdSortCol4_Click()
If lboIssues.RowSource = mcRowSourceSortCol4 Then
lboInfo.RowSource = mcRowSourceSortCol4Desc
Else
lboInfo.RowSource = mcRowSourceSortCol4
End If
End Sub