Using List boxes to query a form will only show info for criteria not the rest of the fields. Error Name!? for other fields

This code allows a user to select criteria as many as they need In 3  List Boxes. Then that information is suppose to populate a Sub form.

The data that is selected shows the criteria that is selected but doesnt show the information for the rest of the fields in the subform. Where and how do I put the code to join the forms to work together and show all the data for the subform?

Below is the code the tables were linked so I had to change them.

 Dim sf As SubForm
    Dim MODKitStr As String, DIVStr As String, BdeStr As String
    Dim SQLStr As String
    Dim ListCounter As Integer
    Dim AnItem As Variant
     
    For Each AnItem In Me.lstMODKit.ItemsSelected
        If Not IsNull(AnItem) Then
            MODKitStr = MODKitStr & "'" & Me.lstMODKit.ItemData(AnItem) & "',"
        End If
    Next AnItem
    If Len(MODKitStr) = 0 Then
        Call noselection("A")
        Exit Sub
    Else
        MODKitStr = Left(MODKitStr, Len(MODKitStr) - 1)
    End If
     
    For Each AnItem In Me.lstDIV.ItemsSelected
        If Not IsNull(AnItem) Then
            DIVStr = DIVStr & "'" & Me.lstDIV.ItemData(AnItem) & "',"
        End If
    Next AnItem
    If Len(DIVStr) = 0 Then
        Call noselection("B")
        Exit Sub
    Else
        DIVStr = Left(DIVStr, Len(DIVStr) - 1)
    End If
       
    For Each AnItem In Me.lstBde.ItemsSelected
        If Not IsNull(AnItem) Then
            BdeStr = BdeStr & "'" & Me.lstBde.ItemData(AnItem) & "',"
        End If
    Next AnItem
    If Len(BdeStr) = 0 Then
        Call noselection("C")
        Exit Sub
    Else
        BdeStr = Left(BdeStr, Len(BdeStr) - 1)
    End If
    SQLStr = "SELECT tblMODHistory.Bde,tblMODHistory.DIV,tblMODHistory.[MOD Kit]   " & _
    "FROM qryMODHistory " & _
    "WHERE qryMODHistory.Bde IN(" & BdeStr & ") and qryMODHistory.DIV IN(" & DIVStr & ") and qryMODHistory.[MOD Kit] IN(" & MODKitStr & ")"
    Set sf = Me.[qryMODHistory_Crosstab subform]
    Debug.Print SQLStr
    sf.Controls.Parent.RecordSource = SQLStr
    sf.Requery

   End Sub
Private Sub noselection(LType As String)
    Dim msg As String
    Select Case LType
    Case Is = "A"
        msg = "No MOD Kits "
    Case Is = "B"
        msg = "No DIvision "
    Case Is = "C"
        msg = "No Brigade "
    Case Else
        msg = "Error : Do not know list box type :"
        End Select

    Me.[qryMODHistory_Crosstab subform].Controls.Parent.RecordSource = "SELECT qryMODHistory.Bde  ,qryMODHistory.DIV, qryMODHistory.[MOD Kit] FROM qryMODHistory WHERE qryMODHistory.[MOD Kit]  = '£$^'"
    Me.[qryMODHistory_Crosstab subform].Requery
    msg = msg & "have been selected" & vbCrLf & "Record was not updated"
    MsgBox msg
End Sub
 


Private Sub lstMODKit_AfterUpdate()
    Call DoubleMultiSelect_Click
End Sub
 
 
Private Sub lstDIV_AfterUpdate()
    Call DoubleMultiSelect_Click
End Sub

Private Sub lstBde_AfterUpdate()
    Call DoubleMultiSelect_Click
End Sub


HelpwithListSearch.zip