Datasheet Subform to Re-order Records sequence using up and down buttons on Main Form

[Mohsin Malik]

Dear Programmer,

I am building a Work Order Database and I have a Main form and a subform (in Datasheet View), I have 02 buttons on Main form and I would like to reorder the records by users and on report I would like to print out with the same order sequence. I have attached an Image for quick understandings, This image is not an Access Subform but I have taken this from some software.

I believe that I could have an extra field in my subform table e.g LineNumber, but I am not sure how to assign value and how this Re-order will work and the subform is re-ordering live, any idea how to get it work, I really appreciate if there is any example or suggestions to get this worked.

Looking forward to hear from you

Thank you
Mohsin

 

[JackKaptijn]

I have made you an example.....
There is no refresh in the sub form yet to show what's happening.

Maybe the switching of the record can be done easier......

 

[Mohsin Malik]

Hello JackKaptjin,

Thank you for making an example for me, I will review and will let you know that how it goes.

Many Thanks
Mohsin

 

[Hortonm]

Is there a way of saving this in the order?
I am trying to do a similar thing but would like to save the order in a linked table

 

[Hortonm]

I have worked out a way of saving this by using an update query. I however keep getting this message attached

this is my Code
Private Sub Move_Track_Down_Click()
'rs is the recordset which contains the actual orderlines
Dim rs As Recordset
'I use two variables for switching: lOld is the record which should be moved. lNew is the one it is switched with
Dim lOld As Long
Dim lNew As Long
Set rs = Me!PresenterRunninglist.Form.RecordsetClone

If Me!PresenterRunninglist.Form.SelTop = rs.RecordCount Then
MsgBox "Bottom line selected"
Else
rs.MoveFirst
Do While Not rs.BOF
If rs!Line = Me!PresenterRunninglist!LoanId Then
lOld = rs!Line
rs.MoveNext
lNew = rs!Line
rs.Edit
rs!Line = lOld
rs.Update
rs.MovePrevious
rs.Edit
rs!Line = lNew
rs.Update
Exit Sub
End If
rs.MoveNext
Loop
End If
End Sub