Solved

Move a record up or down.

Posted on 2004-10-26
274 Views
Last Modified: 2011-10-03
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)
0
Question by:tplowe56
    5 Comments
     
    LVL 58

    Expert Comment

    by:harfang
    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
     

    Author Comment

    by:tplowe56
    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
     
    LVL 58

    Accepted Solution

    by:
    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
     
    LVL 58

    Expert Comment

    by:harfang
    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
     

    Author Comment

    by:tplowe56
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now