Sort a list box...

G

Guest

Below is the code I have for a form that drills down and generate a list box.
I'd like to be able to put some buttons (transparent) along the form that
will enable us to sort it by other fields and options. I'd like to be able
to float over the column header then click to have it resort according to the
location of the button. Any thoughts?

Thanks ever so much!

Private Sub ListResults()

Dim strSQL As String
Dim strProductType As String
Dim strService As String
Dim strMaterial As String
Dim strSize As String
Dim strModFamily As String

strProductType = Me.cboProductFunction.Value & ""
strService = Me.cboService.Value & ""
strMaterial = Me.cboMaterial.Value & ""
strSize = Me.cboSize.Value & ""
strModFamily = Me.cboModFamily.Value & ""

strSQL = ""
strSQL = strSQL & "SELECT [Old Part NO], [Product Type], [Model Family],
Service, [Standard Valve], [Inlet Connection Size], InletConnectionType,
LowestSpringPressure, HighestSpringPressure, Body, [Spring Chamber], Trim,
Diaphragm, BodyConfiguration, LeadTime, ListPrice, [DistNet], [OEM Net],
[Features], Remarks, MaxInletPressure, [Seat Position],Seat, [Body Seat],
[Ball Seat], [Seat Ring]"
strSQL = strSQL & "FROM [Product Profile-Everything] "
strSQL = strSQL & "WHERE ((Not ([Old Part NO]) Is Null) "
If (CheckSelection(strProductType) Or CheckSelection(strService) Or
CheckSelection(strMaterial) Or CheckSelection(strSize) Or
CheckSelection(strModFamily)) Then
If CheckSelection(strProductType) Then strSQL = strSQL & "AND
([Product Type] = '" & strProductType & "') "
If CheckSelection(strService) Then strSQL = strSQL & "AND (Service
LIKE '*" & strService & "*') "
If CheckSelection(strMaterial) Then strSQL = strSQL & "AND ([Body] =
'" & strMaterial & "') "
If CheckSelection(strSize) Then strSQL = strSQL & "AND ([Inlet
Connection Size] = '" & strSize & "') "
If CheckSelection(strModFamily) Then strSQL = strSQL & "AND ([Model
Family] = '" & strModFamily & "') "
End If
strSQL = strSQL & ") "
strSQL = strSQL & "ORDER BY [Model Family], [Inlet Connection Size],
Body, Service, [Standard Valve] "

Me.lstResults.ColumnCount = 25
Me.lstResults.RowSource = strSQL
Me.lstResults.Requery

End Sub
 
M

Marshall Barton

ztyco said:
Below is the code I have for a form that drills down and generate a list box.
I'd like to be able to put some buttons (transparent) along the form that
will enable us to sort it by other fields and options. I'd like to be able
to float over the column header then click to have it resort according to the
location of the button. Any thoughts?

Thanks ever so much!

Private Sub ListResults()

Dim strSQL As String
Dim strProductType As String
Dim strService As String
Dim strMaterial As String
Dim strSize As String
Dim strModFamily As String

strProductType = Me.cboProductFunction.Value & ""
strService = Me.cboService.Value & ""
strMaterial = Me.cboMaterial.Value & ""
strSize = Me.cboSize.Value & ""
strModFamily = Me.cboModFamily.Value & ""

strSQL = ""
strSQL = strSQL & "SELECT [Old Part NO], [Product Type], [Model Family],
Service, [Standard Valve], [Inlet Connection Size], InletConnectionType,
LowestSpringPressure, HighestSpringPressure, Body, [Spring Chamber], Trim,
Diaphragm, BodyConfiguration, LeadTime, ListPrice, [DistNet], [OEM Net],
[Features], Remarks, MaxInletPressure, [Seat Position],Seat, [Body Seat],
[Ball Seat], [Seat Ring]"
strSQL = strSQL & "FROM [Product Profile-Everything] "
strSQL = strSQL & "WHERE ((Not ([Old Part NO]) Is Null) "
If (CheckSelection(strProductType) Or CheckSelection(strService) Or
CheckSelection(strMaterial) Or CheckSelection(strSize) Or
CheckSelection(strModFamily)) Then
If CheckSelection(strProductType) Then strSQL = strSQL & "AND
([Product Type] = '" & strProductType & "') "
If CheckSelection(strService) Then strSQL = strSQL & "AND (Service
LIKE '*" & strService & "*') "
If CheckSelection(strMaterial) Then strSQL = strSQL & "AND ([Body] =
'" & strMaterial & "') "
If CheckSelection(strSize) Then strSQL = strSQL & "AND ([Inlet
Connection Size] = '" & strSize & "') "
If CheckSelection(strModFamily) Then strSQL = strSQL & "AND ([Model
Family] = '" & strModFamily & "') "
End If
strSQL = strSQL & ") "
strSQL = strSQL & "ORDER BY [Model Family], [Inlet Connection Size],
Body, Service, [Standard Valve] "

Me.lstResults.ColumnCount = 25
Me.lstResults.RowSource = strSQL
Me.lstResults.Requery

End Sub
Click to expand...


Save the query's SQL without the ORDER BY clause in a module
level variable. Then each button's click event procedure
can add the clause:

Private Sub ListResults()
. . .
End If
mstrNoSort = strSQL & ") "
strOrderBY = "ORDER BY [Model Family]," _
& "[Inlet Connection Size], Body, Service," _
& "[Standard Valve] "

Me.lstResults.ColumnCount = 25
Me.lstResults.RowSource = mstrNoSort & strOrderBY
End Sub

Sub buttonx_Click()
Dim strOrderBY As String
strOrderBY = "ORDER BY Body"
Me.lstResults.ColumnCount = 25
Me.lstResults.RowSource = mstrNoSort & strOrderBY
End Sub

Note that setting the RowSource automatically causes a
Requery so don't waste time by doing both.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sort a list box - FillList / FillList Headers -- Two part question 3

Top