Move items in a list box

C

Carolanne

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.
 
A

Alex Dybenko

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
 
C

Carolanne

I'm using Table/Query as my rowsource. What would the code look like done
this way?

Thanks!
 
A

Alex Dybenko

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
 
C

Carolanne

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! :)
 
R

Rick Brandt

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).
 
A

Alex Dybenko

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
 
C

Carolanne

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...
 
Top