Refer to a variable list box in code

[DevelopV]

I found a brilliant and simple method to sort list box's to use.

http://www.comboprojects.com/articles/accS...ingListbox.html

A section of this code includes the following:

   Private Sub SortColumn(strField As String, strOrder As String)
   Dim strSQL As String
   Dim strSorted As String
   strSQL = Replace(Left(Me.lstCustomer.RowSource, Me.ListLength - 1), ";", " ")
   strSorted = strSQL & " ORDER BY " & strField & strOrder & ";"
   Me.lstCustomer.RowSource = strSorted
   Me.lstCustomer.Requery
  End Sub

This is called as follows:

Private Sub cmdSortArea_Click()
   If Me.cmdSortArea.Caption = "" Or Me.cmdSortArea.Caption = "q" Then
       Me.cmdSortArea.Caption = "p"
       SortColumn Me.lstCustomer, "Area", ""
       Else
       Me.cmdSortArea.Caption = "q"
       SortColumn Me.lstCustomer, "Area", " DESC"
   End If
   End Sub

However, this only works if the form has one list box because the code in strSQl = ...... refers to the list box
I would like to enhance the code so that it can work on forms with multiple list box's. So I would like to add the name of the list box to the private sub routine, like this:

Private Sub SortColumn(strList As ListBox, strField As String, strOrder As String)
   Dim strSQL As String
   Dim strSorted As String
    strSQL = Replace(Left(strList.RowSource, Me.ListLength - 1), ";", " ")
    strSorted = strSQL & " ORDER BY " & strField & strOrder & ";"
    Me.lstCustomer.RowSource = strSorted
    Me.lstCustomer.Requery
    End Sub

There is something wrong with: strList As ListBox! and I can't figure it out!

Where is my error and how do I fix it?

It would also make sense to change the Private Sub SortColumn(strField As String, strOrder As String) to public. Then I would not have to include it in every form.

Thanks in advance

 

[DevelopV]

Sorry, I should have explained the error!

strSQL = Replace(Left(strList.RowSource, Me.ListLength - 1), ";", " ")

strList give the error "Variable Not Defined

 

[PHV]

How and where is called SortColumn ?
What is Me.ListLength ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

In Excel VBA it would be something like:

Sub MySub(lst As [blue]MSForms.[/blue]ListBox)

With lst
    .AddItem "One"
    .AddItem "Two"
End With

End Sub

You may try this in Access.

Have fun.

---- Andy

 

[TheAceMan1]

How are ya DevelopV . . .

There is something wrong with: [blue]strList As ListBox[/blue]! and I can't figure it out!

You are certainly correct.
[ol][li]You have no arguement to pass the listbox object too and are attempting to pass the SQL as the object. Adding the arguement for the listbox object changes the code for [blue]SortColumn[/blue] to:

[blue]Private Sub SortColumn([purple][b]usrObj As Object[/b][/purple], strField As String, strOrder As String)
   Dim strSQL As String, strSorted As String
   strSQL = Replace(Left(usrObj, Me.ListLength - 1), ";", " ")
   strSorted = strSQL & " ORDER BY " & strField & strOrder & ";"
   usrObj.RowSource = strSorted 'Automatically Requeries!
End Sub[/blue]

[/li]
[li]To pass the listbox object correctly in [blue]cmdSortArea_Click[/blue] you should have:

[blue]Private Sub cmdSortArea_Click()
   If Me.cmdSortArea.Caption = "" Or Me.cmdSortArea.Caption = "q" Then
      Me.cmdSortArea.Caption = "p"
      SortColumn [purple][b]Me.Controls("lstCustomer")[/b][/purple], "Area", ""
      Else
      Me.cmdSortArea.Caption = "q"
      SortColumn [purple][b]Me.Controls("lstCustomer")[/b][/purple], "Area", " DESC"
   End If

End Sub[/blue]

[/li][/ol]
In parallel with [blue]PHV[/blue] you need to reveal where and how your setting [blue]ListLength[/blue].

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I think this needs to be updated (among other possible issues):

' [green]strSQL = Replace(Left(strList.RowSource, Me.ListLength - 1), ";", " ")[/green]
strSQL = Replace(Left(Me(strList).RowSource, Me.ListLength - 1), ";", " ")

This assumes Me.ListLength is legitimate.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Although not as efficient, this method is a little more flexible and reuseable.

Public Sub sortListOrCombo(ctl As Access.Control, strSort As String)
  Dim rs As DAO.Recordset
  Dim newRS As DAO.Recordset
  If ctl.ControlType = acListBox Or ctl.ControlType = acComboBox Then
    Set rs = ctl.Recordset.Clone
    rs.Sort = strSort
    Set newRS = rs.OpenRecordset
    Set ctl.Recordset = newRS
  End If
End Sub

how to use

Public Sub testSort()
  sortListOrCombo me.someControlName, "Lastname Desc, FirstName"
End Sub

 

[TheAceMan1]

... and a correction in the code I provided for SortColumn ...

[blue]Private Sub SortColumn(usrObj As Object, strField As String, strOrder As String)
   Dim strSQL As String, strSorted As String
   strSQL = Replace(Left([purple][b]usrObj.RowSource[/b][/purple], Me.ListLength - 1), ";", " ")
   strSorted = strSQL & " ORDER BY " & strField & strOrder & ";"
   [purple][b]usrObj.RowSource[/b][/purple] = strSorted [green]'Automatically Requeries![/green]
End Sub[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DevelopV]

Thanks for all the assistance
I have modified the code I found on

http://www.comboprojects.com/articles/accSortingList/accSortingListbox.html

My need to do so was to:
* allow multiple list boxes on a form
* have the sort engine in a module that can be called from any form.
* allow a forced sort order. The original code change the sort order from ascending to descending. I have a need to have a forced sort order. e.g if a record in another list box or a combo box is selected I want the records in another list box in a particular order.

so this is what I have come up with:

Public Sub SortColumn(ctlList As Listbox, strField As String, strOrder As String, Optional strForceSortOrder As String)
    
Dim strSQL As String
Dim strSorted As String
Dim intInString As Long
Dim strSortOrder As String

If strForceSortOrder <> "" And strForceSortOrder <> "Ascending" And strForceSortOrder <> "Descending" Then
    MsgBox "Programme error. Forced sort order incorrect. Code will continue", vbCritical, "Programming Error"
    strForceSortOrder = ""
End If

Select Case strForceSortOrder
    Case ""
        If strOrder = "" Or strOrder = "q" Then
            strSortCaption = "p"
            strSortOrder = ""
            Else
            strSortCaption = "q"
            strSortOrder = " DESC"
        End If
    
    Case "Ascending"
        strSortCaption = "p"
        strSortOrder = ""
        
    Case "Descending"
        strSortCaption = "q"
        strSortOrder = " DESC"
End Select

intInString = InStr(1, ctlList.RowSource, "Order By")

strSQL = ""
If intInString > 0 Then
    strSQL = Left(ctlList.RowSource, intInString - 1)
    Else
    strSQL = ctlList.RowSource
End If

intInString = InStr(1, strSQL, ";")
If intInString > 0 Then strSQL = Left(strSQL, intInString - 1)

strSQL = Trim(strSQL)

strSorted = ""
strSorted = strSQL & " ORDER BY " & strField & strSortOrder & ";"

ctlList.RowSource = strSorted
ctlList.Requery
 
End Sub

This is then called from a form like this:

Private Sub cmdSortArea_Click()

SortColumn Me.lstCustomer, "Area", Me.cmdSortArea.Caption, ""
Me.cmdSortArea.Caption = strSortCaption

End Sub

or

Private Sub cmdSortArea_Click()

SortColumn Me.lstCustomer, "Area", Me.cmdSortArea.Caption, "Descending"
Me.cmdSortArea.Caption = strSortCaption

End Sub

One thing: The font used for the buttons is Wingdings 3 (a standard font with Windows). In this font a lowercase p is arrow up and q is arrow down.

Many thanks to ComboProjects for the original code.

 

[strongm]

Wingdings 3 is is not a standard Windows font. It ships with various applications, one of which is Office.

 

[DevelopV]

Found a bug!!!

If the row source of the list box is:

SELECT [tblProduct].[ProductCode] & " - " & [tblProduct].[Productdescription] AS ProductCodeDescription;

I cannot sort on ProductCodeDescription

 

[PHV]

You can't sort by an alias but by ordinal position of the field in the select clause, eg:
SELECT ProductCode & " - " & Productdescription AS ProductCodeDescription FROM tblProduct ORDER BY 1;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

2 questions:
in your original post you had:
[tt]
Private Sub SortColumn(strList As ListBox, ...
[/tt]
and that did not work,
Now you have:
[tt]
Public Sub SortColumn(ctlList As Listbox, ...
[/tt]
and that works. Hmmm, what changed? Just how you named your parameter?

And, you use (not declared anywhere that I can see) strSortCaption to change the caption of the command button on the Form you call this Sub from.

Why not just pass the command button into your Sub and change its caption there? One place service.
[tt]
Public Sub SortColumn(ctlList As Listbox, strField As String, strOrder As String, [blue]ctlButton As CommandButton[/blue], Optional strForceSortOrder As String)
...
ctlButton.Caption = "q"
...
[/tt]
So you would call it:

Private Sub cmdSortArea_Click()

Call SortColumn(lstCustomer, "Area", [blue]cmdSortArea,[/blue] Me.cmdSortArea.Caption, "")[s]
Me.cmdSortArea.Caption = strSortCaption[/s]

End Sub

Actually, you would NOT have to pass the last Optional parameter, you caould just get it from command button's caption passed.

Have fun.

---- Andy

 

[DevelopV]

Hi Andy

and that works. Hmmm, what changed? Just how you named your parameter? Yes

And, you use (not declared anywhere that I can see) strSortCaption to change the caption of the command button on the Form you call this Sub from. strSortCaption is declared as public elsewhere. Sorry!

Why not just pass the command button into your Sub and change its caption there? One place service.
I am not an expert! Your suggestion is more efficient. Criticism taken, and expected! I am sure someone will do this a whole lot better. But for me it works

 

[Andrzejek]

I understand. I know we have to crawl before we can walk, and then we can run…
I just want to caution you about the use of Public variables: I would use them only if it is absolutely necessary, and there is absolutely no other way to go around it (there usually IS a better way). I’m talking from my own experience of debugging the code and trying to find the problem when you have Public vars and use them in many places in code. It is a nightmare, believe me, I know. :-(


Have fun.

---- Andy