Introduction.
Normally, we use filter settings on Forms or Reports to extract records based on specific conditions. This can be accomplished in several ways—for example:
Adding a WHERE condition (without the keyword WHERE) in the parameter setting of the OpenForm macro action.
Specifying a condition in the parameter of the ApplyFilter action in a macro.
Supplying criteria in the DoCmd.OpenForm method:
DoCmd.OpenForm "myForm", acNormal, , "EmployeeID Between 5 AND 10"
Using Filter by Selection directly on a form.
Setting a Query as the form’s record source.
What many developers overlook, however, is that the Filter property of a table itself can also be used to limit records when the table is opened in Datasheet View.
For instance, if you are reviewing someone else’s project and notice that a table displays only a few records in Datasheet View—while you are told the table should contain hundreds—you may wonder what happened to the rest. In such cases, the hidden culprit might be an active Table Filter.
Setting a Filter on a Table.
We will now explore how this filtering trick works on a Table, for a change. If you have ever applied a filter condition directly on a Form, then you already know enough to do the same with a table—it works almost identically.
To try this out, we need some ready-made data. The Northwind sample database is an excellent choice, but you may also experiment with any table from your own database.
Import the Order Details Table from the Northwind sample database.
Open the Table in the Design view.
Press F4 to display the Property Sheet of the Table.
Table Property Sheet View.Find the Filter Property and type [Order ID] Between 35 AND 45 into the property (in Access 2007). For earlier Access Versions, type [OrderID] between 10249 AND 10251.
Set the Filter On Load property value to Yes.
Save and close the Table Structure.
Open the Table in Datasheet View to see the Filter in action.
The output records displayed will be only those with Order IDs between 35 and 45. If you design a Quick Form or Quick Report from this table, the Record Source property will be set automatically. It will include an SQL SELECT statement containing the WHERE condition derived from the table’s Filter property.
However, keep in mind that if you later modify the filter condition on the table, the change will not be reflected automatically in the Record Source SQL of the Form or Report. Once created, the Form or Report retains the original filter condition unless you manually update it. Automating through VBA.
Like most features in Microsoft Access, you can automate this process with VBA to change the filter criteria at the click of a button. However, there is a small catch, which I’ll explain shortly, so that you understand why it’s important. After all, we’re talking about setting a simple filter condition on the Filter property of a table’s structure.
There are two ways to address the Filter property of a table in VBA.
-
Front-door approach: Accessing the property through the path
TableDef.Properties("Filter"). -
Backdoor approach: Accessing it via
Containers("Tables").Documents("TableName").Properties("Filter").
As you can see, the second method is more elaborate, which is why I refer to it as the backdoor method. We’ll try out examples of both approaches.
The second approach becomes especially useful when working with Forms or Reports—for example, when generating a list of all Forms or Reports, or when you want to change the name of a Form. In an earlier blog post, I demonstrated how to create a user-defined (custom) property to store values, such as the last record you worked on, so that the Form can reopen at that record and allow you to continue seamlessly. [Click here to learn more.]
Example-1:
- Copy and Paste the following VBA Code into a Standard Module of your Database.
Public Function TableFilter1(ByVal OrderStart As Long, ByVal OrderEnd As Long) Dim db As Database Dim Tbldef As TableDef Set db = CurrentDb Set Tbldef = db.TableDefs("Order Details") Tbldef.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd Tbldef.Properties("FilterOnLoad").Value = True db.TableDefs.Refresh End Function Run the above sample Code from the Debug Window or from a Command Button Click Event Procedure, like the sample run given below:
TableFilter1 35,45
NB: The above code and sample run are demonstrated using the Order Details table in Access 2007.
If you are working with an earlier version of Access, make the following adjustments:
Change the field name from [Order ID] to [OrderID] (without the space).
In the sample run, use:
instead of the values
35, 45for the OrderID range.If you have not tried out the manual filter method explained above or removed the filter criteria setting of the Filter Property then you will run into problems with the above program reporting an Error message stating that the Filter Property not found.
When you implement the VBA method see that an initial criteria setting is set in the Filter property of the Table. Without the criteria setting the Filter Property will not be visible in VBA.
Example-2:
Copy and paste the following VBA Code into the Standard VBA Module and run the Code in the same way as Example-1 with a different set of OrderIDs:
Public Function TableFilter2(ByVal OrderStart As Long, ByVal OrderEnd As Long)
Dim db As Database, ctr As Container, doc As Document
Set db = CurrentDb
Set ctr = db.Containers("Tables")
Set doc = ctr.Documents("Order Details")
doc.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
doc.Properties("FilterOnLoad").Value = True
doc.Properties.Refresh
End Function
I have applied the filter on table in MS access directory. It help me to work more faster.
ReplyDeleteIts is helpful for records on specific conditions. I used it that is nice.
ReplyDelete