Trouble with WITH... and report (1 Viewer)

[Notiophilus]

Access 2010. I have a subform which dynamically shows either a form or a report. On the main form there is a button to clear the recordset and filters of each, and for clarity's sake I used a with... end with statement. Access disnae like it.

With the form, this works:

If Me.fsubContainer.SourceObject = "fsubBrowseAll" Then       
        
        ' some stuff here
        With Me.fsubContainer.Form
            .RecordSource = "tblStory"          ' Clear recordsource
            .Filter = "(False)"                 ' and hide ALL records
            .FilterOn = True
        End With

But this doesn't:

    ElseIf Me.fsubContainer.SourceObject = "Report.rptStories" Then
        With Me.fsubContainer.Report
            .RecordSource = "tblStory"          ' Clear rs and show all records
[B]            [COLOR=Red].Filter = ""                        ' ##Run-time error 5: invalid procedure or argument[/COLOR][/B]
            .FilterOn = True
        End With

    End If

This, however, does:

        Me.fsubContainer.Report.RecordSource = "tblStory"
[COLOR=SeaGreen] [B]       Me.fsubContainer.Report.Filter = ""                 [/B]
[/COLOR]        Me.fsubContainer.Report.FilterOn = True

Why? Is this just inconsistency in Access, or might it be a sign of a deeper problem in the code? (For whatever reason, the last statement gave several errors until I changed Report.Filter = "(False)" to .Filter = "", so I don't know if it's just that reports are rather touchy)

 

[JHB]

Have you tried, (I know it was not what you asked about):

  ElseIf Me.fsubContainer.SourceObject = "Report.rptStories" Then
    With Me.fsubContainer
      [B][COLOR=Red].Report[/COLOR][/B][COLOR=Red][COLOR=Black].[/COLOR][/COLOR]RecordSource = "tblStory"
      [B][COLOR=Red].Report[/COLOR][/B].Filter = ""
      [B][COLOR=Red].Report[/COLOR][/B].FilterOn = True
    End With
  End If

 

[Ranman256]

you wouldnt filter in a report. The query does the 'filtering'

and you wouldnt set the filter = "", instead just turn it off...
FilterOn = false

 

[Notiophilus]

Have you tried, (I know it was not what you asked about):

  ElseIf Me.fsubContainer.SourceObject = "Report.rptStories" Then
    With Me.fsubContainer
      [B][COLOR=Red].Report[/COLOR][/B][COLOR=Red][COLOR=Black].[/COLOR][/COLOR]RecordSource = "tblStory"
      [B][COLOR=Red].Report[/COLOR][/B].Filter = ""
      [B][COLOR=Red].Report[/COLOR][/B].FilterOn = True
    End With
  End If

That seems to have done it. Thanks!

@Ranman - but reports do have a filter function. Why not use it?
By having both recordsource query and filter I can toggle the filter on and off without performing the query again. (Filtered fields are those I might want to toggle - let's say whether I own a book or not. I can search for [books in category A] [that I own] and switch to [books in category A][all] without a requery.) Scratch that - I also realised I was missing a .Report.Requery as otherwise it won't update, which probably makes the whole "not requerying" thing moot.

 

[Uncle Gizmo]

- but reports do have a filter function. Why not use it?

I have an ulterior motive for responding to your comment about filters. I seldom use them, I use SQL statements. I can't recall all of the steps that resulted in me favouring SQL statements over filters, particularly as they are fundamentally the same. I'm gathering information on it in preparation for a blog, hence my interest in this thread. I would also welcome anyone else chipping in with their views, comments and criticisms...

It was this line of code that reminded me of one of the reasons I use SQL statements as opposed to filters.

.RecordSource = "tblStory" ' Clear rs and show all records

If your form was attached to a very large table then this could make it load slowly because it is drawing all of the records into the form. However to use a filter effectively, you need access to all of the records.

When you start thinking about ways of getting your forms to load quicker, you write SQL statements to restrict the number of records that you pass into the forms record source. In other words you filter with the SQL statements, instead of using the forms filter function.

I'm not saying I don't like filters, or I don't use filters, I'm saying that the way I construct my database means I seldom need to use them.

The other thing I have noticed is once I have constructed an SQL statement for the record source of a form then it's very easy to make this same SQL statement available for the record sort of a report. I don't think that's an important consideration it's just how things flow from your initial decisions.