423,350 Members | 2,519 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Example Filtering on a Form.

NeoPa
Expert Mod 15k+
P: 31,007
CHAPTER 1 - TABLE OF CONTENTS (Including attached database)
CHAPTER 2 - INTRODUCTION
CHAPTER 3 - TABLE LAYOUT
CHAPTER 4 - FORM LAYOUT
CHAPTER 5 - FORM MODULE
CHAPTER 6 - CODE DISCUSSION (FILTER CONTROLS)
CHAPTER 7 - CODE DISCUSSION (THE REST)

--------------------------------------------------------------------------------

CHAPTER 2 - INTRODUCTION

This is a basic example and works with some unbound selection items on an otherwise bound form with no complicated SubForms or anything.
In this example I have a Form called frmAccount bound to table tblAccount.
The form has all of the table fields shown as (bound) controls (Mainly TextBox - name preceded by 'txt', but also a ComboBox - cboAccountType). It also has three extra controls to enable the operator to select items by AccountCode, CreationDate & AccountType. These fields have been chosen for illustrative purposes only and are not supposed to reflect any real-world situation accurately.
To avoid confusion, it is a good idea to select names that have meaning, reflecting their contents, while avoiding special characters (including spaces) and names which are already in use for something else. A good example of the latter is 'Date'. Using this as a field or control name is a problem in waiting.

--------------------------------------------------------------------------------

CHAPTER 3 - TABLE LAYOUT
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblAccount
  2. AccountID; Autonumber; PK
  3. AccountCode; String
  4. AccountName; String
  5. CreationDate; Date/Time
  6. AccountType; Numeric; 0==>Control (Internal Usage); 1==>Supplier; 2==>Customer
  7. SalesThisYear; Numeric
--------------------------------------------------------------------------------

CHAPTER 4 - FORM LAYOUT
Expand|Select|Wrap|Line Numbers
  1. Form Name=frmAccount
  2. txtFindCode; Unbound
  3. txtFindCreationDate; Unbound
  4. cboFindAccountType; Unbound
  5. txtAccountID; Bound to tblAccount.AccountID
  6. txtAccountCode; Bound to tblAccount.AccountCode
  7. txtAccountName; Bound to tblAccount.AccountName
  8. txtCreationDate; Bound to tblAccount.Creation
  9. cboAccountType; Bound to tblAccount.AccountType
  10. txtSalesThisYear; Bound to tblAccount.SalesThisYear
--------------------------------------------------------------------------------

CHAPTER 5 - FORM MODULE

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub txtFindAccountCode_AfterUpdate()
  5.     Call CheckFilter
  6. End Sub
  7.  
  8. Private Sub txtFindCreationDate_AfterUpdate()
  9.     Me!txtFindCreationDate = IIf(IsDate(Me!txtFindCreationDate), _
  10.                                  Format(Me!txtFindCreationDate, "d mmm yyyy"), _
  11.                                  "")
  12.     Call CheckFilter
  13. End Sub
  14.  
  15. Private Sub cboFindAccountType_AfterUpdate()
  16.     Call CheckFilter
  17. End Sub
  18.  
  19. 'CheckFilter produces the new Filter depending on the values currently in
  20. 'txtFindAccountCode, txtFindCreationDate & cboFindAccountType.
  21. Private Sub CheckFilter()
  22.     Dim strFilter As String, strOldFilter As String
  23.  
  24.     strOldFilter = Me.Filter
  25.     'txtFindAccountCode - Text
  26.     If Me!txtFindAccountCode > "" Then _
  27.         strFilter = strFilter & _
  28.                     " AND ([AccountCode] Like '" & _
  29.                     Me!txtFindAccountCode & "*')"
  30.     'txtFindCreationDate - Date
  31.     If Me!txtFindCreationDate > "" Then _
  32.         strFilter = strFilter & _
  33.                     " AND ([CreationDate]=" & _
  34.                     Format(CDate(Me!txtFindCreationDate), _
  35.                            "\#m/d/yyyy\#") & ")"
  36.     'cboFindAccountType - Numeric
  37.     If Me!cboFindAccountType > "" Then _
  38.         strFilter = strFilter & _
  39.                     " AND ([AccountType]=" & _
  40.                     Me!cboFindAccountType & ")"
  41.     'Debug.Print ".Filter = '" & strOldFilter & "' - ";
  42.     'Debug.Print "strFilter = '" & strFilter & " '"
  43.     'Tidy up results and apply IF NECESSARY
  44.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  45.     If strFilter <> strOldFilter Then
  46.         Me.Filter = strFilter
  47.         Me.FilterOn = (strFilter > "")
  48.     End If
  49. End Sub
--------------------------------------------------------------------------------

CHAPTER 6 - CODE DISCUSSION (FILTER CONTROLS)

Each of the controls' filter part code starts with it checking that it is not empty. Any value (Text, Date or Number) will return true when checked as greater than "".
Each starts with " AND " but this is stripped from the first one found later before being applied.
Each check built is surrounded by parentheses () to ensure the logic is not confused with other checks.

TEXT
The delimiter for text literals is (').
The Text check is done as a 'Like' construct as the Account Code would be likely to be unique so = would not mix well with the others as a composite check. We start with the string " AND ([AccountCode] Like '" then add (string append) the value from our txtFindAccountCode control then (lastly) add the string "*')". Using txtFindAccountCode="ABC", this results in :
Expand|Select|Wrap|Line Numbers
  1.  AND ([AccountCode] Like 'CUS*')
This will find any record where the [AccountCode] starts with "CUS".

DATE
The delimiter for date literals is (#).
Firstly, to describe the manipulation of the txtFindCreationDate control to return a version we can usefully pass to SQL. In the code it says 'Format(CDate(Me!txtFindCreationDate),"\#m/d/yyyy\#") & ")"'. As we've used a TextBox to enter a date into, we have little control of what the operator enters. They may enter "1 February 2007" or they may enter "1/2/2007" in England or "2/1/2007" in the USA. Whatever is entered, CDate() will convert, using the local settings, into a valid Date/Time value. SQL requires that any date literal be unambiguous but insists that the USA format of short date be used (m/d/y) rather than the local one whichever that may be. Read (Literal DateTimes and Their Delimiters (#).) for a more detailed explanation.
So, we start with the string " AND ([CreationDate]=" then add (string append) the value from our txtFindCreationDate control but passed through a couple of functions first (explained above) then (lastly) add the string ")". Using txtFindCreationDate="1 Feb 2007", this results in :
Expand|Select|Wrap|Line Numbers
  1.  AND ([CreationDate]=#2/1/2007#)
This will find any record where the [CreationDate] is 1 February 2007.

NUMBER
There is no delimiter required for numeric literals.
We start with the string " AND ([AccountType]=" then add (string append) the value from our txtFindAccountType control then (lastly) add the string ")". Using txtFindAccountType="1", this results in :
Expand|Select|Wrap|Line Numbers
  1.  AND ([AccountType]=1)
This will find any record where the [AccountType] is 1 (Customer).

--------------------------------------------------------------------------------

CHAPTER 7 - CODE DISCUSSION (THE REST)

AFTERUPDATE EVENT PROCEDURES
The first three procedures all call the same subroutine (CheckFilter) as the whole filter needs to be rebuilt whenever any of its constituent parts is changed.
To show consistently (not necessary for the logic) we check the data entered in txtFindCreationDate. If it is a valid date then we format it as "d mmm yyyy". Otherwise it is reset.
We'll go through each control's part of the filter in detail but firstly I've noticed that many people have problems distinguishing what is processed by VBA from what is passed to and processed by the SQL engine (interpreter). It may help you to understand if you uncomment the 'Debug.Print lines and view the results after selecting a value or some values from the 'Find' controls. This comes between the VBA processing and the SQL so it may help to see it at that stage.

APPLYING THE FILTER
Firstly, after stripping the first " AND " from the start, we check that the filter is actually different from the current one.
If it is, we need to set the filter and we also determine if filters should be used (.FilterOn) depending on whether or not strFilter is blank.

--------------------------------------------------------------------------------
Attached Files
File Type: zip FormFiltering.Zip (16.3 KB, 8503 views)
Jan 19 '07 #1
Share this Article
Share on Google+
2 Comments


NeoPa
Expert Mod 15k+
P: 31,007
A related article can be found at Cascading Combo/List Boxes.
May 22 '08 #2

NeoPa
Expert Mod 15k+
P: 31,007
A fuller version of this which includes cascading filter controls can be found at Cascaded Form Filtering.
Aug 17 '10 #3