473,217 Members | 1,979 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,217 developers and data experts.

Example Filtering on a Form.

NeoPa
32,554 Expert Mod 16PB
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, 9095 views)
Jan 19 '07 #1
2 58918
NeoPa
32,554 Expert Mod 16PB
A related article can be found at Cascading Combo/List Boxes.
May 22 '08 #2
NeoPa
32,554 Expert Mod 16PB
A fuller version of this which includes cascading filter controls can be found at Cascaded Form Filtering.
Aug 17 '10 #3

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

Similar topics

3
by: Aionius | last post by:
Good day to all. I have this problem in JavaScript. I have a form (textbox) which accepts a series of numbers in format 9999-9999-9. Now i want to filter all inputs to the textbox. let's say...
7
by: Nathan Bloomfield | last post by:
Hi All, I am hoping someone out there will be kind enough to find out where my code is going wrong. The current code is inefficiant but hopefully it will convey the data I require to be...
2
by: Aravind | last post by:
Hi folks. I have a form, frmHistory, which has 4 command buttons: Sort Title (cmdSortTitle), Sort Name (cmdSortName), Due Today (cmdDueToday), and Due List (cmdDueList). Sort Title and Sort...
1
by: Aravind | last post by:
Hi folks. My database project has the following: -------------------------------------------------------------------------------- tables: Member: MemName, MemNo , MemType, CourseFaculty...
2
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...
0
by: adam | last post by:
Hi there! I need any example of form which is wrote in C#. This form has to add client's data for a database. My email adabi@poczta.onet.pl TIA Adam
3
by: Liddle Feesh | last post by:
Hi, I have a table named "Person" in a MSDE (2000) database, and am building a search form comprising of search fields on the top section (forename, surname, telephonenumber, etc) which all...
4
by: Dave | last post by:
I am having difficulty filtering a form as the user types in a onchange event here is my code strFilter = cboCriteria.Value & " LIKE '" & Me!txtCriteria.text & "*" & "'" If Len(strFilter ) 0...
1
by: luvac | last post by:
hi, PLZ send me example of form connection to MS access.... with VB .net.....
0
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...
3
isladogs
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...
0
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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
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...
0
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...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.