Change Sort Order field

[neeko1226]

The pre-reqs: I have a db that stores project requests by the department that requested them. Each request has an unique ID (project_num) and a priority number (dept_priority). I have never had more than 10 open requests for a given department at a time. I have a priorty form that includes a list box of open requests (for a selected dept) and a combo box with the numbers 1-20 as selection options. I have vba code written to assign the selected number from the combo box to the dept_priority field in my request table on click of a button.

The problem:: If another request for that department already has that priority number, I don't know of a way to change the priority of all the other requests accordingly. For example, if there were 5 requests and I would like to update my 4th priority to now be my first. I need to change the 4 to a 1, and then change my 1 to be 2, 2 to be 3 and 3 to be 4. The logic is beyond me.

Does anyone have a good vba solution for this issue? Any help would be greatly appreciated.

 

[Remou]

You could update 4 to 0 in the initial selection, then update priorities to priority+1. An Update query should suit:

[tt]strSQL="Update tblTable Set Priority = Priority + 1 Where DeptID =" & Me.DeptID
CurrentDB.Execute strSQL, dbFailOnError[/tt]

 

[randy700]

How about...
After the priority is selected, and before the record is written to the table, run an update query that adds 1 to the priorities in the table where the current value is greater than the selected value.

Example:
The user selects a priority of 6.
Your update query would be something like..

"Update tblProjects " & _
"Set Priority = Priority + 1 " & _
"Where DeptID = " & [i]SelectedDeptID[/i] & _
" And Priority >= " & [i]SelectedPriority[/i]

Current projects with priority values 1 through 5 will remain unchanged. Current projects with priority values of 6 and higher will be incremented by 1. The new project can then be inserted with a priority equal to 6.


Randy

 

[neeko1226]

I was able to get it to work with what you suggested, but I had to modify it a bit. Here's what I did:

If IsNull(Me.cmb_Priority) = True Then
    
        MsgBox "Please select a priority number and try again."
        Me.cmb_Priority.SetFocus
        Exit Sub
        
    End If
    
    If IsNull(Me.lb_Projects.Column(1)) = True Then
    
        MsgBox "Please select a request and try again."
        Me.lb_Projects.SetFocus
        Exit Sub
        
    End If
    
    Dim db As Database
    Dim rs As Recordset
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim rscount As Integer
    Dim RP As Integer
    
    Set db = CurrentDb
    
    Set rs = db.OpenRecordset("Select * from tblRequest where Approval = true and CompleteDate is null and Unit = '" & [Forms]![frmMain]![cmbDept] & "' and Dept_Priority = " & Me.cmb_Priority & "")
    
    With rs
        .MoveLast
    End With
    
    rscount = rs.RecordCount
    
    If rscount > 0 Then
    
        Dim msg, Style, Title, Help, Ctxt, Response, MyString

        msg = "There is already a request for this department with a priority of " & Me.cmb_Priority & ".  Are you sure you want to overwrite this priority?"
        Style = vbYesNo + vbCritical
        Title = "Overwrite Priority"
        Response = MsgBox(msg, Style, Title)
    
        If Response = vbYes Then
        
            Set rs1 = db.OpenRecordset("Select * from tblRequest where Approval = true and CompleteDate is null and Unit = '" & [Forms]![frmMain]![cmbDept] & "' and Dept_Priority >= " & Me.cmb_Priority & " and Dept_Priority < " & Me.lb_Projects.Column(0) & " ")
            
            With rs1
                .MoveFirst
                
                Do Until rs1.EOF
                
                    RP = rs1!Dept_Priority + 1
                    
                    .Edit
                
                        rs1!Dept_Priority = RP
                    
                    .Update
                    .MoveNext
                    
                Loop
            
            End With
            
            Set rs2 = db.OpenRecordset("Select * from tblRequest where Approval = true and CompleteDate is null and Unit = '" & [Forms]![frmMain]![cmbDept] & "' and Dept_Priority = " & Me.lb_Projects.Column(0) & " ")
                
            With rs2
                .Edit
                    rs2!Dept_Priority = Me.cmb_Priority
                .Update
            End With
            
        Else
        
            Exit Sub
            
        End If
        
    Else
    
        Set rs2 = db.OpenRecordset("Select * from tblRequest where Approval = true and CompleteDate is null and Unit = '" & [Forms]![frmMain]![cmbDept] & "' and Dept_Priorty = " & Me.lb_Projects.Column(0) & " ")
                
        With rs2
            .Edit
                rs2!Dept_Priority = Me.cmb_Priority
            .Update
        End With
        
    End If
    
    Me.lb_Projects.Requery