VBA to filter subform with Listbox selections

[kellyk87]

HI All I am trying to get my VBA to filter the subform, as currently it does nothing! I have copied it from a source on another forum and at the end of the VBA it originally opened up the query.

However I am trying to tailor this query so that instead of it opening the query I can have a datasheet on the form that displays the filtered records..

There are 3 multiselect listboxes

here is my code

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.
 Private Sub cmdOK_Click()
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim qry As ADOX.View
    Dim varItem As Variant
    Dim strSchool As String
    Dim strGovType As String
    Dim strAuthority As String
    Dim strGovTypeCondition As String
    Dim strAuthorityCondition As String
    Dim strSQL As String
 
' Build criteria string for School
    For Each varItem In Me.lstSchool.ItemsSelected
        strSchool = strSchool & ",'" & Me.lstSchool.ItemData(varItem) & "'"
    Next varItem
    If Len(strSchool) = 0 Then
        strSchool = "Like '*'"
    Else
        strSchool = Right(strSchool, Len(strSchool) - 1)
        strSchool = "IN(" & strSchool & ")"
    End If
' Build criteria string for GovType
    For Each varItem In Me.lstGovType.ItemsSelected
        strGovType = strGovType & ",'" & Me.lstGovType.ItemData(varItem) & "'"
    Next varItem
    If Len(strGovType) = 0 Then
        strGovType = "Like '*'"
    Else
        strGovType = Right(strGovType, Len(strGovType) - 1)
        strGovType = "IN(" & strGovType & ")"
    End If
' Build criteria string for Authority
    For Each varItem In Me.lstAuthority.ItemsSelected
        strAuthority = strAuthority & ",'" & Me.lstAuthority.ItemData(varItem) & "'"
    Next varItem
    If Len(strAuthority) = 0 Then
        strAuthority = "Like '*'"
    Else
        strAuthority = Right(strAuthority, Len(strAuthority) - 1)
        strAuthority = "IN(" & strAuthority & ")"
    End If
' Get GovType condition
    If Me.optAndGovType.Value = True Then
        strGovTypeCondition = " AND "
    Else
        strGovTypeCondition = " OR "
    End If
' Get Authority condition
    If Me.optAndAuthority.Value = True Then
        strAuthorityCondition = " AND "
    Else
        strAuthorityCondition = " OR "
    End If
' Build SQL statement
    strSQL = "SELECT [Current Governor Details query].* FROM [Current Governor Details query] " & _
             "WHERE [Current Governor Details query].[School] " & strSchool & _
             strGovTypeCondition & "[Current Governor Details query].[Governor Type] " & strGovType & _
             strAuthorityCondition & "[Current Governor Details query].[Authority] " & strAuthority & ";"
 ' Apply the SQL statement to the stored query
    cat.ActiveConnection = CurrentProject.Connection
    Set cmd = cat.Views("qryBirthDateQuery").Command
    cmd.CommandText = strSQL
    Set cat.Views("qryBirthDateQuery").Command = cmd
    Set cat = Nothing
[COLOR=red]Me.qryBirthDateQuery_subform1.Requery 'this is the bit that I can't get to work'[/COLOR]
 ' If required the dialog can be closed at this point
 '    DoCmd.Close acForm, Me.Name
' Restore screen updating
    Exit Sub
 End Sub

Any help would be appreciated, I'm quite new to this
Thanks

 

[richsql]

It could be you need to use the name of the subform control, not the name of the subform, though these are often the same...

Forms![MainForm]![subform control name].Form.Requery

or, if you are on the main form

 Me.[subform control name].Form.Requery

 

[kellyk87]

Thanks RichSQL for your suggestion, unfortunately neither work when I go into the query I can see that it has put the filters into the query but for some reason the subform isn't updating?

 

[richsql]

Is your subform data source and controls bound to the query qryBirthDateQuery ?

 

[kellyk87]

yes it is?

 

[kellyk87]

Okay so I found a solution through looking at another thread, I changed the record source of the subform,

Me.qryBirthDateQuerysubform1.Form.RecordSource = strSQL

The solution works ....but very slowly!

Thanks for everyone's help though