Continous forms rows with up and down buttons for row ordering

LISA GEORGE
LISA GEORGE used Ask the Experts™
on
I have continous form as subform with some bound combo boxes, text box bound and unbound.
User wants to move records which are displayed as row wise up and down
Say for example if sub form has total 10 rows and he wish to move row2 to row3 or vice versa
can you please let me know how to do this in ms access form
do i need to have up button and down button for each row with some vba code so if user click on up button it moves 1 row up or if user clicks on down button it moves 1 row down
or is their any other way to work around on this
I am new to MS access and VBA code
Please guide me on this
This is bit urgent so kindly help me
thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
The easiest way would be to have buttons and a field (shown or not) with the sort numbering.  As you move the rows, you renumber and sort based on that field.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
This is typically called assigning a priority to each row.
It is described in full (with a demo) in my article:

Sequential Rows in Microsoft Access

Browse to paragraph: 3. Priority Numbers
Software & Systems Engineer
Commented:
Well here is the code i used on one of my applications..just two small buttons
Clipboard01.jpgThe requirement is to have a field that holds the order of rows...here in my example is called  OrderInProgramNo
Private Sub cmdReorderUp_Click()
    Dim rst As Object
    Dim previousValue As Long
    Dim CurrentValue As Long
    Dim blnStop As Boolean
    If IsNull(Me.OrderInProgramNo) Or Me.OrderInProgramNo = 0 Or Me.OrderInProgramNo = 1 Then Exit Sub

    Set rst = Me.RecordsetClone
    blnStop = False
    With rst
        .MoveFirst
        If Not .EOF And Not .BOF Then
            While Not .EOF And Not blnStop


                If .Fields("OrderInProgramNo") = Me.OrderInProgramNo Then
                    CurrentValue = Me.OrderInProgramNo
                    .Edit
                    .Fields("OrderInProgramNo") = previousValue
                    .Update
                    .MovePrevious
                    .Edit
                    .Fields("OrderInProgramNo") = CurrentValue
                    .Update
                    blnStop = True
                End If
                previousValue = .Fields("OrderInProgramNo")
                .MoveNext

            Wend

        End If
    End With
    With rst

    End With
    Set rst = Nothing
    Me.Requery
End Sub

Open in new window


And of course your form should be ordered by this field
e.g
ORDER BY OrderInProgramNo

Open in new window

the above code is for moving the row UP
for DOWN
just change
With rst
        .MoveLast

Open in new window

.Fields("OrderInProgramNo") = previousValue
                    .Update
                    .MoveNext

Open in new window

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Mark EdwardsChief Technology Officer

Commented:
The first questions from me is:  

What is the reason for wanting to move the position of rows on a subform to a different position in the order?  (The answer depends on what you are trying to do and why)

How is the original row order determined to begin with (Order By, default order, etc.)?  Is there a field in the table that is dedicated to creating a custom sort order?  (You'll be fighting/working with it and it will have to be handled by the process that moves the rows).

How are you wanting to reorder the rows?  Are you looking for a "drag-and-drop" process?  Assign the row a new position number and re-sort the rows?  Move the row up/down 1 row-at-a-time?

Are the rows that the user is dealing with a filtered subset of rows on a larger recordset?  (The rows that are filtered out will have to be taken into account by your reordering process.)

Does the new ordering need to be kept the next time the set of records is loaded? (Like after the app is closed, then reopened)

Hopefully you can find and choose a method that looks like it will work with whatever it is you've got.
John TsioumprisSoftware & Systems Engineer

Commented:
@Mark there are good reasons for reordering like job priority
Mark EdwardsChief Technology Officer

Commented:
John:  I'm sure there are, but what does Lisa need?  specific solutions need specific info.  How does she want to do this?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
I've added up-down buttons to my demo and stripped it down to what you need.
It is attached:

RowUpDownDemo.PNG
If you set the form to order on Priority, you'll notice, that the current record will move up or down when a button in the footer is clicked.
If you set the form to order on any other column, you may press Reset, and the priority field will be aligned to match the order.

Note, that you can also edit the Priority field directly to avoid multiple clicks on an Up or Down button.

The code module of the form is extremely simple:

Option Compare Database
Option Explicit

Private Sub ButtonDown_Click()

    PriorityAdd 1

End Sub

Private Sub ButtonUp_Click()

    PriorityAdd -1
    
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

    ' Rebuild priority of all records having lower priority (= higher numeric value).
    RowPriority Me.Priority

End Sub

Private Sub Form_AfterInsert()

    ' Rebuild priority of all records having lower priority (= higher numeric value).
    RowPriority Me.Priority
    
End Sub

Private Sub Priority_AfterUpdate()

    ' Rebuild priority of all records having lower priority (= higher numeric value).
    RowPriority Me.Priority
    
End Sub

Private Sub ResetPriorityButton_Click()

    ' Set priority order to match the form's current record order.
    SetRowPriority Me.Priority

End Sub

Private Sub PriorityAdd(ByVal Increment As Long)

    Dim CurrentPriority As Long
    Dim MaxPriority     As Long
        
    CurrentPriority = Me!Priority.Value
    MaxPriority = Me.RecordsetClone.RecordCount
    
    If Sgn(Increment) < 0 And CurrentPriority > 1 Or _
        Sgn(Increment) > 0 And CurrentPriority < MaxPriority Then
        
        Me!Priority.Value = CurrentPriority + Increment
        Me.Dirty = False
        RowPriority Me.Priority
    End If
    
End Sub

Open in new window

RowUpDownDemo.accdb
LISA GEORGEMICROSOFT DEVELOPER

Author

Commented:
@Mark Edwards
Yes i want row to move up/down one at a time. FYI there are delete buttons as well for each row so i want up, down button as well for each row.
So if user deletes 4th row then 5th row becomes 4th row
My subform is continous form without record selectors, navigations buttons displayed.
I am using MS access 2010 so is there any way to do this with less VBA code and use more of access form properties.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You will need code for this. Indeed if additions and deletions should be honored.
My demo does that with most of the code stuffed away in a module.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
s there any way to do this with less VBA code
Not really.  You can get Access to do almost anything, but this requires code.  In this case you are lucky enough to have some sample code to get started with (80-90% of the job is already done for you).
Mark EdwardsChief Technology Officer

Commented:
@Lisa:  Thanks for the info.  I have a better idea of what you are looking to do.

Unfortunately, Microsoft did NOT build into its low/no code system the ability to do what you are looking to do, so It can only be done with VBA. That's why Access has BOTH its low/no code method of app creation as well as having the VBA system - you use VBA to do what the low/no code system can't.  If you're waiting for a solution that doesn't involve any VBA, or a lot less VBA than has already been offered in the solutions above - you're going to be waiting for a long, long time....

As far as being able to move a record to the place you want it and keep it there, you're going to need to have some way to let the system know what order you want the records in, and that involves having a dedicated sorting field (column) containing the sorting numbers.  Several suggestions have already been put forward that are good ones and will work for you.  Keep in mind that there is a difference in a sorting number column and a consecutive numbering column.  A sorting column only needs to contain data necessary to put the records in the order that you want them.  A consecutive number column requires that the value in a record be changed every time a record position is changed so that the records are number consecutively from first to last.  Take note of which solutions offered give you what you are looking for.

However, I don't know if any of them have code that renumbers the records if one is deleted as deleting a number record does not change the sort order for the remaining records.  Since inserting a record involves adding it to the bottom of the table, you'll need to use the reordering process to move it where you want it to go.  You may find that you need to move a record many rows to get it to the position you want, so being able to tell your repositioning process exactly where you want a record to go is a feature I'm sure you will find necessary, even if you don't see the need now.

Hope this helps...
Mark EdwardsChief Technology Officer

Commented:
I forgot to mention (and this is more on the technical side) that you'll want a record numbering system that will allow you to put a record between two other records, so if they are numbered consecutively (like 1, then 2, etc.) the number will need to be between them (like 1.5).  I've found that using a Single or Double number type instead of an Integer type allows you to get the records sorted first (1, 1.5, 2), then your renumbering process can come through and renumber them to integer (1, 2, 3).

Just a tidbit I learned from experience.  I'm sure there are a lot of other methods.
John TsioumprisSoftware & Systems Engineer

Commented:
Check my post here that contains a running sample

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial