By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
6 Replies


NeoPa
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

ADezii
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
  1. Public Function fReassignPriorities(bytOriginalPriority As Byte, bytNewPriority As Byte) As Boolean
  2. On Error GoTo Err_fReassignPriorities
  3. Dim MyDB As DAO.Database
  4. Dim rstPriority As DAO.Recordset
  5. Dim lngPK As Long
  6.  
  7. fReassignPriorities = False         'Initialize to False
  8.  
  9. 'Obtain the Primary Key Value for the Record containing the Original Priority
  10. lngPK = DLookup("[Item_ID]", "tblItems", "[Priority] = " & bytOriginalPriority)
  11.  
  12. 'Priority Values must be >=1 and <=100
  13. If (bytOriginalPriority < 1 Or bytOriginalPriority > 100) Or (bytNewPriority < 1 Or bytNewPriority > 100) Then
  14.     Exit Function
  15. ElseIf bytOriginalPriority = bytNewPriority Then    'Priorities cannot be equal
  16.     Exit Function
  17. End If
  18.  
  19. 'If we get here, we have 2 Priority Values that are in the prioper
  20. 'Range (1 to 100) and not equal to one another
  21. If bytOriginalPriority > bytNewPriority Then    'Moving Priority UP (^)
  22.   Set MyDB = CurrentDb()
  23.   Set rstPriority = MyDB.OpenRecordset("tblItems", dbOpenDynaset)
  24.     With rstPriority
  25.       Do While Not .EOF
  26.         If ![Priority] >= bytNewPriority And ![Priority] < bytOriginalPriority Then
  27.           .Edit
  28.             ![Priority] = (![Priority] + 1)   'Shift Priorities within Range DOWN
  29.           .Update
  30.         End If
  31.         .MoveNext
  32.       Loop
  33.     End With
  34. Else
  35.   'something for you to do
  36. End If
  37.  
  38. rstPriority.Close
  39. Set rstPriority = Nothing
  40.  
  41. 'The Original Priority now becomes the New Priority, there are now 2
  42. 'Records with the Original Priority Value, because the Priority of
  43. 'the Prior Record was pushed up. That's why we need the lngPK value.
  44. CurrentDb.Execute "Update tblItems Set tblItems.[Priority] = " & bytNewPriority & _
  45.                   " Where tblItems.[Item_ID] = " & lngPK
  46.  
  47. fReassignPriorities = True
  48.  
  49. Exit_fReassignPriorities:
  50.   Exit Function
  51.  
  52. Err_fReassignPriorities:
  53.   MsgBox Err.Description, vbExclamation, "Error in fReassignPriorities()"
  54.   fReassignPriorities = False
  55.     Resume Exit_fReassignPriorities
  56. End Function
Expand|Select|Wrap|Line Numbers
  1. 'Move Priority 50 to 5, and re-adjust other Priorities
  2. If fReassignPriorities(50, 5) Then
  3.   MsgBox "Success"
  4. Else
  5.   MsgBox "False"
  6. End If
Feb 27 '09 #4

DonRayner
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
  1. Option Compare Database
  2. Public intVar As Integer
  3.  
  4. Private Sub Form_BeforeUpdate(Cancel As Integer)
  5. If Me.NewRecord Then
  6.     Me.ControlName = DMax("[FieldName]", "TableName") + 1
  7.     Exit Sub
  8. End If
  9.  
  10. Dim stSQL
  11.  
  12. If Me.Completed Then
  13.     stSQL = "UPDATE TableName SET TableName.FieldName = [FieldName]-1" & _
  14.             " WHERE (((Table1.FieldName)>" & intVar & " And Table1.FieldName<>0));"
  15.     Me.ControlName = 0
  16. ElseIf intVar > Me.ControlName Then
  17.     stSQL = "UPDATE TableName SET TableName.FieldName = [FieldName]+1" & _
  18.             " WHERE (((TableName.FieldName)<" & intVar & " And (TableName.FieldName)>" & Me.ControlName - 1 & "));"
  19. ElseIf intVar < Me.Model Then
  20.     stSQL = "UPDATE TableName SET TableName.FieldName = [FieldName]-1" & _
  21.             " WHERE (((TableName.FieldName)>" & intVar & " And (TableName.FieldName)<" & Me.ControlName + 1 & "));"
  22. Else
  23.     Exit Sub
  24. End If
  25.     intVar = Me.ControlName
  26.     DoCmd.RunSQL stSQL
  27.     Me.ControlName = intVar
  28.  
  29. End Sub
  30.  
  31. Private Sub Form_Current() 
  32. If Me.NewRecord or Me.ControlName = 0 Then               
  33.     Me.ControlName.Locked = True
  34.     Me.Completed.Locked = true
  35.     Exit Sub
  36. Else
  37.     Me.ControlName.Locked = False
  38.     Me.Completed.Locked = False
  39. End If
  40. intVar = Me.ControlName
  41. End Sub
Feb 27 '09 #5

FishVal
Expert 2.5K+
P: 2,653
@DonRayner
It's certainly a case when SQL is a preferable choice.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current() 
  2. If Me.NewRecord or Me.ControlName = 0 Then               
  3.     Me.ControlName.Locked = True
  4.     Me.Completed.Locked = true
  5.     Exit Sub
  6. Else
  7.     Me.ControlName.Locked = False
  8.     Me.Completed.Locked = False
  9. End If
  10. intVar = Me.ControlName
  11. End Sub
I would suggest a slightly more elegant coding.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. Dim blnLockState as Boolean
  4.  
  5. With Me
  6.     blnLockState = .NewRecord or .ControlName = 0
  7.     .ControlName.Locked = blnLockState
  8.     .Completed.Locked = blnLockState
  9.     intVar = .ControlName
  10. End With
  11.  
  12. End Sub
Feb 27 '09 #6

NeoPa
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
  1. UPDATE [YourTable]
  2. SET [Priority]=[Priority]+1
  3. 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

Post your reply

Sign in to post your reply or Sign up for a free account.