Controls to Sort Continuous Form Records with an Integer Priority Field

[padlocked17]

Good Evening,
Im trying to take a list of records that are displayed on a continuous form and have a control on each record that allows me to “promote” and “demote” each item on the list. Im basically wanting to allow the user to place each record in a priority order of their choosing. I’ve got a Priority field that is an integer to capture the records position, but I’d like to have up and down arrows on each record that changes the values of the Priority field as the user “moves” records up and down via the display of the form. I know I’ve seen an example of this before, but I can’t for the life of me find one. Does anyone have a good reference or example of a best practice to handle this?

Thanks!

 

[June7]

This is not a trivial requirement. Requires updating record from the one moved in front of to the end to reassign the Priority value. If this is a simultaneous multi-user db, users will conflict. Review https://www.tek-tips.com/viewthread.cfm?qid=1636127

 

[Auntiejack56]

I agree with June7, although the fact that users require their records in a particular order can mean that they have effective control over that set of records anyway. Where I have used this technique, it was for the admin user, so no risk of conflict. Use the following at your peril.
I know you have asked for this to be on a continuous form, and it will work just as well there with little adjustment. I'd suggest that you don't have a pair of buttons on each record, just a single pair on the parent. But it's up to you.
Assume your table (tblItemList) has three fields, and a form with a listbox (named scrList) to show them:

The code is (excluding all the error handling etc):

Private Sub cmdDown_Click()
    If IsNumeric(Me.scrList) Then
        If Me.scrList.ListIndex < Me.scrList.ListCount - 1 Then
            goPromote "Down"
            Me.scrList.Requery
        End If
    End If
End Sub

Private Sub cmdUp_Click()
    If IsNumeric(Me.scrList) Then
        If Me.scrList.ListIndex > 0 Then
            goPromote "Up"
            Me.scrList.Requery
        End If
    End If
End Sub

Private Function goPromote(parDirection As String)
Dim dbs As DAO.Database, strSQL As String, nCurrentPriority As Long, nSwapPriority As Long, nCurrentID As Long, nSwapID As Long
    Set dbs = CurrentDb()
    
    ' Get the ID of the record you are on, and its Priority
    nCurrentPriority = Me.scrList.Column(2)
    nCurrentID = Me.scrList.Column(0)
    
    ' If you are moving up, the Priority will swap with the next *lower* record and vice versa, so find the ID of this record you are swapping places with
    Select Case parDirection
    Case "Up"
        nSwapPriority = DMax("ItemPriority", "tblItemList", "ItemPriority < " & nCurrentPriority)
    Case "Down"
        nSwapPriority = DMin("ItemPriority", "tblItemList", "ItemPriority > " & nCurrentPriority)
    End Select
    
    ' Get the ID of the swap record, because just using its Priority as an identifier could be problematic
    nSwapID = DLookup("ID", "tblItemList", "ItemPriority = " & nSwapPriority)
    
    ' Swap the Priorities (don't calculate what they *should* be in case there are inconsistencies e.g. gaps or duplicate Priorities)
    strSQL = "UPDATE tblItemList AS t SET t.ItemPriority = " & nSwapPriority & " WHERE t.ID = " & nCurrentID & ";"
    dbs.Execute strSQL
    
    strSQL = "UPDATE tblItemList AS t SET t.ItemPriority = " & nCurrentPriority & " WHERE t.ID = " & nSwapID & ";"
    dbs.Execute strSQL
    
End Function

There are possible problems if the list you start with has inconsistencies in the Priority numbering. There are ways around this, but if conflict with other users is not a problem, then reset your priorities before you start.

 

[arnelgp]

there is similar discussion here, with some demos.

Solved - Move row up and down in Listbox

Hello together, I am the first time here. I have a huge problem, maybe you can help me. I have a listbox and two buttons. I would like to move a selected element in the listbox up with the UP-BUTTON. With the DOWN-BUTTON, i would like to move the selected element down. The data which you can...

www.access-programmers.co.uk

 

[theDBguy]

Hi. You can also take a look here.

Rearrange Record Sort Order

 

[padlocked17]

This is not a trivial requirement. Requires updating record from the one moved in front of to the end to reassign the Priority value. If this is a simultaneous multi-user db, users will conflict. Review https://www.tek-tips.com/viewthread.cfm?qid=1636127

Thanks all. I typically try and avoid Listbox's (personal preference) but not opposed. Help me understand if there is a better way of doing this. I was wanting to have them sorted in real-time just from the usability perspective (easier to see how you have something ranked if it shows in order). I'm trying to see how this would cause conflicts any differently that someone simply changing the "Priority" of 1 record at a time. Is it just that the single record would be locked and multi-user's unable to edit just that one specific record whereas what I'm proposing wouldn't? Just trying to understand. At the end of the day, I need user(s) to be able to take a list of items and rank order them. If there is a better way of going about it, I'm all ears. I find it highly unlikely that I would have a user conflict with this particular feature (usually it's a team lead ... 1 per team who would be making these changes).

 

[The_Doc_Man]

You said "simply changing the "Priority" of 1 record at a time"

But you would NOT be changing the priority of a single record at a time. This is a zero-sum game. For every promotion there is at least one corresponding demotion. It is zero-sum because you are not removing anyone from the list, only changing order. AuntieJack's solution is a good example of this. You say you would have only one user likely to be doing this at a time. No matter HOW you implement this, if you have the potential of multiple users, you are ALSO looking at a potential for destructive interference (which is computer-speak for "left hand doesn't know what the right hand is doing.")

 

[Pat Hartman]

If you have a large set of data that is too big to see on one page, you might want to consider a minimal code option. I rarely number lists with an increment of 1. I usually use at least 10. Using that as a starting concept, If the user wants to "move" record 140 to be after 90, they change the 140 to 95. After they are finished moving things around, they can press the renumber button and a query sorts the list and generates new secquence numbers incremented by 10. I'll upload a sample that shows how to generate custom user-friendly unique identifies but the subform shows a simple sequence number generator with this "move" and renumber feature.

 

[MajP]

MS Access: custom control built in another language

the code is old and there is a small change you need to make in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window If fGetClassName(hWnd_VSB) = "scrollBar" Then this needs to be changed to If fGetClassName(hWnd_VSB) = "NUIscrollBar"...

www.access-programmers.co.uk

 

[Auntiejack56]

Another aspect is that when doing this on a subform, you are likely to have unsaved changes on a subform record when doing the promote. This will give you an error ("Another user is changing this record").
You may have noticed that other packaged software doesn't usually do it your way - they have a "Resequence" button which pops up a listbox showing enough data to allow the user to resequence. Then after doing the promote in the popup, and closing it, the subform is requeried to show the new sequence. I totally recommend this as a better approach, I would never do it on the subform.
Jack

 

[padlocked17]

Another aspect is that when doing this on a subform, you are likely to have unsaved changes on a subform record when doing the promote. This will give you an error ("Another user is changing this record").
You may have noticed that other packaged software doesn't usually do it your way - they have a "Resequence" button which pops up a listbox showing enough data to allow the user to resequence. Then after doing the promote in the popup, and closing it, the subform is requeried to show the new sequence. I totally recommend this as a better approach, I would never do it on the subform.
Jack

I can get on board with that. Does that solve any of my multi-user possible issues?

 

[Pat Hartman]

Another aspect is that when doing this on a subform, you are likely to have unsaved changes on a subform record when doing the promote.

Only if you don't know that it is up to you to force a save before running an update query that might include the dirty record.

 

[Auntiejack56]

No it doesn't solve multi-user issues. And note carefully what Pat said, that you have to make sure that the subform records are saved before you do an update, no matter where you site the update code.
I prefer to separate the updates by providing a separate form to resequence, and my experience is that the worst that can happen is that you trap an error and tell the user that the records are being used by someone else, try again. That's an additional benefit of the separate form - the error is unambiguous.
But as you've gathered, resequencing records is not trivial, and user requirements can differ markedly.

 

[Pat Hartman]

If you always save the current record before you run an update query, or open a form or report, you will not have a conflict with yourself. You can always run into a conflict with another user when you run an update query which is why I don't recommend them except for tightly selected data that is very unlikely to cause a conflict.

 

[MajP]

I can get on board with that. Does that solve any of my multi-user possible issues?

If you want each user to have their own sort order, that is pretty simple. Currently you have a field in the main table to store the sort order. Get rid of that and create a junction tbl stored locally on the users front end.

tblRecordSort
  RecordID_FK  ' foreign key to the table of records
  SortOrder     ' long integer

Now when you open the form
1. Run an insert query to insert any records not in the local sort order table
2. You can give the new records a sort of either -1, or something large 9999999 to put them all at the end or the beginning
3. your form's recordsource is a query linking the two sorted by sort order then any other fields
4. If you use my list sorter then simply modify the insert and delete query code to insert and delete from the junction table.