Move items in a list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I want to be able to select an item in a list box, then click on either an
up or down arrown and have the list item move in the corresponding direction.
I think I need to reference the listindex, but I'm not exactly sure. Any
help would be greatly appreciated.
 
If you have a value list as rowsource - then you can "move" selected item in
Array or collection (or whatever you get values from) and then assign a new
value list to list box. so it depends on how you load your listbox
 
I'm using Table/Query as my rowsource. What would the code look like done
this way?

Thanks!
 
then you have list items loaded in some field order. once you moved some
item up - you need to change order. so you can just swap a selected record
order field value with pervious one and then requery listbox
 
The problem is, I don't know how to write the code to do this. You make it
sound so simple though. I appreciate your optimism! :-)
 
Carolanne said:
The problem is, I don't know how to write the code to do this. You
make it sound so simple though. I appreciate your optimism! :-)
Click to expand...

It's not actually. Your ListBox is using a query with a sort order applied.
So your "Move Up" button would need to update the field that you are sorting
on in the base table such that when the query is re-run that item will end
up one position higher in the sort order then it is now. This means you
will need to add a number field whose sole purpose is to determine the sort
order.

If you use a Double number type then you would need to know the number
currently in that row and the value of the number of the rows one and two
positions higher so that you can set the new value to a value between those
other rows. That would be accomplished by adding those tow values together
and dividing by 2.

Another approach would be to update the row that is one higher to the value
of the row you are moving and vise-versa (swap the values).
 
Hi Carolanne,
below a code i used for similar purpose, but for subform. Hope it will give
you an idea!
i can it as MoveColumn True (up) or MoveColumn false (down)
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Private Sub MoveColumn(fDirectionUP As Boolean)
Dim lngCurrentRow As Long, rst As DAO.Recordset, lngCurOrder As Long,
strCriteria As String, lngNextOrder As Long, lngNextRow As String

lngCurrentRow = NIL(Me![frmImportStepSub].Form![ImpexStepDetailID])
If lngCurrentRow <> 0 Then
lngCurOrder = NIL(Me![frmImportStepSub].Form![ImpexStepDetailOrder])
Set rst = Me![frmImportStepSub].Form.RecordsetClone
strCriteria = "ImpexStep=" & Me![ImpexStepID] & " And
ImpexStepDetailOrder"
If fDirectionUP Then
strCriteria = strCriteria & "<" & lngCurOrder
rst.FindLast strCriteria
Else
strCriteria = strCriteria & ">" & lngCurOrder
rst.FindFirst strCriteria
End If
If Not rst.NoMatch Then
lngNextOrder = rst("ImpexStepDetailOrder")
lngNextRow = rst("ImpexStepDetailID")
CurrentDb.Execute "UPDATE tblImpexStepDetail SET
tblImpexStepDetail.ImpexStepDetailOrder = " & lngNextOrder & " WHERE
(((tblImpexStepDetail.ImpexStepDetailID)=" & lngCurrentRow & "));"
CurrentDb.Execute "UPDATE tblImpexStepDetail SET
tblImpexStepDetail.ImpexStepDetailOrder = " & lngCurOrder & " WHERE
(((tblImpexStepDetail.ImpexStepDetailID)=" & lngNextRow & "));"
rst.close
Me![frmImportStepSub].Requery
Me![frmImportStepSub].Form.RecordsetClone.FindFirst
"ImpexStepDetailID=" & lngCurrentRow
Me![frmImportStepSub].Form.Bookmark =
Me![frmImportStepSub].Form.RecordsetClone.Bookmark
End If
rst.close
Set rst = Nothing
End If
End Sub
 
Alex,

Thank you SO much for the code. I will try it out substituting my own form
and listbox in place of your code. Sure hope it works! Thanks again!

Carolanne

Alex Dybenko said:
Hi Carolanne,
below a code i used for similar purpose, but for subform. Hope it will give
you an idea!
i can it as MoveColumn True (up) or MoveColumn false (down)
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Private Sub MoveColumn(fDirectionUP As Boolean)
Dim lngCurrentRow As Long, rst As DAO.Recordset, lngCurOrder As Long,
strCriteria As String, lngNextOrder As Long, lngNextRow As String

lngCurrentRow = NIL(Me![frmImportStepSub].Form![ImpexStepDetailID])
If lngCurrentRow <> 0 Then
lngCurOrder = NIL(Me![frmImportStepSub].Form![ImpexStepDetailOrder])
Set rst = Me![frmImportStepSub].Form.RecordsetClone
strCriteria = "ImpexStep=" & Me![ImpexStepID] & " And
ImpexStepDetailOrder"
If fDirectionUP Then
strCriteria = strCriteria & "<" & lngCurOrder
rst.FindLast strCriteria
Else
strCriteria = strCriteria & ">" & lngCurOrder
rst.FindFirst strCriteria
End If
If Not rst.NoMatch Then
lngNextOrder = rst("ImpexStepDetailOrder")
lngNextRow = rst("ImpexStepDetailID")
CurrentDb.Execute "UPDATE tblImpexStepDetail SET
tblImpexStepDetail.ImpexStepDetailOrder = " & lngNextOrder & " WHERE
(((tblImpexStepDetail.ImpexStepDetailID)=" & lngCurrentRow & "));"
CurrentDb.Execute "UPDATE tblImpexStepDetail SET
tblImpexStepDetail.ImpexStepDetailOrder = " & lngCurOrder & " WHERE
(((tblImpexStepDetail.ImpexStepDetailID)=" & lngNextRow & "));"
rst.close
Me![frmImportStepSub].Requery
Me![frmImportStepSub].Form.RecordsetClone.FindFirst
"ImpexStepDetailID=" & lngCurrentRow
Me![frmImportStepSub].Form.Bookmark =
Me![frmImportStepSub].Form.RecordsetClone.Bookmark
End If
rst.close
Set rst = Nothing
End If
End Sub


Carolanne said:
The problem is, I don't know how to write the code to do this. You make
it
sound so simple though. I appreciate your optimism! :-)
Click to expand...
Click to expand...
 
Back
Top