Sort records

  • Thread starter Thread starter Liz Hansen
  • Start date Start date
L

Liz Hansen

Hi,

I have a main form with a subform. I have added the below code to my
subform to sort my employee records. This works, however only one column is
sorted. For instance, let's say I have 3 employees; Elsa Smith, Karen Smith
and Michael Smith and I sort by last name, the order should be as follows:

Smith Elsa
Smith Karen
Smith Michael

However it will sort the last name correct, but the first name will be in
random order. Or let's say I have 3 employee named Michael Appleby, Michael
Funk and Michael Woods then my list should be:

Michael Appleby
Michael Funk
Michael Woods

But again, it will sort the first name correct, but the last name will be in
random order. Is there something I could add to my code in order to sort
both columns?

Thanks,

Anna



Private Function SortCol(strColumnName As String)
If Me.OrderBy = strColumnName Then
Me.OrderBy = strColumnName & " DESC"
Else
Me.OrderBy = strColumnName
End If
Me.OrderByOn = True
End Function


Private Sub Last_Name_DblClick(Cancel As Integer)
SortCol ("LastName")
End Sub

Private Sub First_Name_DblClick(Cancel As Integer)
SortCol ("FirstName")
End Sub
 
Liz said:
Hi,

I have a main form with a subform. I have added the below code to my
subform to sort my employee records. This works, however only one column is
sorted. For instance, let's say I have 3 employees; Elsa Smith, Karen Smith
and Michael Smith and I sort by last name, the order should be as follows:

Smith Elsa
Smith Karen
Smith Michael

However it will sort the last name correct, but the first name will be in
random order. Or let's say I have 3 employee named Michael Appleby, Michael
Funk and Michael Woods then my list should be:

Michael Appleby
Michael Funk
Michael Woods

But again, it will sort the first name correct, but the last name will be in
random order. Is there something I could add to my code in order to sort
both columns?

Thanks,

Anna



Private Function SortCol(strColumnName As String)
If Me.OrderBy = strColumnName Then
Me.OrderBy = strColumnName & " DESC"
Else
Me.OrderBy = strColumnName
End If
Me.OrderByOn = True
End Function


Private Sub Last_Name_DblClick(Cancel As Integer)
SortCol ("LastName")
End Sub

Private Sub First_Name_DblClick(Cancel As Integer)
SortCol ("FirstName")
End Sub
Click to expand...
Why can't you test for the column name being "LastName" and do something
like this.

If strColumnName="LastName" then
Me.OrderBy="LastName, FirstName"
EndIf
 
Liz said:
I have a main form with a subform. I have added the below code to my
subform to sort my employee records. This works, however only one column is
sorted. For instance, let's say I have 3 employees; Elsa Smith, Karen Smith
and Michael Smith and I sort by last name, the order should be as follows:

Smith Elsa
Smith Karen
Smith Michael

However it will sort the last name correct, but the first name will be in
random order. Or let's say I have 3 employee named Michael Appleby, Michael
Funk and Michael Woods then my list should be:

Michael Appleby
Michael Funk
Michael Woods

But again, it will sort the first name correct, but the last name will be in
random order. Is there something I could add to my code in order to sort
both columns?


Private Function SortCol(strColumnName As String)
If Me.OrderBy = strColumnName Then
Me.OrderBy = strColumnName & " DESC"
Else
Me.OrderBy = strColumnName
End If
Me.OrderByOn = True
End Function


Private Sub Last_Name_DblClick(Cancel As Integer)
SortCol ("LastName")
End Sub

Private Sub First_Name_DblClick(Cancel As Integer)
SortCol ("FirstName")
End Sub
Click to expand...

Sort on two columns. Everything you have should work just
by changing the two procedure calls
SortCol "LastName, FirstName"
and
SortCol "FirstName, LastName"

BTW, since it does not return a value, SortCol should be a
Sub, not a Function.
 
Aah, one would think I should have been able to figure that one out myself!

Thanks very much.

:-)
 
Back
Top