Microsoft Office Tutorials and References
In Depth Information
Moving Rows in List Boxes
Although this looks simple, some problems arise when trying to code this
process:
The table needs to have a closed row of numbers, starting with 1 and no
gaps.
¾
The straight forward update of a row with the new sequence number
will cause a Duplicate key error.
¾
After a move, the selected row needs to move too; so the user can keep
pressing up until a row is in the first location.
¾
Finally, take care that an Up on the first row or a Down on the last row
is handled.
¾
The following code does all this on click:
Private Sub btnDown_Click()¶
Dim intSaveIndex As Integer¶
'The click event is taking care that only valid moves can be made¶
'Check if an item before the last one is selected¶
If Me.lstMove.ItemsSelected.Count > 0 And Me.lstMove.ListIndex <
Me.lstMove.ListCount - 1 Then¶
intSaveIndex = Me.lstMove.ListIndex¶
dbKeyFrom = Me.lstMove.Column(0, Me.lstMove.ListIndex)¶
dbKeyTo = Me.lstMove.Column(0, Me.lstMove.ListIndex + 1)¶
' Move¶
Call subMove¶
'Make change visible and move item selection with it¶
Me.Refresh¶
Me.lstMove.SetFocus¶
Me.lstMove.Selected(intSaveIndex + 1) = True¶
Me.lstMove.ListIndex = intSaveIndex + 1¶
Else¶
If Me.lstMove.ListIndex < Me.lstMove.ListCount - 1 Then¶
'No item selected¶
MsgBox “First select an item from the list”¶
Else¶
'No action, already on last row...¶
End If¶
End If¶
End Sub¶
' * * * * *
Private Sub subMove()¶
' Switch two records based on dbKeyFrom and dbKeyTo¶
' the 0 is used to park the first entry.¶
' Otherwise a duplicate key error will occur !¶
CurrentDb.Execute UPDATE tblMove SET Sequence = 0 WHERE Sequence= &
dbKeyFrom & ;¶
Acs
Search JabSto ::




Custom Search