Re numbering a field when changing another record

[Randoz]

I have a simple data base with items that need to be worked on. Each record has a unique number assigned as a priority. The numbers are 1-100 for example, in the order that they are to be worked on. I want to be able to change record 50 form 50th priority to another priority (manualy) say to priority 5 and then change all records previously 5-49 to 6-50 priority and allows for the new priority 5. Any help on how to do this would be greatly appriceated. By the way I am using Access 2003.

[NeoPa]

So, am I right in thinking you have the priority order (rather than value) stored as a value in each record?

More questions.
Is there always a set number of records in the table?
Do you need to handle lowering the priority order value too?

This is working against the concept of databases but can be done. Don't expect something like this to be too straightforward though. Databases were never intended to be used this way.

[Randoz]

The priority number is based on how many records there are. It is a unique field and changes when ever another record is changed to a higher priority. The total number of records can increase or decrease depending on when jobs are completed or new ones are added. The Priority field must be able to be manualy changed for a record and then change all other priority fields in the other records so all jobs can be completed in order.

[ADezii]

@Randoz
If I understand you correctly, this code can act as a Template from which you can work. The manner in which I read the problem, the solution was not quite that obvious as my colleague NeoPa seems to indicate. The Algorithm deals with 100, Unique, Priorities ranging from 1 to 100. When you pass the Original Priority and the New, requested Priority, to the Function, it makes the appropriate Priority Shift (+1) within the Specified Range, then the Final Shift from Original to New via the retention of the original Primary Key Value. I'll post the code, but it is much simpler downloading the Attachment and visually observing the Priority Shifts within the Range. I've also included backup copies of the Main Table, since you will need the Original Values in the Table (tblItems) for each Test. Good Luck, and let us know how you make out.

Public Function fReassignPriorities(bytOriginalPriority As Byte, bytNewPriority As Byte) As Boolean
On Error GoTo Err_fReassignPriorities
Dim MyDB As DAO.Database
Dim rstPriority As DAO.Recordset
Dim lngPK As Long
 
fReassignPriorities = False         'Initialize to False
 
'Obtain the Primary Key Value for the Record containing the Original Priority
lngPK = DLookup("[Item_ID]", "tblItems", "[Priority] = " & bytOriginalPriority)
 
'Priority Values must be >=1 and <=100
If (bytOriginalPriority < 1 Or bytOriginalPriority > 100) Or (bytNewPriority < 1 Or bytNewPriority > 100) Then
    Exit Function
ElseIf bytOriginalPriority = bytNewPriority Then    'Priorities cannot be equal
    Exit Function
End If
 
'If we get here, we have 2 Priority Values that are in the prioper
'Range (1 to 100) and not equal to one another
If bytOriginalPriority > bytNewPriority Then    'Moving Priority UP (^)
  Set MyDB = CurrentDb()
  Set rstPriority = MyDB.OpenRecordset("tblItems", dbOpenDynaset)
    With rstPriority
      Do While Not .EOF
        If ![Priority] >= bytNewPriority And ![Priority] < bytOriginalPriority Then
          .Edit
            ![Priority] = (![Priority] + 1)   'Shift Priorities within Range DOWN
          .Update
        End If
        .MoveNext
      Loop
    End With
Else
  'something for you to do
End If
 
rstPriority.Close
Set rstPriority = Nothing
 
'The Original Priority now becomes the New Priority, there are now 2
'Records with the Original Priority Value, because the Priority of
'the Prior Record was pushed up. That's why we need the lngPK value.
CurrentDb.Execute "Update tblItems Set tblItems.[Priority] = " & bytNewPriority & _
                  " Where tblItems.[Item_ID] = " & lngPK
 
fReassignPriorities = True
 
Exit_fReassignPriorities:
  Exit Function
 
Err_fReassignPriorities:
  MsgBox Err.Description, vbExclamation, "Error in fReassignPriorities()"
  fReassignPriorities = False
    Resume Exit_fReassignPriorities
End Function

'Move Priority 50 to 5, and re-adjust other Priorities
If fReassignPriorities(50, 5) Then
  MsgBox "Success"
Else
  MsgBox "False"
End If

[DonRayner]

Here is a solution using SQL. It will handle changing the priority up or down and the marking of an item as complete.

TableName change to the name of the table containing your records
FieldName change to the name of the priority field in your table
ControlName change to the name of the control on your form
Completed a boolean (yes/no) field in your table and on your form.

Option Compare Database
Public intVar As Integer
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.ControlName = DMax("[FieldName]", "TableName") + 1
    Exit Sub
End If
 
Dim stSQL
 
If Me.Completed Then
    stSQL = "UPDATE TableName SET TableName.FieldName = [FieldName]-1" & _
            " WHERE (((Table1.FieldName)>" & intVar & " And Table1.FieldName<>0));"
    Me.ControlName = 0
ElseIf intVar > Me.ControlName Then
    stSQL = "UPDATE TableName SET TableName.FieldName = [FieldName]+1" & _
            " WHERE (((TableName.FieldName)<" & intVar & " And (TableName.FieldName)>" & Me.ControlName - 1 & "));"
ElseIf intVar < Me.Model Then
    stSQL = "UPDATE TableName SET TableName.FieldName = [FieldName]-1" & _
            " WHERE (((TableName.FieldName)>" & intVar & " And (TableName.FieldName)<" & Me.ControlName + 1 & "));"
Else
    Exit Sub
End If
    intVar = Me.ControlName
    DoCmd.RunSQL stSQL
    Me.ControlName = intVar
 
End Sub
 
Private Sub Form_Current() 
If Me.NewRecord or Me.ControlName = 0 Then               
    Me.ControlName.Locked = True
    Me.Completed.Locked = true
    Exit Sub
Else
    Me.ControlName.Locked = False
    Me.Completed.Locked = False
End If
intVar = Me.ControlName
End Sub

[FishVal]

@DonRayner
It's certainly a case when SQL is a preferable choice.

Private Sub Form_Current() 
If Me.NewRecord or Me.ControlName = 0 Then               
    Me.ControlName.Locked = True
    Me.Completed.Locked = true
    Exit Sub
Else
    Me.ControlName.Locked = False
    Me.Completed.Locked = False
End If
intVar = Me.ControlName
End Sub

I would suggest a slightly more elegant coding.

Private Sub Form_Current()
 
Dim blnLockState as Boolean
 
With Me
    blnLockState = .NewRecord or .ControlName = 0
    .ControlName.Locked = blnLockState
    .Completed.Locked = blnLockState
    intVar = .ControlName
End With
 
End Sub

[NeoPa]

Some SQL to move a record from position 50 to position 5 is relatively straightforward and can easily be built up and exectued from within the code of a form. It's not a concept that I'd recommend, but should you wish to proceed anyway, the SQL would be of the form :

UPDATE [YourTable]
SET [Priority]=[Priority]+1
WHERE [Priority] Between 5 And 49

After running this of course, would be the time to set the [Priority] value of the selected record to 5.

NB. The +1 on line #2 should be a -1 when moving a record to a lower (higher numbered) proiority. This should be handled in the code.