Netflix-like Queue for Form?

[vbaInet]

Just give them a group of Sort options:

* Clear All or Reset
* Fill

The rest they will do manually.

 

[tkepongo]

Just give them a group of Sort options:

* Clear All or Reset
* Fill

The rest they will do manually.

What's the "Fill" option?

 

[vbaInet]

1
2
3
4
.
.
and so on

 

[tkepongo]

1
2
3
4
.
.
and so on

So this approach allows duplicate SortOrders right? If I put a constraint, it might be a pain for the auditors...unless there's some way to automatically update the SortOrders.

 

[vbaInet]

Your PKs should be Question_ID and Sort_ID. And yes, that will allow duplicates - giving the auditors more flexibility.

 

[vbaInet]

Oh my, I just had a eureka moment. It's lots of code but if you can learn how it will solve your sorting problems quite easily.

Look into a Listview control.

 

[vbaInet]

Actually, no. Don't waste your time with the listview. I just remember it only offers a checkbox.

 

[tkepongo]

Sorry it took awhile to get back, but I found a solution!

So I decided to use a continuous form and add a SortOrder column instead of creating another table.

My continuous form displays the questions associated with the Audit_ID and has an UP and a Down button for each record. The buttons essentially swaps the SortOrder with the record below or above (depending which button you click). Here's the code:

Private Sub btnMoveUp_Click()
'If the SortOrder is 1, then do nothing
If Me.SortOrder = 1 Then
    MsgBox ("Can't move question up if it is already on top of the list.")
Else
    Dim RecNum As Integer 'Unique ID of the record
    Dim MySort As Integer 'Current SortOrder for the question selected
    Dim Audit_ID As Long
    Dim Question_ID As Integer
    Dim OtherQSort As Integer 'The SortOrder for the other question that will be moved
    RecNum = Me.ID
    Audit_ID = Me.Audit_ID
    Question_ID = Me.Question_ID
    MySort = Me.SortOrder
    MySort = MySort - 1
    OtherQSort = Me.SortOrder
    'First, move the SortOrder DOWN by minus 1 for the question above the selected question
    strSQL = "UPDATE dbo_jntbl_AuditToQuestion SET SortOrder= " & OtherQSort & " WHERE Audit_ID= " & Audit_ID & " AND SortOrder = " & MySort & ""
    CurrentDb.Execute strSQL, dbFailOnError Or dbSeeChanges
    'Then move the SortOrder UP by plus 1 for the selected question
    strSQL = "UPDATE dbo_jntbl_AuditToQuestion SET SortOrder = " & MySort & " WHERE Audit_ID= " & Audit_ID & " AND Question_ID = " & Question_ID & ""
    CurrentDb.Execute strSQL, dbFailOnError Or dbSeeChanges
    'Refresh the list
    Me.Requery
    'Keep the focus on the same record after requery
    Set rstClone = Me.RecordsetClone
    With rstClone
        .FindFirst "ID=" & RecNum
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
            Me.btnMoveUp.SetFocus
        End If
    .Close
    End With
    Set rstClone = Nothing
End If
End Sub

All of the records have a SortOrder after the record has been inserted. When I insert the record, the SortOrder is NULL. I have a trigger that will find the Audit_ID of the last record inserted and assign the Count(*) to the record's SortOrder--so I'm bumping the new question down to the bottom of the Sort.

Here's the trigger:

CREATE TRIGGER SortAuditQuestions ON jntbl_AuditToQuestion FOR INSERT AS
BEGIN
DECLARE @AuditID as integer
SET @AuditID = (SELECT Audit_ID
FROM    jntbl_AuditToQuestion
WHERE   ID = (SELECT MAX(ID)  FROM jntbl_AuditToQuestion))
PRINT @AuditID

UPDATE jntbl_AuditToQuestion 
SET SortOrder = (SELECT count(*) 
FROM jntbl_AuditToQuestion
WHERE Audit_ID= @AuditID) 
WHERE Audit_ID= @AuditID
And SortOrder Is Null
END

Now I can easily move a question up and down its Sort list

 

[vbaInet]

Well done!