Priority Change Update all records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an intiger field "Priority". I would like to write a simple loop that
updates all records when one records "Priority" is changed. For example, if
I have a record that is currently listed as Priority 5 and I wish to change
it to Priority 2, I need all 2 to be come 3 and 4 to become 5 and 5 to become
2, and leave all other records left alone.

any ideas?
 
Create a function with three variables,
after update of priority for one of your records call the function by passing:

1) KeyID for the record changed
2) The Previous Priority
3) The Current Priority

In A Sub Like:

Private Sub RunPriorityUpdate()
Call UpdatePriorities(1479036363, 1, 10)
End Sub


Then your function would look something like this:

Private Function UpdatePriorities( _
plngKeyID As Long, _
plngOldPri As Long, _
plnNewPri As Long)

Dim daoDbs As DAO.Database
Dim pstrSql As String
Dim pstrOpr1 As String
Dim pstrOpr2 As String
Dim pstrOpr3 As String

Set daoDbs = CodeDb

If plngOldPri > plnNewPri Then
pstrOpr1 = "+1 "
pstrOpr2 = "<="
pstrOpr3 = ">="
Else
pstrOpr1 = "-1 "
pstrOpr2 = ">="
pstrOpr3 = "<="
End If

pstrSql = _
"UPDATE Table1 " & _
"SET Table1.Priority = " & _
"[Table1]![Priority]" & pstrOpr1 & _
"WHERE (((Table1.KeyID)<>" & _
plngKeyID & ") " & _
"AND ((Table1.Priority)" & _
pstrOpr2 & plngOldPri & _
" And (Table1.Priority)" & _
pstrOpr3 & plnNewPri & "));"

daoDbs.Execute (pstrSql)

End Function

Take Care & God Bless ~ SPARKER ~
 
Back
Top