Hello everyone.
I thought I would update people here who might be following this thread. With a good deal of searching I found some module based code elsewhere written 13 years ago by a forum user named MajP. Essentially what it does is if you have a sort field Like my form does which is essentially what my LegNo field is the code will swap the value of the current sort number with that of the previous one and then requery and do a FindRecord. It does the reverse to perform a move down. This was a lifesaver for me as I might have never come up with this on my own. Examining the code is a great learning experience as well. Form the little bit I have played with it it seems to work. I need to enter some more data and see if I can find any bugs which I assume would occur when reaching BOF or EOF.
Here is the code:
Code:
Public Sub SetInitialSort(SortField, frm)
Dim rs As DAO.Recordset
Set rs = frm.Recordset
Do While Not rs.EOF
rs.Edit
rs.Fields(SortField) = rs.AbsolutePosition + 1
rs.Update
rs.MoveNext
Loop
rs.MoveFirst
End Sub
Public Sub moveSortUp(SortField, frm As Access.Form)
Dim rsClone As DAO.Recordset
Dim lngNewSort As Long
Dim lngOldSort As Long
Set rsClone = frm.RecordsetClone
rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition <= 0) Then
lngOldSort = rsClone.Fields(SortField)
lngNewSort = lngOldSort - 1
rsClone.Edit
rsClone.Fields(SortField) = lngNewSort
rsClone.Update
rsClone.MovePrevious
rsClone.Edit
rsClone.Fields(SortField) = lngOldSort
rsClone.Update
frm.Requery
frm.Recordset.FindFirst SortField & " = " & lngNewSort
End If
End Sub
Public Sub MoveSortDown(SortField, frm As Access.Form)
Dim rsClone As DAO.Recordset
Dim lngNewSort As Long
Dim lngOldSort As Long
Set rsClone = frm.RecordsetClone
rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition >= rsClone.RecordCount - 1) Then
lngOldSort = rsClone.Fields(SortField)
lngNewSort = lngOldSort + 1
rsClone.Edit
rsClone.Fields(SortField) = lngNewSort
rsClone.Update
rsClone.MoveNext
rsClone.Edit
rsClone.Fields(SortField) = lngOldSort
rsClone.Update
frm.Requery
frm.Recordset.FindFirst SortField & " = " & lngNewSort
End If
End Sub