Results 1 to 3 of 3
  1. #1
    Warral is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    1

    Move record in ordered subform more than one row up/down

    Hello all,

    I have a quote database for our company's products. During entry of data it autonumbers lines, "LineNo" - everything is fine except on large quotes when they have to add in a row (product) to a specific spot later on.

    The quote has products and quantities in sections according to say the floors of a building. Floor 1 - various items LineNo 2 - 57. Floor 2 - more items, LineNo 58 - 105, and so on.



    I have working buttons to move a record in the subform up or down by 1, which is just swapping the row above with row below.

    When the quote is long, and you have to add a product to "Floor 1" - it puts the new entry at bottom of quote and you have to click a ton of times to move item up to Floor 1 section.

    And this is not about needing code to put the new item at the top of the order instead of bottom, in case you're adding to the "middle floor" - you still need a way to move record more than one row at a click.

    I tried to use code found online to move a row by 10 - but that just swaps positions of the row selected with row 10 above or below. I don't want the data at row I'm moving to to move to the place where the selected row was!

    I need code to move the selected record up or down by 10, but inserting into that spot i.e.. Selected row moves down 10 spots to "LineNo" 57 - so new data is LineNo 57, and what was 57 becomes 58 and all reordered accordingly thereafter.

    I've searched and searched online and only find people posting about moving around by 1 row up down, not multiple rows with one button click.

    I'm willing to provide any other details as needed to help me with this, let me know thank you!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    sounds like what you need is a bubble sort - every 'number' greater than or equal to the new position is incremented by 1, every number greater than the old position is decremented by 1 - or to put it another way every number between the new position and the old position-1 is incremented by 1. Gets more complicated if new position is greater than old position, but the principle remains the same. Bubble sorts are usually used with arrays so you will need to adapt the principle - here is a link which includes a variant called quicksort

    https://stackoverflow.com/questions/...algorithm-slow

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Wonder if this would help:

    Code:
    Public Sub BubbleSort(ToSort As Variant, Optional SortAscending As Boolean = True)
        ' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
        ' By Chris Rae, 19/5/99. My thanks to
        ' Will Rickards and Roemer Lievaart
        ' for some fixes.
        
        'modified by Vlad Cucinschi on May 12 , 2004 to sort a three dimensional array
        
        Dim AnyChanges As Boolean
        Dim BubbleSort As Long
        Dim Swap1D As Variant, Swap2D As Variant, Swap3D As Variant
        Do
            AnyChanges = False
            For BubbleSort = LBound(ToSort, 2) To UBound(ToSort, 2) - 1
                If (ToSort(0, BubbleSort) > ToSort(0, BubbleSort + 1) And SortAscending) _
                   Or (ToSort(0, BubbleSort) < ToSort(0, BubbleSort + 1) And Not SortAscending) Then
                    ' These two need to be swapped
                    Swap1D = ToSort(0, BubbleSort)
                    Swap2D = ToSort(1, BubbleSort)
                    Swap3D = ToSort(2, BubbleSort)
                    
                    ToSort(0, BubbleSort) = ToSort(0, BubbleSort + 1)
                    ToSort(1, BubbleSort) = ToSort(1, BubbleSort + 1)
                    ToSort(2, BubbleSort) = ToSort(2, BubbleSort + 1)
                    
                    ToSort(0, BubbleSort + 1) = Swap1D
                    ToSort(1, BubbleSort + 1) = Swap2D
                    ToSort(2, BubbleSort + 1) = Swap3D
                    AnyChanges = True
                    
                End If
            Next BubbleSort
        Loop Until Not AnyChanges
    End Sub
    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-21-2018, 08:13 AM
  2. Move form controls according to subform resize
    By Lukael in forum Programming
    Replies: 8
    Last Post: 12-06-2015, 01:36 PM
  3. Replies: 3
    Last Post: 02-06-2015, 01:18 PM
  4. Replies: 3
    Last Post: 12-11-2014, 11:26 AM
  5. Replies: 5
    Last Post: 06-16-2013, 05:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums