Change Sequence of Remaining Records After Updating Number of Another

[angelaing]

This is a very complicated requirement and solution. I know you are putting a tremendous amount of thought into making this all work. I'm am putting a tremendous amount of time just to follow where your going with it. I think I understand the logic than I get an ahaha gotcha somewhere but that may be just me. Thanks so much

[NeoPa]

AngelaIng:
NeoPa, I attached the code to the Events you suggested and after I type in a new number to any record on the continuous form nothing happens it sits there forever. I can't click the close button on the form or navigate up or down. I can tab to a new field in the same record but that's it.

It would be interesting to see if you've set Event properties up properly to run the code, and if the code IS running whether or not it goes wrong anywhere. What are your debugging skills like (See Debugging in VBA)?

What version of Access are you running this with?
Is it possible for you to attach a ZIPped copy of this and any back-end files that may be necessary for me to try running it here. It's much easier to find problems when they're in front of you.

I believe this concept should work, but I haven't tested it for real yet.

[angelaing]

And NeoPa, this is the solution they want to use. So I'll keep trying to figure out what's going on in the meantime. I have to take off for an appointment in 30 minutes but will get back to it.

[angelaing]

I set watches and stepped through code to see the various values but I still think my debugging skills are not that good.

I have to run right now to go to an appt that's about an hour away so I won't be back until much later today but I will look at your link then and if I still can't figure it out if you don't mind I will either send you a copy of all the code. i could even send you the whole database but there are two of them. It's not a sql server back end a 2003 mdb back end and 2016 accdb front. I could import all the table objects into the 2016 front end and send it to you that way but then having them all in one might cause some behavioral changes. I don't know.

[angelaing]

or just send a copy of the code. Thanks, will catch up later

[angelaing]

I keep trying to send you a message and somethings going wrong. I will look at your instructions first. There's more to debugging than stepping through code. I glanced at your instructions on debugging and there's more for me to learn so let me work on it more when I GET Back and i will let you know

[NeoPa]

I have some changes to post.

I've created a copy for myself in 2010 and found a problem or two. You go for your appointment elsewhere and you should have some better code to play with when you get back.

[angelaing]

ok so i am a little late. I am in a hurry so I just read parts of your email but when I read the printed out version of the email I can see you asked for a copy of the code. By now I could have sent it to you. I will do both, send you a copy and try and figure it out on my own so I learn. Ok, it will be in about 6 hours and I know there's a time difference. Take care

[NeoPa]

Right. As I said earlier, I now have a proof of concept working perfectly in a very small Access 2010 ACCDB that I've attached in a ZIP file.

The main points I found that had to be addressed seem to line up nicely with your own experience and were about using Undo() to set the Control and the Form (Current record) back to before the operator even started to make changes to them. Using Cancel alone stops the change from being applied, but leaves the changed data for the operator to have another try at it. What I had to do was to clear any changes, first in the Control, and then in the Form, so that the changed record released it's lock.

Anyway, the latest code is now :

Option Compare Database
Option Explicit

'%FV = From Value; %TV = To Value; %PM = +/-
'%LL = Lower limit; %UL = Upper Limit; %NL = New Line
Private Const conSQL As String = _
               "UPDATE [t_Projects]%NL" _
             & "SET [GrpUnitRank]=IIf([GrpUnitRank]=%FV,%TV,[GrpUnitRank] %PM 1)%NL" _
             & "WHERE  ([GrpUnitRank] Between %LL And %UL)"

Private lngFrom As Long, lngTo As Long

Private Sub Form_Timer()
    Dim strSQL As String
    Dim dbVar As DAO.Database

    With Me
        'This is a one-off so we disable it from running again.
        .OnTimer = ""
        .TimerInterval = 0
    End With
    'Do nothing if lngFrom or lngTo are not set.
    If lngFrom = 0 _
    Or lngTo = 0 Then _
        Exit Sub
    'Replace all the place-holders from conSQL with our values.
    strSQL = Replace(conSQL, "%FV", lngFrom)
    strSQL = Replace(strSQL, "%TV", lngTo)
    strSQL = Replace(strSQL, "%PM", IIf(lngFrom > lngTo, "+", "-"))
    strSQL = Replace(strSQL, "%LL", IIf(lngFrom > lngTo, lngTo, lngFrom))
    strSQL = Replace(strSQL, "%UL", IIf(lngFrom > lngTo, lngFrom, lngTo))
    strSQL = Replace(strSQL, "%NL", vbNewLine)
    Set dbVar = CurrentDb()
    'For now we ignore errors.
    'That can be added later without need for my help.
    On Error Resume Next
    Call dbVar.Execute(Query:=strSQL, Options:=dbFailOnError)
    If Err.Number = 0 Then
        Call Me.Requery
        lngFrom = 0
        lngTo = 0
    End If
End Sub

Private Sub txtRank_BeforeUpdate(Cancel As Integer)
    'We never actually update this value from the form.
    Cancel = True
    With Me
        With .txtRank
            'Update not valid if new value either same as old OR > num recs.
            If .Value = .OldValue _
            Or .Value > DCount(Expr:="*", Domain:="[t_Projects]") Then _
                Exit Sub
            lngFrom = .OldValue
            lngTo = .Value
            'Undo pending change made to the value.
            Call .Undo
        End With
        'Although this may lose other changes to the record it's necessary
        'in order to release the lock on the current record.
        Call .Undo
        'Set the Timer procedure to fire when this one completes.
        .OnTimer = "[Event Procedure]"
        .TimerInterval = 1
    End With
End Sub

I've set up my database with the same names where necessary for the code to work so this code should work for you as-is.

The SQL created when I ran this in the scenario outlined in an earlier post was :

UPDATE [t_Projects]
SET [GrpUnitRank]=IIf([GrpUnitRank]=7,3,[GrpUnitRank] + 1)
WHERE  ([GrpUnitRank] Between 3 And 7)

Exactly as we'd want it to. And it worked perfectly every time.

[NeoPa]

AngelaIng:
I am in a hurry so I just read parts of your email but when I read the printed out version of the email I can see you asked for a copy of the code. By now I could have sent it to you. I will do both, send you a copy and try and figure it out on my own so I learn.

That's a very commendable attitude Angela, and I don't want to discourage you from anything that will help you learn. That said, I suspect we now have some working code for you so I doubt you'll need to send me anything for now.

If, after trying out the new code (Paste it into the module BTW. Retyping code is a reliable cause of introducing problems.), you find you still have problems then we can think again about sharing your code and/or your two databases.

I'm not expecting to hear back from you until after you come back ;-) Hurry off if you haven't gone already, but you can be quietly confident you'll get it working quickly and easily on your return.

[PhilOfWalton]

@Neopa

Elegant bit of coding as ever, the swap routine is beautifully concise.

However, on the sample Db, it appears that you can't add a new project (on the form) as the Undo looses the new record.

Phil

[NeoPa]

Indeed Phil, and thank you.

My solution was an exercise as specified by the question in this thread. If more is desired then that can be considered elsewhere. There is certainly information available to the code to allow differentiation between an addition and an update. I would expect any such design to add the [GrpUnitRank] value automatically as the number of records covered.

I don't believe such a discussion fits in this thread however.

[angelaing]

NeoPa. This meets the requirement perfectly. I have to do something similar to the same table updating the sequence basedv on the group unit rank entered for a new project enteted into a data entry form. I can conceptualize how I think it should work. This one I’m atill trying to understand why the .undo was needed.

[angelaing]

Excuse my mispellings. I’m typing on my phone and my eyes arent so good

[angelaing]

Responding to Phils comment. It’s ok because new records aren’t meant to be added on this form