Move a record up or down.

I need to move a record up or down in a form. I am trying to adapt this code below that is for a subform. The field for the sort that I want reordered is "Sequence" the table is "FolderLabels" and the query is "MoveRecord". Having problems with sytax removing refernces to subforms. The record selector has to move up or down with the record also.

Original post containing code: http://www.experts-exchange.com/Databases/MS_Access/Q_20645846.html?query=Move+Records+up+and+down+in+Access++&clearTAFilter=true

'Save the current sequence value
intSavePos = Me.sfrmPositions.Form.Sequence

'is it the first ? ==> no action
If intSavePos = 1 Then Exit Sub

'Switch positions. Put a zero as a placeholder in the current sequence position, move the selected record up one position
'and move that record down one

DoCmd.SetWarnings False

strSQL = "UPDATE positions SET positions.Sequence = 0 WHERE KOLID=" & Me.sfrmPositions.Form.KOLID & " AND sequence=" & intSavePos & ";"
DoCmd.RunSQL strSQL

strSQL = "UPDATE positions SET positions.Sequence = " & intSavePos & " WHERE KOLID=" & Me.sfrmPositions.Form.KOLID & " AND sequence=" & intSavePos - 1 & ";"
DoCmd.RunSQL strSQL

strSQL = "UPDATE positions SET positions.Sequence = " & intSavePos - 1 & " WHERE KOLID=" & Me.sfrmPositions.Form.KOLID & " AND sequence=0;"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

'Refresh  form to put everything in order or sequence again
Me.Refresh

'Set cursor on new location
Me.sfrmPositions.Form.SelTop = (intSavePos - 1)
tplowe56Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
harfangConnect With a Mentor Commented:
Believe me, I saw this one coming :) Just wanted you to have a go at it...


Private Sub cmdDown_Click()

    Const bytFree As Byte = 0   ' Must not exist in Sequence
    Dim bytSaved As Byte        ' Save current Seq. number
    Dim bytSwap As Byte         ' Next Seq. number

    ' Some checks...
    If Me.NewRecord Then Exit Sub

    With Me.RecordsetClone
        ' synchronize records and save current seq. number
        .Bookmark = Me.Bookmark
        bytSaved = !Sequence
       
        ' is there a next record?
        .MoveNext
        If .EOF Then Exit Sub
        ' there is...
        .MovePrevious
       
        ' set to free sequence number
        .Edit
        !Sequence = bytFree
        .Update
       
        ' Move to next and store Seq. number
        .MoveNext
        bytSwap = !Sequence
        ' replace with "current" Seq. number
        .Edit
        !Sequence = bytSaved
        .Update

        ' Return to current and assign Seq. number of previous record
        .MovePrevious
        .Edit
        !Sequence = bytSwap
        .Update
       
    End With
   
    ' Requery the form and return to the moved record...
    Me.Requery
    Me.RecordsetClone.FindFirst "Sequence = " & bytSwap
    Me.Bookmark = Me.RecordsetClone.Bookmark
       
End Sub

Incidentally, the line:

    If Me.NewRecord Then Exit Sub

should also be added to the "some checks" section of cmdUp

Good luck
0
 
harfangCommented:
Problem is, the above code is not very good, neither for a subform nor for a main form.
Here is an alternative, assuming only a very few things:

- There is a field called [Sequence], containing unique numbers
- In this example these are bytes (0 to 255 only), replace Byte with Long if needed :)
- The form is always sorted by Sequence
- No record has the Sequence number 0 (zero)
- There is a button somewhere called cmdUp :)

--------------------------------------

Private Sub cmdUp_Click()

    Const bytFree As Byte = 0   ' Must not exist in Sequence
    Dim bytSaved As Byte        ' Save current Seq. number
    Dim bytSwap As Byte         ' Previous Seq. number

    ' Some checks...
    If Me.Sequence = 1 Then Exit Sub
    If Me.CurrentRecord = 1 Then Exit Sub

    With Me.RecordsetClone
        ' synchronize records and save current seq. number
        .Bookmark = Me.Bookmark
        bytSaved = !Sequence
       
        ' set to free sequence number
        .Edit
        !Sequence = bytFree
        .Update
       
        ' Move to previous and store Seq. number
        .MovePrevious
        bytSwap = !Sequence
        ' replace with "current" Seq. number
        .Edit
        !Sequence = bytSaved
        .Update

        ' Return to current and assign Seq. number of previous record
        .MoveNext
        .Edit
        !Sequence = bytSwap
        .Update
       
    End With
   
    ' Requery the form and return to the moved record...
    Me.Requery
    Me.RecordsetClone.FindFirst "Sequence = " & bytSwap
    Me.Bookmark = Me.RecordsetClone.Bookmark
       
End Sub
0
 
tplowe56Author Commented:
Thank you very much your code works great. I had searched the net extensively and not found any code to accomplish this task, that worked.

Now I am trying to code the "Move Down" button, but inverting "MoveNext" and "MovePrevious" is not giving me the correct result. Can you suugsest the changes to accomplish this? Thanks.
0
 
harfangCommented:
While I'm at it...

After With Me.RecordsetClone, you could have an additional check:

    With Me.RecordsetClone

        ' still more error catching :)
        .FindFirst "Sequence = " & bytFree
        If Not .NoMatch Then
            MsbBox "Error: the sequence number " & bytFree & " should not be used" & vbCrLf _
                & "Please correct this problem"
            Exit Sub
        EndIf

       ' synchronize records and save current seq. number
       .....


Cheers!
0
 
tplowe56Author Commented:
Thanks again, the code works perfect. I had never used Access until 3 weeks ago, and I don't normally write code, so I'm in way over my head. I've learned a lot but this Moveup/Movedown thing was hard to find any code snippets that worked.

Thanks!!!
0
All Courses

From novice to tech pro — start learning today.