show few rows as selected in list box

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
V 

Answer 1

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

Answer 2

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.

Answer 3

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