By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,015 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Cascaded Form Filtering

NeoPa
Expert Mod 15k+
P: 31,277
Cascaded Form Filtering

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

The first thing I need to explain about this exercise is that, while I have tried to use data that has some meaning, this is fundamentally an exercise to illustrate the various concepts usable in the various scenarios that require filtering and cascaded filtering in particular.

This is a fairly basic example and works with some unbound selection controls in the Header Section of an otherwise bound form. No SubForms were used or abused in the creation of this exercise.

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 four extra (unbound) controls to enable the operator to select items by AccountName (or part thereof), CreationDate and SalesThisYear. 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

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

CHAPTER 4 - FORM LAYOUT

Form Name=[frmAccount]
Expand|Select|Wrap|Line Numbers
  1. cboByAccountType; ComboBox; Unbound
  2. txtByAccountName; TextBox; Unbound
  3. txtBySalesLastYear; TextBox; Unbound
  4. cboFromCreationDate; ComboBox; Unbound
  5. cboToCreationDate; ComboBox; Unbound
  6. txtAccountID; TextBox; Bound to [AccountID]
  7. txtAccountCode; TextBox; Bound to [AccountCode]
  8. txtAccountName; TextBox; Bound to [AccountName]
  9. txtCreationDate; TextBox; Bound to [Creation]
  10. cboAccountType; ComboBox; Bound to [AccountType]
  11. txtSalesThisYear; TextBox; Bound to [SalesThisYear]
--------------------------------------------------------------------------------

CHAPTER 5 - FORM MODULE

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Const conDateSource As String = "SELECT DISTINCT [CreationDate] " & _
  5.                                         "FROM tblAccount " & _
  6.                                         "ORDER BY [CreationDate]"
  7.  
  8. Private Sub cboByAccountType_AfterUpdate()
  9.     Me.cboAccountType.DefaultValue = Nz(Me.cboByAccountType, "")
  10.     Call Cascade
  11.     Call ApplyFilter
  12. End Sub
  13.  
  14. Private Sub txtByAccountName_AfterUpdate()
  15.     Call Cascade
  16.     Call ApplyFilter
  17. End Sub
  18.  
  19. Private Sub txtBySalesThisYear_AfterUpdate()
  20.     Me.txtSalesThisYear.DefaultValue = Nz(Me.txtBySalesThisYear, "")
  21.     Call Cascade
  22.     Call ApplyFilter
  23. End Sub
  24.  
  25. Private Sub cboFromCreationDate_AfterUpdate()
  26.     Dim strDefault As String
  27.  
  28.     With Me
  29.         If IsNull(.cboFromCreationDate) Then
  30.             strDefault = ""
  31.         Else
  32.             strDefault = "#" & Format(CDate(.cboFromCreationDate), _
  33.                                       "m/d/yyyy") & "#"
  34.         End If
  35.         .txtCreationDate.DefaultValue = strDefault
  36.     End With
  37.     Call ApplyFilter
  38. End Sub
  39.  
  40. Private Sub cboToCreationDate_AfterUpdate()
  41.     Call ApplyFilter
  42. End Sub
  43.  
  44. 'Cascade filters the two unbound filter ComboBoxes cboFromCreationDate &
  45. 'cboToCreationDate by the values already entered/selected from the top level
  46. 'unbound controls in the Header Section (cboByAccountType; txtByAccountName &
  47. 'txtBySalesThisYear.  As cboFromCreationDate is always reset in here, the
  48. 'DelfaultValue for txtCreationDate must be also.
  49. Private Sub Cascade()
  50.     Dim strSQL As String, strFilter As String
  51.  
  52.     With Me
  53.         .cboFromCreationDate = Null
  54.         .cboToCreationDate = Null
  55.         .txtCreationDate.DefaultValue = ""
  56.         strSQL = conDateSource
  57.         strFilter = GetFilter()
  58.         If strFilter > "" Then _
  59.             strSQL = Replace(strSQL, "ORDER", "WHERE " & strFilter & " ORDER")
  60.         .cboFromCreationDate.RowSource = strSQL
  61.         .cboToCreationDate.RowSource = strSQL & " DESC"
  62.     End With
  63. End Sub
  64.  
  65. 'ApplyFilter applies new Filter from GetFilter().
  66. Private Sub ApplyFilter()
  67.     Dim strFilter As String, strOldFilter As String
  68.  
  69.     With Me
  70.         strOldFilter = .Filter
  71.         strFilter = GetFilter
  72.         If strFilter <> strOldFilter Then
  73.             .Filter = strFilter
  74.             .FilterOn = (strFilter > "")
  75.         End If
  76.     End With
  77. End Sub
  78.  
  79. 'GetFilter returns the new filter depending on the values currently in
  80. 'the unbound controls in the Header Section (cboByAccountType; txtByAccountName;
  81. 'txtBySalesThisYear; cboFromCreationDate & cboToCreationDate.
  82. Private Function GetFilter() As String
  83.     Dim strFilter As String
  84.  
  85.     With Me
  86.         'cboByAccountType - Numeric - Exact match
  87.         If Not IsNull(.cboByAccountType) Then _
  88.             strFilter = strFilter & " AND " & _
  89.                         "([AccountType]=" & _
  90.                         .cboByAccountType & ")"
  91.         'txtByAccountName - Text - Like / Wildcards
  92.         If Not IsNull(.txtByAccountName) Then _
  93.             strFilter = strFilter & " AND " & _
  94.                         "([AccountName] Like '*" & _
  95.                         .txtByAccountName & "*')"
  96.         'txtBySalesThisYear - Numeric - Greater or Equal
  97.         If Not IsNull(.txtBySalesThisYear) Then _
  98.             strFilter = strFilter & " AND " & _
  99.                         "([SalesThisYear]>=" & _
  100.                         .txtBySalesThisYear & ")"
  101.         'cboFromCreationDate - Date - Between or Equal
  102.         If Not IsNull(.cboFromCreationDate) Then
  103.             strFilter = strFilter & " AND ([CreationDate]"
  104.             If IsNull(.cboToCreationDate) Then
  105.                 strFilter = strFilter & "=#%F#)"
  106.             Else
  107.                 strFilter = strFilter & " Between #%F# And #%T#)"
  108.                 strFilter = Replace(strFilter, _
  109.                                     "%T", Format(CDate(.cboToCreationDate), _
  110.                                                  "m/d/yyyy"))
  111.             End If
  112.             strFilter = Replace(strFilter, _
  113.                                 "%F", Format(CDate(.cboFromCreationDate), _
  114.                                              "m/d/yyyy"))
  115.         End If
  116. 'Debug.Print ".Filter = '" & strOldFilter & "' - ";
  117. 'Debug.Print "strFilter = '" & strFilter & " '"
  118.         'Tidy up results and apply IF NECESSARY
  119.         If strFilter > "" Then strFilter = Mid(strFilter, 6)
  120.         GetFilter = strFilter
  121.     End With
  122. End Function
--------------------------------------------------------------------------------

CHAPTER 6 - CODE DISCUSSION (FILTER CONTROLS)

Each of the controls' filter part code starts with it checking that it is not empty. Any control will be Null when there is no data within.
Each starts with " AND " but this is stripped later, from the first one found, before being applied.
Each check built is surrounded by parentheses () to ensure the logic is not confused with other checks.

Each of the unbound filter controls has an AfterUpdate event procedure associated with it. There are fundamentally three tasks than can be required for each, though not all three are required for all of these controls :
  1. Set the relevant .DefaultValue property.

    Only cboByAccountType, txtBySalesThisYear and cboFromCreationDate are required to do this.
  2. Cascade the selected value to reflect in the cboFromCreationDate and cboToCreationDate ComboBox lists.

    Only cboByAccountType, txtByAccountName and txtBySalesThisYear are required to do this.
  3. Filter the form to reflect all the filter controls.

    All controls do this.

GENERAL
All controls, when they add anything to the ongoing filter string at all, always start with the string " AND ". This is required between each element of the filter string to separate them and ensure all are taken into consideration. This will, of course, leave a single extraneous one at the beginning. It could easily have been arranged to have the extra one at the end instead, but the code to strip it from the start of a string is more straightforward than the code to strip it from the end as that requires determining first the length of the string.

To illustrate, I include below the two alternative lines of code :
Expand|Select|Wrap|Line Numbers
  1. If strFilter > "" Then strFilter = Mid(strFilter, 6)
  2. If strFilter > "" Then strFilter = Left(strFilter, Len(strFilter) - 5)
As is clear, line #1 is shorter and involves less function calls and arithmetic than line #2. Line #2 is probably closer to a human way of thinking though, so you will probably see many examples of it around.


NUMBER
There is no delimiter required for numeric literals.

We have two number controls. One handles an exact match, and the other a Greater Than Or Equal To match.

Exact Match - cboByAccountType
We start with the string " AND ([AccountType]=" then add (string append) the value from our cboByAccountType control then (lastly) add the string ")". Using cboByAccountType=1, this results in :
Expand|Select|Wrap|Line Numbers
  1.  AND ([AccountType]=1)
This will find all records where the [AccountType] is 1 (Customer).

Greater Than Or Equal To Match - txtBySalesThisYear
We start with the string " AND ([SalesThisYear]>=" then add (string append) the value from our txtBySalesThisYear control then (lastly) add the string ")". Using txtBySalesThisYear=1,000, this results in :
Expand|Select|Wrap|Line Numbers
  1.  AND ([SalesThisYear]>=1000)
This will find all records with SalesThisYear >= 1,000.

TEXT
The delimiter for text literals is (') - See Quotes (') and Double-Quotes (") - Where and When to use them for more on this.

The Text filtering is done as a 'Like' construct as the Account Name would be likely to be unique (An exact match would be fairly pointless in this context). We start with the string " AND ([AccountName] Like '*" then add (string append) the value from our txtByAccountName control then (lastly) add the string "*')". Using txtByAccountName="pub", this results in :
Expand|Select|Wrap|Line Numbers
  1.  AND ([AccountName] Like '*pub*')
This will find any record where the [AccountName] contains "pub".

DATE
The delimiter for date literals is (#) - See Literal DateTimes and Their Delimiters (#) for more on this.

Firstly, to describe the manipulation of the cboFromCreationDate control to return a version we can usefully pass to SQL. In the code it says :
Expand|Select|Wrap|Line Numbers
  1. Format(CDate(.cboFromCreationDate), "m/d/yyyy"))
The operator can enter the data in any format they wish. As long as it's a valid date we don't much care. That said, we do need the data to be properly interpreted by our code. The CDate() call ensures the data is first converted into a valid date value. This will use the local settings to interpret what is esentially string data. Next, the Format() call converts this date into a formatted string appropriate for SQL. That is specifically the m/d/y format.

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. See linked article above for more details on this.

As we have the option of entering a To Date also, the filtering here has to determine if the operator has entered a To value or not. If they have, it uses the Between #X# And #Y# construct. Otherwise it simply uses =#X#.

So, we start with the string " AND ([CreationDate]" then we determine if a To Date is available and go from there.

If the To date is available we add (string append) the string " Between #%F# To #%T#)". Otherwise we simply add "=#%F#)" instead. We will get to replacing %F and %T from this string with the actual values from the controls next.

Using the Replace() function, we strip out these replaceable tokens (%F and %T) and replace them with the values from the controls. If a token to replace isn't found in the string, then no change is made to the string (The result of the function is the same as the main string passed).

So, if we just entered "1 Feb 2007" in cboFromCreationDate, then the resultant filter element would be :
Expand|Select|Wrap|Line Numbers
  1.  AND ([CreationDate]=#2/1/2007#)
However, if we then added "28 Feb 2007" to cboToCreationDate, then the resultant filter element would be :
Expand|Select|Wrap|Line Numbers
  1.  AND ([CreationDate] Between #2/1/2007# And #2/28/2007#)
Either way, we get exactly what we should expect.

If there are any lingering doubts or confusion about how all this works, I strongly suggest you remove the comment marks before the Debug lines near the bottom of the GetFilter() function procedure and review what gets printed into the Immediate pane of the VBA IDE window.

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

CHAPTER 7 - CODE DISCUSSION (THE REST)

AFTERUPDATE EVENT PROCEDURES
Both the Cascade() and ApplyFilter() subroutines make use of the GetFilter() function.

Cascade() simply ensures both of the date filter controls (that are cascaded to) are reset before continuing. It then calls GetFilter() to determine the value of the overall filter string, which it then applies to the .RowSource properties of both the Date controls. It uses the Replace() function to insert the optional WHERE clause into the initial SQL found in conDateSQL.

ApplyFilter() is likewise pretty simple. It checks the proposed filter from GetFilter() and compares it with the existing filter. Only if they're different does it apply the new one. Refer to APPLYING THE FILTER below for details on how this is done.

GetFilter() is mainly just as described in detail in Chapter 6 above (CODE DISCUSSION (FILTER CONTROLS)).

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, 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 CascadedFormFiltering.Zip (22.9 KB, 1153 views)
Aug 17 '10 #1
Share this Article
Share on Google+
3 Comments


100+
P: 131
Yep! nice job
Oct 5 '10 #2

P: 6
Thanks guys that help me
Aug 8 '13 #3

NeoPa
Expert Mod 15k+
P: 31,277
Always pleased to hear it's helpful :-)
Aug 15 '13 #4