Public Function to Sort Any Continuous Form Column 1

[waubain]

I have a number of continuous forms where when the header label is clicked it sorts that column. Each column has its own sub, but I am getting more forms and would like to convert this to a Public Sub.
Here is sub for one column. The name of the control is txtClientName and the control source is ClientName.

Option Explicit
Const ClickColor As Long = 12957359
Const BaseColor As Long = 15790320

Sub lblSortClientName_Click()
    lblSortClientName.BackColor = ClickColor
    If Me.OrderBy = "ClientName" Then
        Me.OrderBy = "ClientName DESC"
    Else
        Me.OrderBy = "ClientName"
    End If
    Me.OrderByOn = True
    lblSortClientName.BackColor = BaseColor
End Sub

When I call the Public Sub I can see the label change color and then back so that part works, but column does not sort. I am not getting any error messages. The column when loaded is not sorted on this column. The Public Sub resides in a separate module.

Sub lblSortClientName_Click()
     Call SortColumn("frmClientInfo", "lblSortClientName", "ClientName"
End Sub

Option Explicit
Const ClickColor As Long = 12957359
Const BaseColor As Long = 15790320

Public Sub SortColumn(ByVal frmName As String, lblName As String, colnName As String)

    Forms(frmName).Controls(lblName).BackColor = ClickColor
    If Forms(frmName).OrderBy = colnName Then
        Forms(frmName).OrderBy = colnName & "DESC"
      Else
        Forms(frmName).OrderBy = colnName
    End If

    Forms(frmName).OrderBy = True
    Forms(frmName).Controls(lblName).BackColor = BaseColor
End Sub

Thank you in advance.

You don't know what you don't know...

 

[dhookom]

Did your code compile?
Did you attempt to set a breakpoint in your code so you can see what is happening?
You seem to be missing a closing ):

Call SortColumn("frmClientInfo", "lblSortClientName", "ClientName"

Duane
Hook'D on Access
MS Access MVP

 

[waubain]

Yes it compiled without a problem and stepped through as expected. The missing closing was a cut and paste error when posting.

I stepped through it again and then spotted the problem.

Forms(frmName).OrderBy = True

Should have been

Forms(frmName).OrderByOn = True

I was missing the On. Works as expected.
Thanks for responding, it force me to step through it again. I also noticed that "DESC" should be " DESC"

You don't know what you don't know...

 

[MajP]

Learn to use debug.print to error check.
Forms(frmName).OrderBy = colnName & "DESC"
debug.print forms(frmName).orderby

You will see it prints
SomeFieldDESC

It cannot sort on that
Forms(frmName).OrderBy = colnName & " DESC

 

[MajP]

Sorry I see that you caught your mistake already. Put my point about learning to error check your own code still stands.

 

[Andrzejek]

I would suggest something like this:

Public Sub SortColumn(ByRef frmForm As MSForm, ByRef lbl As Label, ByRef strColName As String)
...

this way instead of:[tt]
Forms(frmName).OrderBy = strColName[/tt]
you may use:[tt]
frmForm.OrderBy = strColName[/tt]

and instead of:[tt]
Forms(frmName).Controls(lblName).BackColor = BaseColor[/tt]
you may use:[tt]
lbl.BackColor = BaseColor[/tt]

Just a suggestion....

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[waubain]

Thank you everyone for the suggestions and lessons.

You don't know what you don't know...