Renumbering a column in a datasheet

[Frizzellio]

I have a datasheet that displays a ranking column (1 to however many records). When a user changes a particular record's ranking (say from 8 to 2), how can I automatically update the other rankings? (ie old 2 becomes 3, 3 becomes 4, etc)

 

[MajP]

Here is my strategy.
1)Provide a ranking column. "sngRankSort". Make this a single
2)Sort the forms query by the rank sort column.
3)As the form opens write to the rank sort field sequentially.
0,1,2,3,4,5
4)The order of events for a control is before update, after update, lost focus. In the before update event see what change took place
"None" no change in the value
"Up" the rank order went up (i.e. 2 to 7)
"Down" the rank order went down (7 to 2)

5) In the lost focus event (the value has actually already been saved but then you can change it by adding +- .5 depending on if you are moving it to a higher or lower ranking). If going up make the new value .5 more. (so 7 becomes 7.5). Now renumber the records. Going down is the opposite.

Public changeType As String
Private Sub Form_Load()
  Call updateRankOrder
End Sub

Public Sub updateRankOrder()
  Dim rs As DAO.Recordset
  Dim bk As String
  Set rs = CurrentDb.OpenRecordset("qryRankOrder", dbOpenDynaset)
  Do While Not rs.EOF
    rs.Edit
    rs.Fields("sngRankOrder") = rs.AbsolutePosition
    rs.Update
    rs.MoveNext
  Loop
End Sub

Private Sub sngRankOrder_BeforeUpdate(Cancel As Integer)
  If Int(Me.sngRankOrder) <> Me.sngRankOrder Then
    MsgBox "Provide an integer"
    Cancel = True
  Me.Undo
  End If
  If Me.sngRankOrder.OldValue = Me.sngRankOrder.Value Then
    changeType = "None"
  ElseIf Me.sngRankOrder.OldValue > Me.sngRankOrder.Value Then
    changeType = "Down"
  Else
    changeType = "Up"
  End If

End Sub
Private Sub sngRankOrder_LostFocus()
 If Not changeType = "None" Then
   If changeType = "Down" Then
      Me.sngRankOrder = Me.sngRankOrder - 0.5
   ElseIf changeType = "Up" Then
      Me.sngRankOrder = Me.sngRankOrder + 0.5
   End If
    Me.Dirty = False
    Call updateRankOrder
    Me.Requery
 End If
End Sub

 

[Frizzellio]

Thanks for the code MajP. Unfortunately, if I change a rank to something of the same rank (up or down), I run into a problem. For example, if I change something from 2 to 1, and 1 already exists, it reverts to 2. Same thing going the other way. Here's the code:

Public changeType As String
Private Sub Form_Load()
  Call updateRankOrder
End Sub

Public Sub updateRankOrder()
  Dim rs As DAO.Recordset
  Dim bk As String
  Dim strSQL As String
  
  strSQL = "SELECT * FROM tblEquipment WHERE [Funded] = No ORDER BY [Bin_Assigned], [Board_Rank]"
  Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  Do While Not rs.EOF
    rs.Edit
    rs.Fields("Board_Rank") = rs.AbsolutePosition + 1
    rs.Update
    rs.MoveNext
  Loop
End Sub

Private Sub Board_Rank_BeforeUpdate(Cancel As Integer)
  If Int(Me.Board_Rank) <> Me.Board_Rank Then
    MsgBox "Provide an integer"
    Cancel = True
  Me.Undo
  End If
  If Me.Board_Rank.OldValue = Me.Board_Rank.Value Then
    changeType = "None"
  ElseIf Me.Board_Rank.OldValue > Me.Board_Rank.Value Then
    changeType = "Down"
  Else
    changeType = "Up"
  End If

End Sub
Private Sub Board_Rank_LostFocus()
 If Not changeType = "None" Then
   If changeType = "Down" Then
      Me.Board_Rank = Me.Board_Rank - 0.5
   ElseIf changeType = "Up" Then
      Me.Board_Rank = Me.Board_Rank + 0.5
   End If
    Me.Dirty = False
    Call updateRankOrder
    Me.Requery
 End If
End Sub

Note the only change to your code (besides table/field names) is adding 1 to the absolute value to start the sort at 1. Even with this +1 not included, I have the same issue. I'm not sure if changeType is actually being recorded, but I could be wrong. Any ideas?

 

[MajP]

This works for me so there could be a couple issues. First ensure that your Board_Rank field is a floating point (single, double) not integer. If this field is an integer it will not work.
Put some debug.print statements in to see what is happening:

Do While Not rs.EOF
rs.Edit

debug.print rs.Fields("Board_Rank") & " " & rs.AbsolutePosition + 1

rs.Fields("Board_Rank") = rs.AbsolutePosition + 1
rs.Update
rs.MoveNext
Loop

This will show if this update is running.

 

[MajP]

I tried this on a different form and table, and works fine. For simplicity (and a little speed) why not define this query

"SELECT * FROM tblEquipment WHERE [Funded] = No ORDER BY [Bin_Assigned], [Board_Rank]"

as a stored query (i.e "qryEquipmentSort")to ensure it works. Then
strSql = "qryEpuipmentSort"

This method works fine, and I have used this interface concept before to manually sort a list. However, this is not how I would do it. I would build a large listbox with up and down buttons on the side. Click the up button the record moves up, click the down button the record moves down. Then when you are all done (i.e. you close the form) it resorts your table based on the listbox order. Here are the steps.

1) As the listbox opens you need to read your values from a table,query and convert to a string.
2)As you move a record up and down you switch the list index of the records.
3)When complete use DAO/ADO similar to UpdateRankOrder to read the order in the listbox and write to your table.

You can also simulate drag and drop using your current form using the mousedown, mouse up events.

 

[MajP]

Here is a example of using a list box to sort your records. I think this interface works much better than allowing the user to type in the rank order field:

http://www.4shared.com/file/60738100/9468342/DynamicSort.html

1) The list box needs to be based on a query that includes as the first column the primary key for your table.
2)When the form opens the convertToValueList will convert the rowsource into a string. This allows you to delete and add specific items to a list, and thus allowing you to move an item up and down a list.

 

[Frizzellio]

MajP,

many thanks for the guidance and examples! I will give these a go when I return to work Tuesday.

Cheers!

Chuck