ACC: How to Test a User-Defined Filter in Filter By Form

Last reviewed: August 28, 1997
Article ID: Q154060
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how to test the validity of a user-defined filter when using the Filter By Form feature of Microsoft Access. The sample procedure demonstrates some of the validation techniques you can use to ensure that the filter you apply to the form returns records.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

In this example, if the filter you apply does not return any records, a message appears, and you return to the Filter By Form screen to retry the filter:

  1. Open the sample database Northwind.mdb.

  2. Open the Employees form in Design view.

  3. Set the OnApplyFilter property to the following [Event Procedure]:

    Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As _

              Integer)
              Me.TimerInterval = 100
           End Sub
    
    

  4. Set the OnTimer property to the following [Event Procedure]:

    Private Sub Form_Timer()

              Dim strfilter As String
              Me.TimerInterval = 0
              If IsNull(Me.Filter) Then Exit Sub
              strfilter = Me.Filter
              If Me.RecordsetClone.RecordCount = 0 Then
                  MsgBox "The selected filter:" & Chr(13) & _
                  strfilter & Chr(13) & _
                  "returns no records. Try a less restrictive search."
                  ' Form, Records, Filter, Filter By Form.
                  DoCmd.DoMenuItem 0, 5, 0, 0, acMenuVer70
              End If
           End Sub
    
    

  5. Save and open the form in Form view.

  6. On the Records menu, point to Filter, and then click Filter By Form.

  7. In the First Name field, select Laura.

  8. On the Filter menu, click Apply Filter/Sort. Note that the record for Laura Callahan is returned.

  9. On the Records menu, point to Filter, and then click Filter By Form.

  10. In the First Name field, type "Test" (without the quotation marks).

  11. On the Filter menu, click Apply Filter/Sort.

  12. Note that the following message appears, indicating the filter does not return records:

    The selected filter: ((Employees.FirstName="Test")) returns no records. Try a less restrictive search.

NOTE: To cancel out of the Filter By Form mode, remove all criteria and click Apply Filter/Sort on the Filter menu.

REFERENCES

For more information about OnFilter and OnApplyFilter, type "Filters and OnApplyFilter," in the Office Assistant, click Search, and then click to view the appropriate topic.


Additional query words: Validate FormFilter Count Query
Keywords : JetQryFiltr kbusage
Version : 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.