426,122 Members | 927 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

# Re numbering a field when changing another record

 P: 27 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. Feb 26 '09 #1
6 Replies

 Expert Mod 15k+ P: 31,419 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. Feb 26 '09 #2

 P: 27 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. Feb 27 '09 #3

 Expert 5K+ P: 8,623 @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. Expand|Select|Wrap|Line Numbers 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 Expand|Select|Wrap|Line Numbers 'Move Priority 50 to 5, and re-adjust other Priorities If fReassignPriorities(50, 5) Then   MsgBox "Success" Else   MsgBox "False" End If Feb 27 '09 #4

 Expert 100+ P: 489 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. Expand|Select|Wrap|Line Numbers 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 Feb 27 '09 #5

 Expert 2.5K+ P: 2,653 @DonRayner It's certainly a case when SQL is a preferable choice. Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers 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 Feb 27 '09 #6

 Expert Mod 15k+ P: 31,419 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 : Expand|Select|Wrap|Line Numbers 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. Feb 27 '09 #7