none
show few rows as selected in list box RRS feed

  • Question

  • Listbox is loaded from main table,  there is another sub table has fewer rows but same data as main table.

    Need to select rows in listbox based on sub table


    is it possible do this functionality in MS Access Form?

    Thanks
    Wednesday, January 27, 2016 9:45 PM

Answers

  • I think so. You can loop through the items in the Listbox and set the Selected property to True if it matches a record in the sub table. Hope that helps...
    • Marked as answer by David_JunFeng Friday, February 5, 2016 9:07 AM
    Wednesday, January 27, 2016 10:01 PM
  • >>>is it possible do this functionality in MS Access Form?

    According to your description, it is possible to do this. You can use the Selected property in Visual Basic to determine if an item in a list box is selected.

    Me!Listbox.Selected(4) = True


    if you want to select ListBox item in subTable, refer to below code:

    Dim rs As DAO.Recordset
    Dim i as integer
    
    Set rs = CurrentDb.OpenRecordset("YourTable")
    If rs.RecordCount = 0 Then Exit Sub
    Do Until rs.EOF
       For i = 0 To Me.YourListBox.ListCount - 1
           If CLng(Me.YourListBox.ItemData(i)) = "" & rs!id Then
              Me.YourListBox.Selected(i) = True
              End If
           Next
           rs.MoveNext
    Loop
    

    In addition you could clear all selected item in ListBox, refer to below code:

    Function ClearList(lst As ListBox) As Boolean
    On Error GoTo Err_ClearList
        Dim varItem As Variant
    
        If lst.MultiSelect = 0 Then
            lst = Null
        Else
            For Each varItem In lst.ItemsSelected
                lst.Selected(varItem) = False
            Next
        End If
    
        ClearList = True
    
    Exit_ClearList:
        Exit Function
    
    Err_ClearList:
        Call LogError(Err.Number, Err.Description, "ClearList()")
        Resume Exit_ClearList
    End Function
    

    • Marked as answer by David_JunFeng Friday, February 5, 2016 9:07 AM
    Thursday, January 28, 2016 2:07 AM

All replies

  • I think so. You can loop through the items in the Listbox and set the Selected property to True if it matches a record in the sub table. Hope that helps...
    • Marked as answer by David_JunFeng Friday, February 5, 2016 9:07 AM
    Wednesday, January 27, 2016 10:01 PM
  • I'm not sure exactly what you are doing with this listbox. But when selecting a subset of items in a list box, I usually display two listboxes side by side. The leftmost one has all the items minus those selected and the righmost one contains the selected items. Then I have right and left pointing buttons between the listboxes to select or unselect items. When selecting an item, it moves from the left listbox to the right listbox and when unselecting items move from the right to the left.

    This is pretty easy to code and is intuitive to most users.

    A similar dialog is used in some MS Access wizards.

    Wednesday, January 27, 2016 10:23 PM
  • >>>is it possible do this functionality in MS Access Form?

    According to your description, it is possible to do this. You can use the Selected property in Visual Basic to determine if an item in a list box is selected.

    Me!Listbox.Selected(4) = True


    if you want to select ListBox item in subTable, refer to below code:

    Dim rs As DAO.Recordset
    Dim i as integer
    
    Set rs = CurrentDb.OpenRecordset("YourTable")
    If rs.RecordCount = 0 Then Exit Sub
    Do Until rs.EOF
       For i = 0 To Me.YourListBox.ListCount - 1
           If CLng(Me.YourListBox.ItemData(i)) = "" & rs!id Then
              Me.YourListBox.Selected(i) = True
              End If
           Next
           rs.MoveNext
    Loop
    

    In addition you could clear all selected item in ListBox, refer to below code:

    Function ClearList(lst As ListBox) As Boolean
    On Error GoTo Err_ClearList
        Dim varItem As Variant
    
        If lst.MultiSelect = 0 Then
            lst = Null
        Else
            For Each varItem In lst.ItemsSelected
                lst.Selected(varItem) = False
            Next
        End If
    
        ClearList = True
    
    Exit_ClearList:
        Exit Function
    
    Err_ClearList:
        Call LogError(Err.Number, Err.Description, "ClearList()")
        Resume Exit_ClearList
    End Function
    

    • Marked as answer by David_JunFeng Friday, February 5, 2016 9:07 AM
    Thursday, January 28, 2016 2:07 AM