473,221 Members | 2,412 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,221 developers and data experts.

Cascaded Form Filtering

32,554 Expert Mod 16PB
Cascaded Form Filtering

CHAPTER 1 - TABLE OF CONTENTS (Including attached database)



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.



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


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]


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Const conDateSource As String = "SELECT DISTINCT [CreationDate] " & _
  5.                                         "FROM tblAccount " & _
  6.                                         "ORDER BY [CreationDate]"
  8. Private Sub cboByAccountType_AfterUpdate()
  9.     Me.cboAccountType.DefaultValue = Nz(Me.cboByAccountType, "")
  10.     Call Cascade
  11.     Call ApplyFilter
  12. End Sub
  14. Private Sub txtByAccountName_AfterUpdate()
  15.     Call Cascade
  16.     Call ApplyFilter
  17. End Sub
  19. Private Sub txtBySalesThisYear_AfterUpdate()
  20.     Me.txtSalesThisYear.DefaultValue = Nz(Me.txtBySalesThisYear, "")
  21.     Call Cascade
  22.     Call ApplyFilter
  23. End Sub
  25. Private Sub cboFromCreationDate_AfterUpdate()
  26.     Dim strDefault As String
  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
  40. Private Sub cboToCreationDate_AfterUpdate()
  41.     Call ApplyFilter
  42. End Sub
  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
  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
  65. 'ApplyFilter applies new Filter from GetFilter().
  66. Private Sub ApplyFilter()
  67.     Dim strFilter As String, strOldFilter As String
  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
  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
  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


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.

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.

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.

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".

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.



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.

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, 1341 views)
Aug 17 '10 #1
3 16849
Mr Key
132 100+
Yep! nice job
Oct 5 '10 #2
Thanks guys that help me
Aug 8 '13 #3
32,554 Expert Mod 16PB
Always pleased to hear it's helpful :-)
Aug 15 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: jfancy-Transport Canada | last post by:
Hi, I'm looking for an asp page to detect if there are any characters in my contact form that shouldn't be there. For example, if there is a "<" character, then this may mean there is html in my...
by: Dalan | last post by:
Okay, I have worked on this and then some, but cannot seem to crack it. So if someone can straighten my code out, or suggest a new approach, then I'm all ears. Here goes: I have two tables - one...
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the...
by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if...
by: Sean | last post by:
Greetings all, I am attempting to make a form that will filter through several tables that (I believe) have refretial integrity. I am pulling data from several tables into the form and i would...
by: glamster7 | last post by:
Hi Folks, I have a form frm.viewbookings which shows in the header the labels Stylist1 through to Stylist 7 with the stylist names underneath them. In total for the set of data I have there are...
by: seangibson | last post by:
Sorry, I've been posting alot of questions regarding cascaded combo boxes here but each time I solve one problem I seem to be faced with a new. This time here's the problem: I've got the combo...
by: Lyn | last post by:
I have a problem using the form .Filter and .FilterOn properties which causes Access to crash (as detailed in a separate post). The form operates in continuous mode, displaying matching records...
by: DeZZar | last post by:
Hi all, Unfortunately I am quite a novice with Access!! I've created a number of data bases for my work however becuase my skills are limited to really built in functionality and wizards my...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.