By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 1,029 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Using buttons to change sort order

Seth Schrock
Expert 2.5K+
P: 2,931
Introduction

There have been a few times recently that I have had to make it so that users could easily change the order of records. This comes in handy when planning tasks and you don't necessarily enter them in the correct order. So instead of just having the user have to manually change the numbers in the sort field, I created four buttons: Move First, Move Up, Move Down, and Move Last. Originally, I had created a separate procedure to take care of each of these moves. I also had it fairly locked down in that each procedure would only work on that one recordset. This part wasn't a big deal when I only had one set of buttons in the database. Now, I have a database where I will have multiple sets, so I decided that I needed to make my procedures more flexible. So this is what I have come up with.


Solution
First some setup information. For simplicity sake, I'll make the table that stores the sorted data have only three fields and have it setup like this:
Expand|Select|Wrap|Line Numbers
  1. tblSortedData
  2. DataID, PK, AutoNumber
  3. Description, Text
  4. DataOrder, Number(Integer)
Now, I like basing my forms on queries instead of the table directly as this allows me to reference fields that aren't included in my form, so I have created a query that just selects everything from tblSortedData and I will call it qrySortedData.
Now this data will be viewed in a subform called sfrmSortedData and it has the following controls:
Expand|Select|Wrap|Line Numbers
  1. Control Name     Control Source
  2. txtDescription   Description
  3. txtDataOrder     DataOrder
This subform will be on a main form called frmMain and it will have a subform control titled the same as the subform name (sfrmSortedData) and four buttons: cmdMoveFirst, cmdMoveUp, cmdMoveDown, and cmdMoveLast. That should get us ready for the code.

I am including my error handling that calls a separate function, but not the separate function itself. If you want to see it, I can post it later.

First, I created an enum to make it easier to work with the different directions and put it in my utilities module (modUtilities).
Expand|Select|Wrap|Line Numbers
  1. Public Enum eMoveDirection
  2.     ssFirst = 1
  3.     ssUp = 2
  4.     ssDown = 3
  5.     ssLast = 4
  6. End Enum
Now I will make the sub that will actually do the changing of the sort values. To start with, here is the empty shell of the sub including all the declarations of the variables:
Expand|Select|Wrap|Line Numbers
  1. Public Sub ChangeOrder(Move As Integer, ByVal CurrentOrder As Integer, _
  2.                             Query As String, SortField As String)
  3. On Error GoTo Error_Handler
  4.  
  5. Dim db As DAO.Database
  6. Dim strDMax As String
  7. Dim rst As DAO.Recordset
  8. Dim rst1 As DAO.Recordset
  9. Dim rst2 As DAO.Recordset
  10. Dim NewValue As Integer
  11. Dim strCriteria1 As String
  12. Dim strCriteria2 As String
  13. Dim i As Integer
  14.  
  15.  
  16.  
  17.  
  18.  
  19. Exit_Procedure:
  20.     On Error Resume Next
  21.  
  22.     rst.Close
  23.     rst1.Close
  24.     rst2.Close
  25.  
  26.     Set db = Nothing
  27.     Set rst = Nothing
  28.     Set rst1 = Nothing
  29.     Set rst2 = Nothing
  30.  
  31.     Exit Sub
  32.  
  33. Error_Handler:
  34.     Call ErrorMessage(Err.Number, Err.Description, "modUtilities: ChangeOrder")
  35.     Resume Exit_Procedure
  36.     Resume
  37.  
  38. End Sub
First, let me explain the arguments of the procedure.

Move: This tells the procedure which move action will be performed
CurrentOrder: This passes the current value of the sort field
Query: This passes the SQL string that will allow the procedure to open the recordset for the particular set of buttons that is being operated.
SortField: This passes the name of the field that is being used to sort the data.

I will explain most of the other variables as they come up, but I will explain the rst, rst1, rst2, strCriteria1, and strCriteria2. rst contains all the records that are being sorted. rst1 will only ever hold the record that is the current record that the user is trying to move. strCriteria1 will hold the information needed to get that record out of rst. rst2 is similar, but it holds all the records that will have to be moved around in order for the selected record to end up where it needs to go. Likewise, strCriteria2 holds the string information to get the needed records from rst.

Now we will open the rst recordset:
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  2. Set rst = db.OpenRecordset(Query, dbOpenDynaset)
Now we will get the new value and set strCriteria1 and strCriteria2 using a Select Case statement.
Expand|Select|Wrap|Line Numbers
  1. strCriteria1 = SortField & " = " & CurrentOrder
  2.  
  3. Select Case Move
  4.     Case ssUp
  5.         NewValue = CurrentOrder - 1
  6.         strCriteria2 = SortField & " = " & NewValue
  7.  
  8.     Case ssDown
  9.         NewValue = CurrentOrder + 1
  10.         strCriteria2 = SortField & " = " & NewValue
  11.  
  12.     Case ssFirst
  13.         NewValue = 1
  14.         strCriteria2 = SortField & " < " & CurrentOrder
  15.  
  16.     Case ssLast
  17.         strDMax = "SELECT TOP 1 " & Mid(Query, 8) & " ORDER BY " & SortField & " DESC"
  18.  
  19.         Set rst2 = db.OpenRecordset(strDMax, dbOpenDynaset)
  20.  
  21.         With rst2
  22.             NewValue = .Fields(SortField)
  23.             .Close
  24.             Set rst2 = Nothing
  25.         End With
  26.  
  27.         strCriteria2 = SortField & " > " & CurrentOrder
  28.  
  29. End Select
So we are testing for which move is needed. Note: each procedure calling this sub has already tested that the current record isn't the first record for ssUp and ssFirst and that it isn't the last record when calling ssDown and ssLast. strCriteria1 always only selects the record that is currently selected. So, if we are trying to move the record up in the order, NewValue will be one less than the current value. And since we are only moving the selected record up one space, there is only one record that has to move in response and that is the record that currently holds the value that is equal to the new value. Thus we have lines 5 & 6. The same basic principle is true for ssDown, except that the NewValue will be one greater than the current value.
For ssFirst, NewValue is automatically 1 as that is the highest priority value possible. strCriteria2 becomes all records whose sort order is less than the current order value (this includes the record that is currently number one).
If we are trying to move to the last record, we first have to determine what that is as there could be 10 records or there could be 50. So, since the domain function DMax doesn't support variables in the Domain Name slot, I used my old trick of using a "SELECT TOP 1" query to get the value and then I assign this to NewValue. strCriteria2 becomes all records that have a order value higher (lower ranked) values than the current record.

Now we can use strCriteria1 and strCriteria2 to filter rst to come up with rst1 and rst2.
Expand|Select|Wrap|Line Numbers
  1. rst.Filter = strCriteria1
  2. Set rst1 = rst.OpenRecordset
  3.  
  4. rst.Filter = strCriteria2
  5. Set rst2 = rst.OpenRecordset
Now for the magic that actually changes the values.
Expand|Select|Wrap|Line Numbers
  1. 'Non current records
  2. With rst2
  3.     .MoveLast
  4.     .MoveFirst
  5.  
  6.     If .RecordCount > 1 Then
  7.  
  8.         'MoveLast or MoveFirst
  9.         If NewValue > CurrentOrder Then
  10.  
  11.             'MoveLast
  12.             For i = CurrentOrder To (NewValue - 1)
  13.                 .Edit
  14.                 .Fields(SortField) = i
  15.                 .Update
  16.                 .MoveNext
  17.             Next i
  18.  
  19.         Else
  20.  
  21.             'MoveFirst
  22.             For i = NewValue To (CurrentOrder - 1)
  23.                 .Edit
  24.                 .Fields(SortField) = i + 1
  25.                 .Update
  26.                 .MoveNext
  27.             Next i
  28.  
  29.         End If
  30.  
  31.     Else
  32.  
  33.         'MoveUp and MoveDown
  34.         .Edit
  35.         .Fields(SortField) = CurrentOrder
  36.         .Update
  37.  
  38.     End If
  39.  
  40. End With
  41.  
  42. 'Current record
  43. With rst1
  44.     .Edit
  45.     .Fields(SortField) = NewValue
  46.     .Update
  47. End With
I start with the non current records. First I have to do a .MoveLast/.MoveFirst so that I get a correct record count. I then test to see if there is more than one record. If there is, then the move is either a move first or move last. If there isn't, then it was either a move up or move down (which would behave the exact same way). So, starting with .RecordCount > 1 = True... We then test to see if it was move last or move first that was called. I won't actually do the math for you as it would take a lot more explanation in an already large article, so I'll just leave it at "it works correctly".
If .RecordCount was equal to 1, then it is a simple setting of the record to the CurrentOrder which is now the old value.
Also easy is the setting of the current record to the new value as seen in lines 42 - 47. That finishes the procedure. Here it is in its entirety.
Expand|Select|Wrap|Line Numbers
  1. Public Sub ChangeOrder(Move As Integer, CurrentOrder As Integer, _
  2.                             Query As String, SortField As String)
  3. On Error GoTo Error_Handler
  4.  
  5. Dim db As DAO.Database
  6. Dim strDMax As String
  7. Dim rst As DAO.Recordset
  8. Dim rst1 As DAO.Recordset
  9. Dim rst2 As DAO.Recordset
  10. Dim NewValue As Integer
  11. Dim strCriteria1 As String
  12. Dim strCriteria2 As String
  13. Dim i As Integer
  14.  
  15.  
  16. Set db = CurrentDb
  17. Set rst = db.OpenRecordset(Query, dbOpenDynaset)
  18.  
  19. strCriteria1 = SortField & " = " & CurrentOrder
  20.  
  21. Select Case Move
  22.     Case ssUp
  23.         NewValue = CurrentOrder - 1
  24.         strCriteria2 = SortField & " = " & NewValue
  25.  
  26.     Case ssDown
  27.         NewValue = CurrentOrder + 1
  28.         strCriteria2 = SortField & " = " & NewValue
  29.  
  30.     Case ssFirst
  31.         NewValue = 1
  32.         strCriteria2 = SortField & " < " & CurrentOrder
  33.  
  34.     Case ssLast
  35.         strDMax = "SELECT TOP 1 " & Mid(Query, 8) & " ORDER BY " & SortField & " DESC"
  36.  
  37.         Set rst2 = db.OpenRecordset(strDMax, dbOpenDynaset)
  38.  
  39.         With rst2
  40.             NewValue = .Fields(SortField)
  41.             .Close
  42.             Set rst2 = Nothing
  43.         End With
  44.  
  45.         strCriteria2 = SortField & " > " & CurrentOrder
  46.  
  47. End Select
  48.  
  49.  
  50.  
  51. rst.Filter = strCriteria1
  52. Set rst1 = rst.OpenRecordset
  53.  
  54. rst.Filter = strCriteria2
  55. Set rst2 = rst.OpenRecordset
  56.  
  57. 'Non current records
  58. With rst2
  59.     .MoveLast
  60.     .MoveFirst
  61.  
  62.     If .RecordCount > 1 Then
  63.  
  64.         'MoveLast or MoveFirst
  65.         If NewValue > CurrentOrder Then
  66.  
  67.             'MoveLast
  68.             For i = CurrentOrder To (NewValue - 1)
  69.                 .Edit
  70.                 .Fields(SortField) = i
  71.                 .Update
  72.                 .MoveNext
  73.             Next i
  74.  
  75.         Else
  76.  
  77.             'MoveFirst
  78.             For i = NewValue To (CurrentOrder - 1)
  79.                 .Edit
  80.                 .Fields(SortField) = i + 1
  81.                 .Update
  82.                 .MoveNext
  83.             Next i
  84.  
  85.         End If
  86.  
  87.     Else
  88.  
  89.         'MoveUp and MoveDown
  90.         .Edit
  91.         .Fields(SortField) = CurrentOrder
  92.         .Update
  93.  
  94.     End If
  95.  
  96. End With
  97.  
  98. 'Current record
  99. With rst1
  100.     .Edit
  101.     .Fields(SortField) = NewValue
  102.     .Update
  103. End With
  104.  
  105.  
  106. Exit_Procedure:
  107.     On Error Resume Next
  108.  
  109.     rst.Close
  110.     rst1.Close
  111.     rst2.Close
  112.  
  113.     Set db = Nothing
  114.     Set rst = Nothing
  115.     Set rst1 = Nothing
  116.     Set rst2 = Nothing
  117.  
  118.     Exit Sub
  119.  
  120. Error_Handler:
  121.     Call ErrorMessage(Err.Number, Err.Description, "modUtilities: ChangeOrder")
  122.     Resume Exit_Procedure
  123.     Resume
  124.  
  125. End Sub
Now for the procedures that call it. They are mostly self explanatory, so I won't go into great detail. Here is the procedure for cmdMoveLast's OnClick event:
Expand|Select|Wrap|Line Numbers
  1. Dim strQuery As String
  2. Dim lngRecord As Long
  3.  
  4. strQuery = "SELECT * FROM tblSortedData"
  5.  
  6. With Me.sfrmSortedData.Form   
  7.  
  8.     lngRecord = !DataID
  9.  
  10.     If Not .Recordset.RecordCount = .CurrentRecord Then
  11.         ChangeOrder ssLast, .Recordset!DataOrder, strQuery, "DataOrder"
  12.     Else
  13.         MsgBox "You cannot move this record higher in the sort order."    
  14.     End If
  15.  
  16.     Me.sfrmSortedData.Requery
  17.     .Recordset.FindFirst "DataOrder = " & lngRecord
  18. End With
strQuery is the query string that gets passed to the ChangeOrder procedure. Line 17 makes the record that just got moved be the selected record. Without it, if you did a move up on the second record, the second record would remain highlighted. It would just be a different record than the one you clicked originally. The same applies to any other move. The procedure for cmdMoveDown is the same except that you would need to change the first argument to ssDown. The procedures for cmdMoveFirst and cmdMoveUp are
Expand|Select|Wrap|Line Numbers
  1. Dim strQuery As String
  2. Dim lngRecord As Long
  3.  
  4. strQuery = "SELECT * FROM tblQuestionLevel " & _
  5.            "WHERE PenalCodeID_fk = " & Me.OpenArgs
  6.  
  7. With Me.frmDT_Stage2.Form
  8.  
  9.     lngRecord = !LevelID
  10.  
  11.     If Not .CurrentRecord = 1 Then
  12.         ChangeOrder ssFirst, .Recordset!QLOrder, strQuery, "QLOrder"
  13.     Else
  14.         MsgBox "You cannot move this record higher in the sort order."
  15.     End If
  16.  
  17.     Me.frmDT_Stage2.Requery
  18.     .Recordset.FindFirst "LevelID = " & lngRecord
  19. End With
All that needs to change is the ssFirst to ssUp. The main difference between these procedures (other than the move direction) is the record testing.



Solution

I have here a procedure that allows all the move directions in the same procedure and it isn't tied to one set of buttons, so it is very reusable. This allows for easy reordering of records by users without the risk for values to be duplicated or skipped.

Please let me know if you have a better way, have spotted an error, or that it has helped you. Comments of any sort are very welcome.
Apr 16 '13 #1
Share this Article
Share on Google+