Linking a form to multiple list boxes

Question

I have a master form which contains three list boxes and one sub form. I would like to build a routine which allows me to switch links between the sub form and the three list boxes. Is this possible? Or do i have to create three copies of the same sub form and hide two while one the other is activated?

To be practical, my form will work like this: The sub form contains a list of records of people participating in a project, their specific role, and which internal team they come from. I would like to use three list boxes to allow the user to filter this form by either:

(1) All participants coming from a certain team (2) All participants by roles (titles) (3) Filter by name of particants

Where I am short is on how to re-link the filter on the sub form so that it changes from list box to list box as the user passes from filter to filter.

Using Krish's suggestion below as a simple test i am trying the following code but am getting a compilation error message on the recordsource line stating that it is impossible to find the method or the data member.. Not sure what that means:

Private Sub lstRoles_AfterUpdate()

Dim SQL_GET As String
SQL_GET = "SELECT * from tblProjectGovernanceResources where ((role like '" & lstRoles.Value & "')"
Me.frmProjectGovernanceResources.RecordSource = SQL_GET

End Sub

Answer 1

Thanks a lot! This did it all!

Private Sub lstRoles_AfterUpdate()

Dim SQL_GET As String SQL_GET = "SELECT * from tblProjectGovernanceResources where ([role] = '" & lstRoles.Value & "')"

Me.frmProjectGovernanceResources.Form.RecordSource = SQL_GET

End Sub

Answer 2

I created some code for the easiest version possible. This means all of your listboxes have the 'multi select' property set to 'None' (this means you can't select multiple items in the list and you can't 'deselect' an item by clicking on it again. I did add some code at the end so you can see how a different multi-select option may work.

My form has three listboxes, a subform, and two buttons. One button will clear all selections in all listboxes. The other button applies the filter to the subform.

Option Compare Database
Option Explicit

'*** NOTE!!! THIS CODE ASSUMES YOU HAVE SET YOUR LISTBOX PROPERTY to 'NONE'.

' IF YOU SET 'MULTI SELECT' To 'SIMPLE' or 'EXTENDED', you MUST use different code to find all selected items.

Dim strWhereTeam        As String
Dim strWhereRole        As String
Dim strWhereParticipant As String

Private Sub cmdClear_Click()
' Clear all selections in all listboxes
Dim i   As Integer
    For i = 0 To Me.lstParticipant.ListCount        'Deselect ALL rows in Listbox
        lstParticipant.Selected(i) = False
    Next i
    For i = 0 To Me.lstRole.ListCount               'Deselect ALL rows in Listbox
        lstRole.Selected(i) = False
    Next i
    For i = 0 To Me.lstTeam.ListCount               'Deselect ALL rows in Listbox
        lstTeam.Selected(i) = False
    Next i
    strWhereTeam = ""
    strWhereRole = ""
    strWhereParticipant = ""
    Me.MySubForm.Form.Filter = ""                   ' Reste filter to NONE
    Me.MySubForm.Form.FilterOn = False
End Sub

Private Sub cmdFilter_Click()
'Build Filter (concatenate three selections)
Dim strFilter   As String
    strFilter = ""
    If strWhereTeam & "" <> "" Then
        strFilter = strWhereTeam
        If strWhereRole & "" <> "" Then
            strFilter = strFilter & " AND " & strWhereRole
            If strWhereParticipant & "" <> "" Then
                strFilter = strFilter & " AND " & strWhereParticipant
            End If
        Else
            If strWhereParticipant & "" <> "" Then
                strFilter = strFilter & " AND " & strWhereParticipant
            End If
        End If
    ElseIf strWhereRole & "" <> "" Then
        strFilter = strWhereRole
        If strWhereParticipant & "" <> "" Then
            strFilter = strFilter & " AND " & strWhereParticipant
        End If
    ElseIf strWhereParticipant & "" <> "" Then
        strFilter = strWhereParticipant
    End If

    If strFilter = "" Then
        Me.MySubForm.Form.Filter = ""
        Me.MySubForm.Form.FilterOn = False
    Else
        Me.MySubForm.Form.Filter = strFilter
        Me.MySubForm.Form.FilterOn = True
    End If
End Sub

Private Sub lstParticipant_Click()
    strWhereParticipant = "[Participant] = '" & Me.lstParticipant.ItemData(Me.lstParticipant.ListIndex) & "'"
    Debug.Print strWhereParticipant
End Sub

Private Sub lstRole_Click()
    strWhereRole = "[Role] = '" & Me.lstRole.ItemData(Me.lstRole.ListIndex) & "'"
    Debug.Print strWhereRole
End Sub

Private Sub lstTeam_Click()
    If Me.lstTeam.MultiSelect <> 0 Then
        MsgBox "You have set the 'Multi Select' property to either Simple or Extended. This code may not work!", vbOKOnly + vbCritical, "ListBox MultiSelect not 'None'"
    End If
    strWhereTeam = "[Team] = '" & Me.lstTeam.ItemData(Me.lstTeam.ListIndex) & "'"
    Debug.Print strWhereTeam
    'Simple_Code
End Sub

'' Sample code if set 'Multi Select' to 'Simple' or 'Extended'
'Sub Simple_Code()
'    Dim var       As Variant
'    strWhereTeam = ""
'    For Each var In Me.lstTeam.ItemsSelected
'        strWhereTeam = strWhereTeam & "[Team] = '" & Me.lstTeam.ItemData(var) & "' OR "
'    Next var
'    strWhereTeam = "(" & left(strWhereTeam, Len(strWhereTeam) - 4) & ")"
'    Debug.Print strWhereTeam
'End Sub

Answer 3

you can retrieve the selected value from a listbox simply byt listbox1.value. As Wayne G pointed. you would add a code in your listbox_after_update event to update your subform's recordsource.

something like:

dim SQL_GET as string
sql_get = "SELECT * from tbl_myTAble where ((condition like '" & listbox1.value & "') OR (condition2 like '"& listbox2.value &"') OR (condition3_number like "& listbox3.value &"))

me.mysubform.recordsource = sql_Get

obviously you need to improve this as per your requirements. Try this and for a better answer, produce what you have coded so far..