Read Datasheet Filter or Use Filtered Datasheet Recordset

[ESquared]

You can bind a view, table function, or stored procedure to a subform instead of a form object. The query will display as a datasheet. Access instantiates a form behind the scenes, adds appropriate controls, and displays the form in datasheet view.

In the datasheet, you can right-click and select various filters: Filter By Selection, Filter Excluding Selection, Filter For, and so on.

After applying some filters, it would be really nice to use the underlying filtered recordset in some reports. This would work as a poor-man's ultimate report filter function, by allowing a user to filter on any column for any value and then use as the source for the report. However, Access (ADP) is doing the filtering differently than the usual Recordset.Filter. I say "poor-man's" because the client is paying very little, needs the answer very soon, and expects a lot. I don't have time to write a big report filter form that can handle any data type and filter type.

Browsing the datasheet's properties reveals some interesting things:

- The underlying recordset is not filtered.
- Form.Recordset.Filter = 0.
- Form.RecordCount is the original record count, not the filtered one.
- Form.CurrentRecord does show the filtered value, starting at 1.
- Form.Recordset.AbsolutePosition moves around to the value of the underlying recordset as you select each filtered record. So if the original recordset has 3000 records, when you click on filtered record 1 it might be 2892 from the original recordset.
- Form.RowHeight and Form.Controls(x).ColumnHeight work as normal for datasheets.
- Form.SelTop and SelHeight work normally.
- Setting a report's RecordSet property to the datasheet's recordset works, but it is also not filtered.
- When two adjacent records from the underlying recordset are both visible in the filtered recordset, issuing a MoveNext or a MovePrevious works: the next or previous filtered record becomes selected. Issuing a MoveNext or MovePrevious does nothing if the next (or previous) underlying record is not in the filtered recordset.
- A quick test against a filtered continuous subform indicates similar behavior: the filtered records are fewer than the underlying recordset which has no .Filter applied.
- Applying a filter to the underlying recordset works and doesn't remove the record from the displayed records.

I suspect that Access is using a clone or copy of the original recordset and synchronizing the two somehow.

Does anyone have any ideas? Here are mine:
- Somehow get access to the datasheet filter text and use it as a WHERE clause on the report. I don't know how to do this.
- Somehow get access to the actual filtered recordset and use that. I don't know how to do this.
- Step through the datasheet, and knowing the key columns, extract the information necessary to duplicate the filter in the report. I haven't been able to figure out how to do this.

... And now the filter's aren't working on the datasheet, when they were before, giving the message for any filter I try, "Cannot apply Filter on one or more fields specified in the Filter property." I don't think I changed anything that could cause this.

Help? Please? Thank you!!!!

 

[danvlas]

What I did:

In a tabular form, I placed text/combo boxes in form header, naming them srcRecordsetFieldName

After that, I placed checkboxes named chkRecordsetFieldName

Then, put the view name in the Tag property of the form. It can also be a function, but mind the parameters.

Then, in a command button OnClick procedure, I put:

Dim ctr As Control
Dim strSemn As String
Dim strseparator As String
Dim strFltr As String

strFltr = " Where 1=1"

For Each ctr In Me.Controls
    If Left(ctr.Name, 3) = "src" Then
        Select Case Me.Recordset.Fields(Right(ctr.Name, Len(ctr.Name) - 3)).Type
        Case 130, 202, 200
            strseparator = "'"
        Case Else
            strseparator = ""
        End Select
        If Me("chk" & Right(ctr.Name, Len(ctr.Name) - 3)) = True Then
            strSemn = "="
        Else
            strSemn = "<>"
        End If
        
        If Not IsNull(ctr) Then
            strFltr = strFltr & " And " & Right(ctr.Name, Len(ctr.Name) - 3) & strSemn & strseparator & ctr & strseparator
        End If
         
    End If
    
Next


Me.RecordSource = "Select * From " & Me.Tag & strFltr

User enters/selects the relevant information in the 'src', boxes, unchecks the 'chk' boxes for 'all BUT that', then clicks the button.

Form is 'filtered' (not really, but does exactly the same), totals are re-calculated and by passing the OpenArgs of the report to 'Me.Recordsource' you can have the report open with exactly the same filter, by setting Me.RecordSource=Me.OpenArgs

If your Access is lower than 2003, you should get the recordsource in another way (maybe Screen.ActiveForm.RecordSource).

Tip: instead of checkboxes you could make some combo boxes containing the signs: =, <, <> and so on for more flexibility.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ESquared]

Thanks for your ideas, Daniel.

It appears that I was simply uninformed about how Access does normal filters on forms from the toolbars and right-click menus:

Form.Recordset.Filter remains unchanged
but Form.Filter DOES change.

By not having the report bound to a recordsource and setting the appropriate query in the Report_Open function, and also by passing in a WhereCondition on DoCmd.OpenReport, I was able to get the functionality I wanted. I had to convert the Access SQL syntax to SQL Server syntax, but that wasn't so hard.

I realized this morning that I might even be able to use the Report.Filter function and not have to do the conversion thing. I'll experiment. Either way, I can avoid actually running the whole dang query without filters.

My current way passes a WHERE clause to the server itself and is very fast, but possibly could break on weird syntax or allow SQL injection attacks.

The other way would involve binding the datasheet recordset to the report, after setting the report's filter and filteron properties.

I'll let you know.

And, by the way, the error I was getting was related to Access saving extended properties for queries (views, SPs, table functions) in SQL Server. Access was

1) trying to save column properties for a stored procedure, which is not allowed. A workaround is to use a table function or view instead of a stored procedure.

and 2) messing up, somehow, applying the saved filter to the datasheet on open. A workaround is to set the datasheet's Form.Filter property to "" on Form_Close, thus not saving it and thus preventing Access from trying to reapply it.