none
Filter query RRS feed

  • Question

  • Hi all,

    I have a form based on a query witch makes totals

    The query is named Qry_result and has sql:

    SELECT qry_Filter.ID, Sum(Table2.Maand1) AS SumOfMaand1, Sum(Table2.Maand2) AS SumOfMaand2
    FROM qry_Filter INNER JOIN Table2 ON qry_Filter.ID = Table2.id
    GROUP BY qry_Filter.ID;

    This is a simplified example

    The sql for qry_Filter is:

    SELECT Table1.ID, Table1.Field1, Table1.Field2, Table1.Field3
    FROM Table1
    WHERE (((Table1.[field1])='2'));

    The goal is to filter the records based on values field1, field2 and field3

    The reason why I have not insert the fields "field1,2,3" is because they will "grouped by" in the qry_result

    I thought to work with qury parameters to filter the qry_Filter but that is failing me.

    When I change the query qry_Filter with VBA and I make a form.requery, the data doesn't change. It only change when I close and open the form.

    VBA code to change the qry_Filter:

    Private Sub CmbValue_AfterUpdate()
    Dim rst As Recordset
    Dim qry As QueryDef
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
        
    CurrentDb.QueryDefs.Delete "qry_Filter"
    Set qdf = CurrentDb.CreateQueryDef("qry_Filter")
        
     strSQL = "SELECT Table1.ID, Table1.Field1, Table1.Field2, Table1.Field3" _
          & " FROM Table1 where field1='" & Me.CmbValue & "'"
        
     qdf.SQL = strSQL
     Me.Requery  'this doesn't update the form, only when i close and open the form again
     qdf.Close
     Set qdf = Nothing

    End Sub

    I woul'd be easier to say

    me.filter="field1=...."

    Anyone a suggestion?

    Tuesday, December 1, 2015 12:54 PM

Answers

  • I believe the problem here is that, because you are only changing the filter query, Access doesn't recognize that the form's recordsource is changed, so it uses the cached recordset that it already queried.  You can force it to discard the cached recordset by setting the form's RecordSource property, even if you just set it to its own current value, rather than just requerying the form.  Here's some much-simplified code that seems to work:

    Private Sub CmbValue_AfterUpdate()
    
        Dim strSQL As String
            
        strSQL = "SELECT Table1.ID, Table1.Field1, zTable1.Field2, zTable1.Field3" _
             & " FROM Table1 where Field1='" & Me.CmbValue & "'"
           
        CurrentDb.QueryDefs("qry_Filter").SQL = strSQL
        
        Me.RecordSource = Me.RecordSource
    
    End Sub
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Lteu Wednesday, December 2, 2015 8:41 AM
    Wednesday, December 2, 2015 2:19 AM

All replies

  • I am a bit confused trying to follow your post.  In general terms: the form object has a record source i.e. a query (or a table).

    So then if you modify/rerun the query (that is the record source of the form) with a different criteria - then doing the form's .requery should definitely refresh its data set and you should see new values consistent with the change of the query record source.

    Filtering is working at the form object level property - and can only result in a sub set of the underlying record source.  You must refer to the fields of the record source.  You can't refer to fields of Table1 (as in your example) if Table1 is not the record source.

     Hope this helps a little......

    Tuesday, December 1, 2015 11:55 PM
  • I believe the problem here is that, because you are only changing the filter query, Access doesn't recognize that the form's recordsource is changed, so it uses the cached recordset that it already queried.  You can force it to discard the cached recordset by setting the form's RecordSource property, even if you just set it to its own current value, rather than just requerying the form.  Here's some much-simplified code that seems to work:

    Private Sub CmbValue_AfterUpdate()
    
        Dim strSQL As String
            
        strSQL = "SELECT Table1.ID, Table1.Field1, zTable1.Field2, zTable1.Field3" _
             & " FROM Table1 where Field1='" & Me.CmbValue & "'"
           
        CurrentDb.QueryDefs("qry_Filter").SQL = strSQL
        
        Me.RecordSource = Me.RecordSource
    
    End Sub
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Lteu Wednesday, December 2, 2015 8:41 AM
    Wednesday, December 2, 2015 2:19 AM
  • Thanks Dirk, that was the problem. I've replaced the requery in Me.RecordSource = Me.RecordSource
    Wednesday, December 2, 2015 8:45 AM