CODEWITHSUNDEEP
ms-accessvba
vuyy1182
vuyy1182

Reputation: 1686

Does access VBA has Listbox.List method as excel VBA has

I'm writing code in access vba for the list box items to move up and down. Needs to use .List Property in access . But it throws an error says no method or member found. Any replace method with .List ? Researching on this more than 4 days. Private Sub cmdUP_Click()

  Dim i As Long
 Dim leaveAlone As Boolean
 Dim pos As Long
 Dim Temp As String

pos = 0

With Me.lbfNames
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 = .RowSource(i - 1)
    .RowSource(i - 1) = .RowSource(i) ' before i used .List instead of rowsource
    .RowSource(i) = Temp
    .ListIndex = i - 1
    .Selected(i) = False
    .Selected(i - 1) = True
    End If

    End If
    Next

    End With

Upvotes: 0

Views: 3991

Answers (2)

Danish ALI
Danish ALI

Reputation: 75

Short Answer: No, MS Access VBA doesn't have ListBox.List(row, column) but instead it has ListBox.AddItem(Item, Index) and ListBox.RemoveItem(Index)

For Multi-Column ListBoxes semi-colon character ';' could be used to separate column items i.e. myMultiColListBox.AddItem("Col_1_item;Col_2_item;Col_3_item")

Upvotes: 1

vuyy1182
vuyy1182

Reputation: 1686

I've figured that out, how to do it in access. But set list box Multiselect property to 'None'.

 Moving Down

 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

      Moving up

   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

Upvotes: 1

Related Questions