Using a Priority and updating other records

[Perceptus]

Not sure if this belonged in the Query or Module or Programming forum.


I have a table (tblPriorityTest) with 3 fields. ID, Description, Priority. Priorities are to range between 1-9000.

I have a form where I have each field named to the field it represents.

In the before update event i have.

Private Sub Priority_BeforeUpdate(Cancel As Integer)
PriorityUpdate Me.Priority
End Sub

And the Priority Update Code.

Private Function PriorityUpdate(ByVal tbPriority As TextBox)
    Dim oldValue As Integer: oldValue = tbPriority.oldValue
    Dim newValue As Integer: newValue = tbPriority
    Dim SQL As String
    'Dim intChange As Integer: intChange = 0
    If oldValue = 0 Or newValue = 0 Or oldvale = newValue Then Exit Function
    
    If newValue < oldValue Then
        intChange = oldValue - newValue
        SQL = "Update tblPriorityTest Set [Priority]=[Priority]-" & IIf(intChange <= 0, Abs(intChange), intChange) & " Where [Priority]<" & oldValue
        SQL = SQL & " and ID <>" & id & ""
        SQL = SQL & " and [Priority]<9000"
              
    ElseIf newValue > oldValue Then


        intChange = newValue - oldValue
        SQL = "Update tblPriorityTest Set [Priority]=[Priority]+" & intChange & " Where [Priority]>" & oldValue
        SQL = SQL & " and ID <>" & id & ""
        SQL = SQL & " and [Priority]<9000"
    End If
    
    Debug.Print SQL
    
    CurrentDb.Execute SQL
    
    'intChange = CurrentDb.RecordsAffected
End Function

my data look like

id:	Description:	Priority:
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7

When I change the priority of ID#5 to a 7. I want all the numbers after it to be changed.

It sorta works. sorta not. Am I doing this the wrong way? Suggestions.

[rpeare]

in other words when you change item 5's priority to 7 you want ID 6 priority to become 5 and id 7's priority to become 6?

What exactly is your code doing that 'sorta works, sorta not'.

[Perceptus]

Changing a lower number to a higher number works. but changing a higher number to a lower number doesn't.

[rpeare]

try this:

If newValue < oldValue Then         
    intChange = oldValue - newValue         
    SQL = "Update tblPriorityTest Set [Priority]=[Priority]-" & IIf(intChange <= 0, Abs(intChange), intChange) & " Where [Priority]<=" & oldValue

[Perceptus]

This still produced a negative number.

I think the error in my ways is the Inline IF statement. I forgot that subtracting a negative is the same as adding a positive

[rpeare]

Where are you on this, did you solve the problem?

If not could you supply a sample database so I don't have to re-write something.

[Perceptus]

I only get to work on this problem every other tuesday and thursday. Its still on my list. In my next post i will provide a sample.

Thank you for your patience. Hope you had a good 4th.