Implementing Move Up/Move Down

[Guest]

I would like for my user to be able to assign a sort order to the records in
a single table. In other words, a position of 1st, 2nd, 3rd etc needs to be
assigned manually by the user. I would like it to be as "smart" as possible,
in that if a record is reassigned the third position, all other positions
would shift accordingly. Some sort of visual method would be terrific, such
as Move Up/Move Down arrows. Or possibly just an automatic reshuffling when
one value is changed.

I have no requirements as to how the order information is stored in each
record. I simply need a method for assigning the order. The table is not
long. It may have a few dozen records; probably no more than 50.

 

[Jesper F]

I would like for my user to be able to assign a sort order to the records

in
a single table.

I have a function that does this that you can have.
Also - this function is used with all your records displayed in a listbox.
You should the have an "up"-button and a "down"-button. The listbox should
be a single-select.

With this function:

--------------------
Sub ListMove(sForm As String, sList As String, sTable As String, sSortField
As String, _
sUpOrDown As String, Optional sWhere As String)
'********************************************************************
'* Made by: Jesper Fjølner
'* Function: Moves elements in listbox up or down
'********************************************************************
On Error GoTo err_:

If IsNull(Forms(sForm)(sList)) Or Forms(sForm)(sList) = "" Or
IsEmpty(Forms(sForm)(sList)) Then
MsgBox "Du skal vælge et element på listen først.", vbOKOnly + vbInformation
Exit Sub
End If

If sUpOrDown = "Up" And Forms(sForm)(sList).ListIndex = 0 Then
'Last row selected, exits
Exit Sub
End If
If sUpOrDown = "Down" And Forms(sForm)(sList).ListIndex =
Forms(sForm)(sList).ListCount - 1 Then
'First row selected - exits
Exit Sub
End If

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Integer
Dim iValgt As Integer
Dim iNr As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM " & sTable & " ORDER BY " &
sSortField & "")

rs.MoveLast
rs.MoveFirst
iCount = rs.RecordCount
iValgt = Forms(sForm)(sList).ListIndex + 1

Do Until rs.EOF
iNr = rs(sSortField)
If iValgt < iNr Then
'If moving down, subtract from the rest of the sortnumbers
If sUpOrDown = "Down" Then
If iNr - 1 = iValgt Then
rs.Edit
rs(sSortField) = iNr - 1
rs.Update
End If
End If
End If

If iValgt = iNr Then
rs.Edit
If sUpOrDown = "Up" Then
rs(sSortField) = iNr - 1
End If
If sUpOrDown = "Down" Then
rs(sSortField) = iNr + 1
End If
rs.Update
End If

If iValgt > iNr Then
'If the chosen row only has one other row above or below
If sUpOrDown = "Up" Then
If iNr + 1 = iValgt Then
rs.Edit
rs(sSortField) = iNr + 1
rs.Update
End If
End If
End If
rs.MoveNext
Loop

Forms(sForm)(sList).Requery

rs.Close
Set rs = Nothing
Set db = Nothing

'* Errorhandling
exit_:
Exit Sub
err_:
Select Case Err.Number
Case 13
MsgBox "Error" & Err.Number & ": " & Err.Description
GoTo exit_
Case Else
MsgBox "Errror" & Err.Number & ": " & Err.Description
GoTo exit_
End Select
End Sub
--------------------

you should be able to do it.

Put this under the "down"-button:
ListMove "nameofyourform", "nameoflistbox", "nameoftable",
"nameOfTheSortField", "down", OptionalWhereCondition

Put this under the "up"-button:
ListMove "nameofyourform", "nameoflistbox", "nameoftable",
"nameOfTheSortField", "up", OptionalWhereCondition



Hope this helps,


/ Jesper Fjølner