Reorder Priority of Records

[Cjbuechler]

I need to reoder the value of the priority field in a table (table1). For example if I have a form called Form1 that displays the records:

RecordName Priority Description
Red 1 blah blah
Blue 2 etc.
Green 3 etc.
Orange 4 etc.
Purple 5 etc.

and if I change the Priority of Green to 0 I want the other priorities to change to account for it rather than just doing a plain sort by priority.

RecordName Priority Description
Green 0 etc.
Red 1 blah blah
Blue 2 etc.
Orange 3 etc.
Purple 4 etc.

I figured setting up an event procedure on Update on the Priority field that basically selects the table, sorts them by priority and steps through resetting the priority starting at 1 and moving up from there would be fine but any attemps I have made don't do the resetting part of things. Any ideas?

 

[Zion7]

I'm assuming, there will be many variations, on how you re-prioritize? Not always sending record, to bottom or top of list. Can a priority 5, become a priority 3?
If this is the case...

I would first, use the OnFocus event to get the previous value, & use a public variable, to store it. "iOldValue".
And get RecordName "sRecordName"

Then with Beforeupdate event, store new value, with a different public variable. "iNewValue"

Then using loop structure, have a condition where,

firstly,


Do Until rec.EOF
If rec!RecordName <> sRecordName Then
If rec!txtPriority < iNewValue Then
rec!txtPriority = rec!txtPriority - 1
Else if rec!txtPriority > iNewValue And < iOldValue Then
rec!txtPriority = rec!txtPriority + 1
End If
End If
rec.Update
rec.MoveNext
Loop

Now, bear with me here, with the logic. I'm not sure it's on the nose, but I hope the principle or potential is clear,(and viable).

Again, depending on the different variations of reprioritizing, it may require more elaborate logic.

I'd be more than happy, to figure this out further, if need be. Let me know, otherwise hope this helps & good luck!

 

[DanJR]

you can also make use of the OldValue property.

This is just another variation, which assumes that you start in a valid state (ie your priorities are already ordered, are within the range of 1 to max records, and are increments of 1). If this is the case, then you only need to 'visit' and update those records that require updating. It also assumes that your underlying recordset is ordered by priority. Half of it is validation.

Private Sub txtPriority_AfterUpdate()
  Dim rs As DAO.Recordset
  Dim lngCurrentOrder As Long
  
  lngCurrentOrder = Me.txtPriority.Value
  Set rs = Me.Recordset
  Me.Requery
  rs.FindFirst "[Order] = " & lngCurrentOrder
  
  Set rs = Nothing
End Sub

Private Sub txtPriority_BeforeUpdate(Cancel As Integer)
  Dim rs As DAO.Recordset
    
  ' check for change in value
  If Me.txtPriority.Value = Me.txtPriority.OldValue Then GoTo CleanUp
  
  Set rs = Me.RecordsetClone
  varBookmark = Me.Bookmark
  
  ' check for valid order
  ' must be in range of 1 and max record count
  If (rs.BOF And rs.EOF) Then
    If Me.txtPriority.Value <> 1 Then
      MsgBox "Invalid Priority"
      Cancel = True
      GoTo CleanUp
    End If
  End If
  
  If Me.txtPriority.Value < 1 Then
    MsgBox "Invalid Priority"
    Cancel = True
    GoTo CleanUp
  End If
  
  rs.MoveLast
  If Me.NewRecord Then
    If Me.txtPriority.Value > rs.RecordCount + 1 Then
      MsgBox "Invalid Priority"
      Cancel = True
      GoTo CleanUp
    End If
  Else
    If Me.txtPriority.Value > rs.RecordCount Then
      MsgBox "Invalid Priority"
      Cancel = True
      GoTo CleanUp
    End If
  End If

  ' ok, re-order priority here
  rs.Bookmark = varBookmark
  If Me.txtPriority.Value > Me.txtPriority.OldValue Then
    rs.MoveNext
    If rs.EOF Then GoTo CleanUp
    Do While rs.Fields("Order") <= Me.txtPriority.Value
      rs.Edit
      rs.Fields("Order").Value = rs.Fields("Order").Value - 1
      rs.Update
      rs.MoveNext
      If rs.EOF Then GoTo CleanUp
    Loop
  Else
    rs.MovePrevious
    If rs.BOF Then GoTo CleanUp
    Do While rs.Fields("Order") >= Me.txtPriority.Value
      rs.Edit
      rs.Fields("Order").Value = rs.Fields("Order").Value + 1
      rs.Update
      rs.MovePrevious
      If rs.BOF Then GoTo CleanUp
    Loop
  End If
  
CleanUp:
  Set rs = Nothing
End Sub