How to move up and down selected item in access form listbox

[svuyyuru]

I've designed access form with one listbox and added 'up' and 'down' buttons to move up and down the selected item in the listbox. i.e from its selected position to one item up when 'up' button clicked and same as one item down when down button is clicked. Any VBA code will do this functionality?

 

[jdraw]

What is the point of this? Couldn't you sort the list - even if you use a hidden field?

 

[svuyyuru]

I didn't try use hidden field. same requirement working on excel, but not with access with VBA.
Here s my code for excel list box

Dim i As Long
Dim leaveAlone As Boolean
Dim pos As Long
Dim Temp As String
pos = 0
With Me.lbLeft
For i = 0 To .ListCount - 1
leaveAlone = False

If .Selected(i) Then

If i = pos Then
leaveAlone = True
End If

pos = pos + 1

If leaveAlone = False Then
Temp = .List(i - 1)
.List(i - 1) = .List(i)
.List(i) = Temp
.ListIndex = i - 1
.Selected(i) = False
.Selected(i - 1) = True
End If

End If
Next
End With

But this code not working in access vba, throwing .List member not found.

 

[Mile-O]

You can't do many things in an Access listbox simply because it's not Excel and, given they are different environments, the uses of objects are different too. You can't, for example, usa AddItem in Access for your listbox. In Access, they tend to be based off a query for data selection to populate whatever field in a table they are bound to. The order they are presented in shouldn't matter. Why you would want to shuffle records about in a listbox will likely remain unclear to everyone but yourself, but if you want to do it then your underlying table is probably best to have a field called SortOrder with each item given a number between 1 and however long your list is. Then, on your up and down buttons, some VBA can swap the SortOrder around between the record above or below - whatever way your are going. And then you requery the listbox.

 

[Galaxiom]

You can't, for example, usa AddItem in Access for your listbox. In Access, they tend to be based off a query for data selection to populate whatever field in a table they are bound to.

You might want to look at that again. The RowSourceType needs to be set to Value List but AddItem is certainly available.

 

[Mile-O]

Hmmm, can't think why I would think it wasn't with such certainty. But thanks for keeping me right. I'm a bit rusty in this, but getting back into it all quite fast.

 

[svuyyuru]

I've figured that we can do this way. Set listbox Multiselect property to 'None'
<CODE>
Private Sub cmdDown_Click()
Dim sText As String
Dim iIndex As Integer
Dim bottomLimit As Integer
iIndex = lbfNames.ListIndex
bottomLimit = lbfNames.ListCount - 1
'check: only proceed if there is a selected item
If lbfNames.ListCount > 1 Then
If iIndex >= bottomLimit Then
MsgBox ("Can not move the item down any further.")
Exit Sub
End If
'save items text and items indexvalue
sText = lbfNames.Column(0, iIndex)
If iIndex < bottomLimit Then
lbfNames.RemoveItem iIndex
'place item back in new position
lbfNames.AddItem sText, iIndex + 1
End If
'if you keep that item selected
'you can keep moving it by pressing btnMoveDown
lbfNames.Selected(iIndex + 1) = True
iIndex = iIndex + 1
End If
End Sub
</CODE>

****Moving up
<CODE>
Private Sub cmdUP_Click()
Dim sText As String
Dim iIndex As Integer
iIndex = lbfNames.ListIndex
' ReDim iIndex(0 To 10)
'check: only proceed if there is a selected item
If lbfNames.ListCount > 1 Then
'index 0 is top item which can't be moved up!
If iIndex <= 0 Then
MsgBox ("Can not move the item up any higher.")
Exit Sub
End If
' If iIndex = -1 Or lbfNames.ListCount > 1 Then
'save items text and items indexvalue
sText = lbfNames.Column(0, iIndex)
lbfNames.RemoveItem iIndex
'place item back on new position
lbfNames.AddItem sText, iIndex - 1
'if you keep that item selected
'you can keep moving it by pressing cmdUp
lbfNames.Selected(iIndex - 1) = True
iIndex = iIndex - 1
End If
End Sub
</CODE>

 

[Galaxiom]

My favourite way of managing listboxes uses an ADO recordset. This provides Sort and Filter methods.