none
ApplyFilter usage RRS feed

  • Question

  • I want to be able to fill in certain fields on a form, click a button and then have the form results filtered based on the fields filled in. I coded this for one field but when I click the button, the Before Update event is firing and all my validations triggered (there are several required fields). How can I allow the ApplyFilter logic but bypass the Before Update event and yet I need the BeforeUpdate event when they do add a new record or update an existing one. Below is my code:

    Private Sub butFind_Click()
       On Error GoTo ER
       Dim strFilter As String
       strFilter = ""
    '  Build WHERE clause for filter
       If Nz(Me!CheckNumber, "") <> "" Then
          strFilter = "CheckNumber = '" & Me!CheckNumber & "'"
       End If
       If strFilter <> "" Then
          DoCmd.ApplyFilter , strFilter
       End If
       ' Old code:
       ' Screen.PreviousControl.SetFocus
       ' DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
       Exit Sub
    ER:
       MsgBox Err.Description, , "butFind_Click"
    End Sub

    Tuesday, May 2, 2017 2:51 PM

Answers

  • Thanks. I decided to use a separate form to collect the filtering choices.

    I'm going to use an existing form that is used to filter a report, so I need to make it do double-duty.

    I need to tell which of the two functions is operating, filtering or reporting. I can't use Openargs because that is already being used for another purpose.

    Is there a way to tell which form opened the current form since I could use that to distinguish between the two logic paths?

    Tuesday, May 2, 2017 7:11 PM

All replies

  • Hi,

    Just a stab in the dark but you might try using unbound textboxes for the filter values because by using bound textboxes, you are dirtying the form and thus activating the BeforeUpdate event.

    Just my 2 cents...

    Tuesday, May 2, 2017 2:55 PM
  • To use bound controls for entering filter criteria you must first move to any empty new record in the form.  Then, after inserting the values, in the code to execute the search, first build the string expression for the filter by stepping through the controls.  Before applying the filter, however,  you must undo the new record which you've just entered by calling the form's Undo method.  You will then be able to apply the filter without causing the form's BeforeUpdate event procedure to be executed.

    The above approach is risky, however, as it's all to easy for a user to save a specious record unwittingly, so it's more usual to use separate unbound controls to filter a form.  The following code is an a example for searching an address book form.  In the form the controls for entering the criteria are identified by setting the Tag property of each to SearchByMe

        Dim ctrl As Control
        Dim strFilter As String
        
        ' loop through form's Controls collection
        ' and build filter string expression
        ' for those controls not Null
        For Each ctrl In Me.Controls
            If ctrl.Tag = "SearchByMe" Then
                If Not IsNull(ctrl) Then
                    Select Case ctrl.Name
                        Case "cboLastName"
                        strFilter = strFilter & _
                            "And LastName = """ & ctrl & """ "
                        Case "cboCity"
                        strFilter = strFilter & _
                            "And CityID = " & ctrl & " "
                        Case "cboCounty"
                        strFilter = strFilter & _
                            "And CountyID = " & ctrl & " "
                        Case "cboPostCode"
                        strFilter = strFilter & _
                            "And PostCode = """ & ctrl & """ "
                    End Select
                End If
            End If
        Next ctrl
        
        With Me
            If Len(strFilter) > 0 Then
                ' remove leading 'And '
                strFilter = Mid(strFilter, 5)
                ' apply filter
                .Filter = strFilter
                .FilterOn = True
            Else
                'turn filter off
                .FilterOn = False
            End If
        End With

    The unbound controls can be placed in a bound form's header for instance.  Alternatively the filter criteria can be entered via an unbound dialogue form.  The above code is in fact adapted from such a form's module.  You'll find it demonstrated in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the original code is:

        Dim ctrl As Control
        Dim strFilter As String
        
        ' loop through form's Controls collection
        ' and build filter string expression
        ' for those controls not Null
        For Each ctrl In Me.Controls
            If ctrl.Tag = "SearchByMe" Then
                If Not IsNull(ctrl) Then
                    Select Case ctrl.Name
                        Case "cboLastName"
                        strFilter = strFilter & _
                            "And LastName = """ & ctrl & """ "
                        Case "cboCity"
                        strFilter = strFilter & _
                            "And CityID = " & ctrl & " "
                        Case "cboCounty"
                        strFilter = strFilter & _
                            "And CountyID = " & ctrl & " "
                        Case "cboPostCode"
                        strFilter = strFilter & _
                            "And PostCode = """ & ctrl & """ "
                    End Select
                End If
            End If
        Next ctrl
        
        With Forms!frmAddresses
            If Len(strFilter) > 0 Then
                ' remove leading 'And '
                strFilter = Mid(strFilter, 5)
                ' apply filter
                .Filter = strFilter
                .FilterOn = True
            Else
                'turn filter off
                .FilterOn = False
            End If
        End With

        ' close dialogue form
        DoCmd.Close acForm, Me.Name

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, May 2, 2017 4:41 PM Hyperlink inserted.
    Tuesday, May 2, 2017 4:06 PM
  • Thanks. I decided to use a separate form to collect the filtering choices.

    I'm going to use an existing form that is used to filter a report, so I need to make it do double-duty.

    I need to tell which of the two functions is operating, filtering or reporting. I can't use Openargs because that is already being used for another purpose.

    Is there a way to tell which form opened the current form since I could use that to distinguish between the two logic paths?

    Tuesday, May 2, 2017 7:11 PM
  • I can't use Openargs because that is already being used for another purpose.

    In fact you can.  Take a look at Args.zip in my same OneDrive folder.

    This little demo file illustrates the use of a module which allows you to pass multiple named arguments, amongst other things, to a form.


    Ken Sheridan, Stafford, England

    Tuesday, May 2, 2017 8:19 PM
  • Is there a way to tell which form opened the current form since I could use that to distinguish between the two logic paths?

    Hi ATGNWT,

    There are several ways:

    - You can concatenate several parts of informations using a separator for the OpenArgs argument. In the Open event of the form these information parts can be splitted again using that separator. This is sequence dependant.

    - You can use placeholders in the OpenArgs argument, to make it sequence independant: OpenArgs = "ID=12345/ITEM=Table/NAME=Persons/". A specialized function with argument "ID" will return "12345". You can even use XML for this purpose.

    - Use a "onetime" global that is set just before the opening of the form and asked for in the Open event.

    Imb.

    Tuesday, May 2, 2017 8:41 PM
  • I know it's possible to use OpenArgs separators, I've done that myself many times ... but I don't want to make any changes to the existing logic path, so I'm going to base it off of which form opened the current form. Thanks.
    Tuesday, May 2, 2017 8:56 PM
  • Has your issue been resolved?

    Did the suggestion from .theDBguy to use unbound text and demo project from Ken Sheridan work for you?

    In my option, I would suggest you try unbound textbox which is much easy and convenience to achieve.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 8, 2017 7:01 AM