By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Sorting on the fly

Seth Schrock
Expert 2.5K+
P: 2,937
I have created a class module that should allow me to sort records in a form based on selections made by the user. In this case, I have the user double click the label to sort by the corresponding field. Everything works great except that it doesn't sort anything. Part of what it does is change the label captions to include up or down arrows to display the sort order and that works great. Here is the code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Const UpArrow As Long = 11165
  5. Private Const DownArrow As Long = 11167
  6.  
  7. Private frm As Form
  8. Private strControl As String
  9. Private strOrderBy As String
  10. Private strCaption As String
  11. Private intOrder As Integer   '1 = Ascending, 2 = Descending
  12.  
  13.  
  14. Public Sub Setup(FilterForm As String, Optional ParentForm As String = "")
  15.  
  16. If ParentForm = "" Then
  17.     Set frm = Forms(FilterForm)
  18. Else
  19.     Set frm = Forms(ParentForm).Controls(FilterForm).Form
  20. End If
  21.  
  22. End Sub
  23.  
  24. Public Sub ChangeSort(NewControl As String)
  25.  
  26. With frm
  27.     If strControl <> NewControl And strControl & "" <> "" Then
  28.         .Controls(strControl & "_Label").Caption = .Controls(strControl & "_Label").tag
  29.     End If
  30.  
  31.     If strControl <> NewControl Then
  32.         strOrderBy = "ASC [" & .Controls(NewControl).ControlSource & "]"
  33.         strCaption = .Controls(NewControl & "_Label").tag & " " & ChrW(UpArrow)
  34.         strControl = NewControl
  35.         intOrder = 1
  36.     Else
  37.         If intOrder = 1 Then
  38.             strOrderBy = "DESC [" & .Controls(NewControl).ControlSource & "]"
  39.             strCaption = .Controls(NewControl & "_Label").tag & " " & ChrW(DownArrow)
  40.             intOrder = 2
  41.         Else
  42.             strOrderBy = "ASC [" & .Controls(NewControl).ControlSource & "]"
  43.             strCaption = .Controls(NewControl & "_Label").tag & " " & ChrW(UpArrow)
  44.             intOrder = 1
  45.         End If
  46.  
  47.     End If
  48.  
  49.     .OrderBy = strOrderBy
  50.     .OrderByOn = True
  51.     .Controls(strControl & "_Label").Caption = strCaption
  52.  
  53. End With
  54.  
  55. End Sub
  56.  
I know that my frm reference is working, because the labels are getting their captions changed correctly. I have tried adding a Refresh and a Requery at the end, but that doesn't work either. I have put debug.prints for both the OrderBy and OrderByOn properties and they don't return what I've set them to. Any ideas why this isn't working?
3 Weeks Ago #1

✓ answered by Rabbit

I think the field name comes first and then ASC/DESC

Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,357
I think the field name comes first and then ASC/DESC
3 Weeks Ago #2

Seth Schrock
Expert 2.5K+
P: 2,937
Duh. I knew it had to be something simple. I'm surprised it didn't throw an error. Oh, well. Thanks Rabbit.
3 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,419
Hi Seth.

It seems like you're still going the long & laborious way round when dealing with SQL issues. Check out How to Debug SQL String.
3 Weeks Ago #4

Seth Schrock
Expert 2.5K+
P: 2,937
From my troubleshooting, I didn't think that it was a SQL error, but something assigning the value to the OrderBy property, since I couldn't ever get it to return the value I had assigned it. I'm a little rusty on Access though.
3 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,419
It's certainly less obvious than most. Filtering is done using a string in SQL format but many fail to realise it should be treated in the same way. As you say, it's often hard even to click that it's such an issue but I guess now you can see it's really a two-step issue and only the second step is needed to discover the fault. As is often the case, once that's been discovered the VBA part of the process is very easy to fix.
3 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.