473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Sorting on the fly

Seth Schrock
2,965 Expert 2GB
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?
Jun 26 '19 #1

✓ answered by Rabbit

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

5 933
Rabbit
12,516 Expert Mod 8TB
I think the field name comes first and then ASC/DESC
Jun 26 '19 #2
Seth Schrock
2,965 Expert 2GB
Duh. I knew it had to be something simple. I'm surprised it didn't throw an error. Oh, well. Thanks Rabbit.
Jun 26 '19 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jun 26 '19 #4
Seth Schrock
2,965 Expert 2GB
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.
Jun 27 '19 #5
NeoPa
32,556 Expert Mod 16PB
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.
Jun 27 '19 #6

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

Similar topics

4
by: dont bother | last post by:
This is really driving me crazy. I have a dictionary feature_vectors{}. I try to sort its keys using #apply sorting on feature_vectors sorted_feature_vector=feature_vectors.keys()...
0
by: ck388 | last post by:
For some reason when I enable the callback feature of the gridview I still get a page refresh, that is it seems like there is a postback that occurs, not a callback which is just supposed to update...
7
by: Federico G. Babelis | last post by:
Hi All: I have this line of code, but the syntax check in VB.NET 2003 and also in VB.NET 2005 Beta 2 shows as unknown: Dim local4 As Byte Fixed(local4 = AddressOf dest(offset)) ...
19
by: Owen T. Soroke | last post by:
Using VB.NET I have a ListView with several columns. Two columns contain integer values, while the remaining contain string values. I am confused as to how I would provide functionality to...
10
by: Sjaakie | last post by:
Hi, I'm, what it turns out to be, fooling around with 3-tier design. At several websites people get really enthusiastic about using custom dataobjects instead of datasets/-tables. While trying to...
4
by: Ambica Jain | last post by:
Hi, I want custom sorting on some of the columns in the datagrid. And i am able to do the same by overriding MouseDown event. However, i need to rebind my datatable to reflect the changes in...
7
by: Kamal | last post by:
Hello all, I have a very simple html table with collapsible rows and sorting capabilities. The collapsible row is hidden with css rule (display:none). When one clicks in the left of the...
1
KevinADC
by: KevinADC | last post by:
Introduction In part one we discussed the default sort function. In part two we will discuss more advanced techniques you can use to sort data. Some of the techniques might introduce unfamiliar...
5
by: lemlimlee | last post by:
hello, this is the task i need to do: For this task, you are to develop a Java program that allows a user to search or sort an array of numbers using an algorithm that the user chooses. The...
5
by: jrod11 | last post by:
hi, I found a jquery html table sorting code i have implemented. I am trying to figure out how to edit how many colums there are, but every time i remove code that I think controls how many colums...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.