Access 2010 - Error Handling Filter Subform

Question

how can I disable the filter of a Datagridview, using VBA, if after filtering there is no record?

Here my first try:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox ("Kein Datensatz gefunden. Filter wird entfernt.")
        Me.Form.FilterOn = False
        Me.Form.Requery
    End If
End Sub

Here is the second try:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone
    rs.Filter = Replace(Me.Filter, "[Tabelle1].", "")
    Set rs = rs.OpenRecordset()

    If rs.EOF Then
        Cancel = True
    End If
End Sub

In the second code I get the error 3061. The first code "works", but is finally not want I need.

Because:

I have 3 Forms. MAIN, Sub1 and Sub2.

In the MAIN are Sub1 , Sub2 and a single TextBox. The TextBox is call "psoudoID". Sub1 is a normal Form to show the details of the different recordsets and is placed on the top of the MAIN. Below Sub1 is Sub2. Sub2 is a Datagridview. When the user is clicking on a recordset on Sub2, the ID from Sub2 "goes" to the psoudoID and from there to Sub1. You understand?

Now is the problem, that the user can filter every column in Datagrid to find the recordset here needs and show all details above. But when the datagrid is empty after filtering , so Sub2 can't give an ID to the psoudoID and so on Sub1 is not displayed in MAIN much longer. The screen is empty on that place. By clicking the Filter-Button in grid, Sub1 is on the screen again.

I want to disable the filter by clicking "Ok" of a MsgBox or automatically and not by clicking the Filter-Button on gridview.

I hope you can understand my discribtion. And sorry for my bad English :-)

THX.

Vegeta

Which row in the first attempt produces the error 3061? Is there a reason you're setting Me.FilterOn to False or modifying the filter instead of setting Cancel to True? (Cancel cancels the current filter action and reverts to the previous filter if any, while Me.FilterOn = False turns the filter off, Cancel seems appropriate for what you want) — Erik A
The first code works. But I can't "fire" it at the right place. The error 3061 comes on the second code. At the replace. — Vegeta_77
Try replacing Set rs = rs.OpenRecordset() with Dim rsf As Recordset_ Set rsf = rs.OpenRecordSet. Does that help? — Erik A
Does rs.Filter = Replace(CStr(Nz(Me.Filter, "")), "[Tabelle1].", "") help? That way you make sure Replace gets 3 strings and no NULLs. — Erik A

Erik A Erik A · Accepted Answer · 2017-05-26T09:57:34

I believe you're having problems with filters being Null, and with using Me.Filter before the filter is actually applied. Also, re-assigning an object to a property/member of itself has caused problems for me before, so I try to avoid doing that.

The following should work:

Dim strOldFilter As String
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    Me.TimerInterval = 50
    strOldFilter = Nz(Me.Filter, "")
End Sub

Private Sub Form_Timer
        Me.TimerInterval = 0
        If Me.Filter = strOldFilter Then Exit Sub
        Dim rs As DAO.Recordset
        Dim strFilter As String
        strFilter = Nz(Me.Filter, "")
        If strFilter = "" Then
            'Handle this specific scenario
             Exit Sub
        End if
        Set rs = Me.RecordsetClone
        rs.Filter = Replace(strFilter, "[Tabelle1].", "")
        Dim rsf as DAO.Recordset
        Set rsf = rs.OpenRecordset

        If rsf.EOF Then
            Me.Filter = strOldFilter
        End If
End Sub

Note the potential to cause infinite loops that repeat every 50 msec if you change a filter resulting in 0 records to another filter resulting in 0 records (but you shouldn't be able to have a filter resulting in 0 records if the function works)

Access 2010 - Error Handling Filter Subform

1 Answers