Cascaded Form Filtering

[NeoPa]

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]

AccountID; Autonumber; PK
AccountCode; String
AccountName; String
CreationDate; Date/Time
AccountType; Numeric; 0==>Control (Internal Usage); 1==>Supplier; 2==>Customer
SalesThisYear; Numeric

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

CHAPTER 4 - FORM LAYOUT

Form Name=[frmAccount]

cboByAccountType; ComboBox; Unbound
txtByAccountName; TextBox; Unbound
txtBySalesLastYear; TextBox; Unbound
cboFromCreationDate; ComboBox; Unbound
cboToCreationDate; ComboBox; Unbound
txtAccountID; TextBox; Bound to [AccountID]
txtAccountCode; TextBox; Bound to [AccountCode]
txtAccountName; TextBox; Bound to [AccountName]
txtCreationDate; TextBox; Bound to [Creation]
cboAccountType; ComboBox; Bound to [AccountType]
txtSalesThisYear; TextBox; Bound to [SalesThisYear]

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

CHAPTER 5 - FORM MODULE

Option Compare Database
Option Explicit
 
Private Const conDateSource As String = "SELECT DISTINCT [CreationDate] " & _
                                        "FROM tblAccount " & _
                                        "ORDER BY [CreationDate]"
 
Private Sub cboByAccountType_AfterUpdate()
    Me.cboAccountType.DefaultValue = Nz(Me.cboByAccountType, "")
    Call Cascade
    Call ApplyFilter
End Sub
 
Private Sub txtByAccountName_AfterUpdate()
    Call Cascade
    Call ApplyFilter
End Sub
 
Private Sub txtBySalesThisYear_AfterUpdate()
    Me.txtSalesThisYear.DefaultValue = Nz(Me.txtBySalesThisYear, "")
    Call Cascade
    Call ApplyFilter
End Sub
 
Private Sub cboFromCreationDate_AfterUpdate()
    Dim strDefault As String
 
    With Me
        If IsNull(.cboFromCreationDate) Then
            strDefault = ""
        Else
            strDefault = "#" & Format(CDate(.cboFromCreationDate), _
                                      "m/d/yyyy") & "#"
        End If
        .txtCreationDate.DefaultValue = strDefault
    End With
    Call ApplyFilter
End Sub
 
Private Sub cboToCreationDate_AfterUpdate()
    Call ApplyFilter
End Sub
 
'Cascade filters the two unbound filter ComboBoxes cboFromCreationDate &
'cboToCreationDate by the values already entered/selected from the top level
'unbound controls in the Header Section (cboByAccountType; txtByAccountName &
'txtBySalesThisYear.  As cboFromCreationDate is always reset in here, the
'DelfaultValue for txtCreationDate must be also.
Private Sub Cascade()
    Dim strSQL As String, strFilter As String
 
    With Me
        .cboFromCreationDate = Null
        .cboToCreationDate = Null
        .txtCreationDate.DefaultValue = ""
        strSQL = conDateSource
        strFilter = GetFilter()
        If strFilter > "" Then _
            strSQL = Replace(strSQL, "ORDER", "WHERE " & strFilter & " ORDER")
        .cboFromCreationDate.RowSource = strSQL
        .cboToCreationDate.RowSource = strSQL & " DESC"
    End With
End Sub
 
'ApplyFilter applies new Filter from GetFilter().
Private Sub ApplyFilter()
    Dim strFilter As String, strOldFilter As String
 
    With Me
        strOldFilter = .Filter
        strFilter = GetFilter
        If strFilter <> strOldFilter Then
            .Filter = strFilter
            .FilterOn = (strFilter > "")
        End If
    End With
End Sub
 
'GetFilter returns the new filter depending on the values currently in
'the unbound controls in the Header Section (cboByAccountType; txtByAccountName;
'txtBySalesThisYear; cboFromCreationDate & cboToCreationDate.
Private Function GetFilter() As String
    Dim strFilter As String
 
    With Me
        'cboByAccountType - Numeric - Exact match
        If Not IsNull(.cboByAccountType) Then _
            strFilter = strFilter & " AND " & _
                        "([AccountType]=" & _
                        .cboByAccountType & ")"
        'txtByAccountName - Text - Like / Wildcards
        If Not IsNull(.txtByAccountName) Then _
            strFilter = strFilter & " AND " & _
                        "([AccountName] Like '*" & _
                        .txtByAccountName & "*')"
        'txtBySalesThisYear - Numeric - Greater or Equal
        If Not IsNull(.txtBySalesThisYear) Then _
            strFilter = strFilter & " AND " & _
                        "([SalesThisYear]>=" & _
                        .txtBySalesThisYear & ")"
        'cboFromCreationDate - Date - Between or Equal
        If Not IsNull(.cboFromCreationDate) Then
            strFilter = strFilter & " AND ([CreationDate]"
            If IsNull(.cboToCreationDate) Then
                strFilter = strFilter & "=#%F#)"
            Else
                strFilter = strFilter & " Between #%F# And #%T#)"
                strFilter = Replace(strFilter, _
                                    "%T", Format(CDate(.cboToCreationDate), _
                                                 "m/d/yyyy"))
            End If
            strFilter = Replace(strFilter, _
                                "%F", Format(CDate(.cboFromCreationDate), _
                                             "m/d/yyyy"))
        End If
'Debug.Print ".Filter = '" & strOldFilter & "' - ";
'Debug.Print "strFilter = '" & strFilter & " '"
        'Tidy up results and apply IF NECESSARY
        If strFilter > "" Then strFilter = Mid(strFilter, 6)
        GetFilter = strFilter
    End With
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 :

If strFilter > "" Then strFilter = Mid(strFilter, 6)
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 :

 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 :

 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 :

 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 :

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 :

 AND ([CreationDate]=#2/1/2007#)

However, if we then added "28 Feb 2007" to cboToCreationDate, then the resultant filter element would be :

 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.

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

[Mr Key]

Yep! nice job

[medube1]

Thanks guys that help me

[NeoPa]

Always pleased to hear it's helpful :-)