IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, June 2006, updated March 2007.
A flexible search form provides several boxes where a user can enter criteria. An efficient search form creates the criteria from only those boxes where the user enters a value.
Download the search database (23KB zipped).
It illustrates how to use:
Users sometimes try to provide optional criteria like this:
Like "*" & [Forms].[Form1].[Text0] & "*"
That approach is inefficient and unsuitable for non-Text fields. It is also flawed, as it fails to return all records when the text box is left blank (i.e. the Nulls are excluded.)
The sample database uses a continuous form bound to the table or query that provides the fields for the results:
Click the Filter button to show the results. Click Remove Filter to clear all criteria, and show all records.
Text boxes in the Detail section have the Locked property set, so users do not change the data. (You cannot use the form's AllowEdits property, as this prevents you using the unbound criteria boxes as well.)
Only the records that meet all criteria are returned.
Read this section in conjunction with the sample code in the search form, or view the code in a separate window.
The Click event procedure for cmdFilter looks at each unbound control in turn. If not null, it adds to the string strWhere. Each one ends in " AND ", so the next one can be added as well. The trailing " AND " is removed before applying the string to the Filter of the form.
Different field types require different delimiters, so the code demonstrates searching each type of field.
City is a Text type field in tblClient, so we add the quotes around the search value. If those quotes look strange, see Quotation marks within quotes.
MainName is also a Text field, so again we add the quotes. This time we used the Like operator with the * wildcard, to show how to search for a value anywhere in a field. The Like operator gives a slower search: a leading wildcard, in particular, prevents JET from using an index.
LevelID is a Number type field. Do not add the quote marks around values matched to a Number or Currency field.
IsCorporate is a Yes/No type field. If True, the client is a company (corporate entity); if False, the client is a person (individual.) The MainName field has company names for corporate clients, and surnames for individuals. When searching a Yes/No field, you need to give the user three choices: a) just Yes; b) just No; c) Yes or No (all). An unbound check box can do that if you set its TripleState property to Yes, but the interface is confusing, so we use a combo where the choices are obvious. The combo's Value List uses -1 for corporate; 0 for person, and 1 for both. The code therefore tests if the value is -1 or 0, and adds to the Where string. We add nothing to the filter sting for other cases (1 or Null.)
EnteredOn is a Date/Time type field, so the code adds the # delimiter around the date value in the string, and formats it with the native JET format to avoid problems with international dates. We provide From and To boxes so the user can specify a date range. If the field contains a time component, your criteria must use "less than the next day" so the final date is included. Results:
|Date range entry||Result|
|From date only||All records from that date onwards|
|To date only||All record up to and including that date|
|Both From and To dates||Only records between the two dates (both inclusive)|
|Neither From nor To||No filter applied on this field|
After building strWhere from the non-blank yellow boxes, the code tests its length less the 5 characters of the trailing " AND ". It shows a dialog if no criteria are found. Otherwise it removes the last 5 characters, assigns the result to the Filter property of the form, and sets FilterOn to True.
The Click event procedure for cmdReset loops through all the controls in the Form Header section. Any text boxes or combos are set to Null. Any check boxes are set to False (i.e. unchecked.) Any other controls (labels, command buttons, ...) are ignored.
Then the filter is removed by setting the form's FilterOn property to No.
Looping through the controls means you do not need to change this code if you add extra filter boxes to the form in future.
If you do not get the Where string right in your database, the attempt to set the form's Filter may fail. To help with debugging, remove the single quote from this line:
Now when it fails, press Ctrl+G to open the Immediate Window, and see what is wrong with your string. It must look exactly like the WHERE clause of a query. You can mock up a query using any literal values for criteria, and then switch to SQL View (View menu when in query design) to see an example of the WHERE clause you need to create.
The square brackets around field names are optional, unless your field names contain strange characters (spaces, leading numbers, other symbols except underscore.) The parentheses around the phrases are also optional, unless you mix operators. If you do mix ANDs and ORs, be aware that you get different results from:
(a AND b) OR c
a AND (b OR c)
where a, b, and c each represent phrases such as "City = ""New York""".
To use the search results for a report, build the Where string exactly the same way, and then use it as the WhereCondition for OpenReport:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
We assume you have set the Allow Zero Length property to No for all Text fields in your table. If not, you must adapt this code to check for zero-length strings as well as nulls, and remember to do that in all queries and code in your database. Alternatively, you could programmatically fix the database with the code in the Problem Properties article.
If you want the form to open with no results, add these two lines to the Open event procedure of the form:
Me.Filter = "(False)"
Me.FilterOn = True
and add those two lines to cmdReset_Click as well, in place of:
Me.FilterOn = False
Ultimately, the Filter string is something that evaluates to True or False. This expression excludes everything, because it is False for all records.
If a form returns no records and no new records can be added, the Detail section goes completely blank, and you face these display problems. For this reason, we did not set the form's AllowAdditions property to No, but cancel the form's BeforeInsert event instead.
To include a multi-select list box in your criteria, adapt the code shown in this article: Use a multi-select list box to filter a report.
To filter on other tables that are not even in the form's RecordSource, use a subquery.
The sample database also contains a query named qryAlternativeApproach. This query reads the values from the text boxes on the form, but does not use any code.
For many reasons, the query approach is not really recommended:
For the SQL buffs, the query is:
PARAMETERS [Forms]![frmClientSearch]![cboFilterIsCorporate] Short, [Forms]![frmClientSearch]![txtFilterLevel] Long, [Forms]![frmClientSearch]![txtStartDate] DateTime, [Forms]![frmClientSearch]![txtFilterEndDate] DateTime; SELECT tblClient.* FROM tblClient WHERE IIf([Forms]![frmClientSearch]![cboFilterIsCorporate] = -1, (tblClient.IsCorporate), IIf([Forms]![frmClientSearch]![cboFilterIsCorporate] = 0, (NOT tblClient.IsCorporate), True)) AND (([Forms]![frmClientSearch]![txtFilterMainName] Is Null) OR (tblClient.MainName Like "*" & [Forms]![frmClientSearch]![txtFilterMainName] & "*")) AND (([Forms]![frmClientSearch]![txtFilterCity] Is Null) OR (tblClient.City = [Forms]![frmClientSearch]![txtFilterCity])) AND (([Forms]![frmClientSearch]![txtFilterLevel] Is Null) OR (tblClient.LevelID = [Forms]![frmClientSearch]![txtFilterLevel])) AND (([Forms]![frmClientSearch]![txtStartDate] Is Null) OR (tblClient.EnteredOn >= [Forms]![frmClientSearch]![txtStartDate])) AND (([Forms]![frmClientSearch]![txtFilterEndDate] Is Null) OR (tblClient.EnteredOn < DateAdd("d", 1, [Forms]![frmClientSearch]![txtFilterEndDate])));
The technique of dynamically building the WHERE string in code has many uses: forms, reports, executing action queries, OpenRecordset(), even reassigning the SQL property of a QueryDef. Any situation that requires flexible criteria is probably a candidate for this approach.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Build a search criteria form'?
|Rob Davis says...|
14 April 2008
I use a public variable Criteria$ which is emptied as the 'search' form opens. On clicking 'Go' the VBA code explicitly checks each usable object and builds a concatonated Criteria$ before closing the search form and returning to the parent. The parent then checks to see if Criteria$ is null, if not, opens the target object (another form, more usually a report) using Criteria$ as the search parameter. I can send examples if you like.
05 July 2008
Have been using Access for five years on and off. The may reason for going away from it was the inability to have effective search.
Everythingaccess.com has written something that can't be found elsewhere, a proper search.
|Wayne Phillips says...|
05 July 2008
@James: Thank you for your comments, although we can't take direct credit for it as the article is written by Access MVP, Allen Browne.
Glad you found the article useful.
|Adam Kaydar says...|
03 September 2008
The usefulness of the search form - great as it is - would be further enhanced if it included an option to filter for records that have "Null" data, i.e. are blank, and those that have "Not Null" data, i.e. are not blank, for all the filtrable fields.
Also, it would be useful to have an option to filter for an exact match on a date field and not only for "After" and "Prior To". Could you please advise how to achieve this?
|Melissa Miller says...|
11 September 2008
This is great tool. Thanks for sharing. Very easy to setup and use. One question, is there a way to copy the results or dump them into a separate table so they can be copied? I am using this search form to pull a list of emails based on set criteria. I then need to be able to easily copy the email addresses to dump into Outlook.
08 February 2010
Oh THANK YOU.
I was assigned to make a data base in Access. It has a lot of power and I was doing okay, right up to this sort of thing. I used to use Mac/Filemaker, and it's dead easy there. It was a stumper in Access. I've been flailing for a WEEK praying that the boss wouldn't notice the lack of progress.
In just a few minutes, this helped me put a project back on progress. My paycheck thanks you.
10 February 2010
Super tool! Thanks.
What if I have two fields to show (employee and user), and the names are from a same table (people).
After that I would like to update some data at the result Sub-form
26 April 2010
I've been wanting to make a search form like this for some time. Allen's explanation along with the code made this very easy to understand and modify. I'll be using this in several places.
|David McMahon says...|
20 June 2010
Spent all day looking for an appropriate search form example. This one worked great for my purposes.
08 July 2010
Just what I was looking for.
18 July 2010
I am new to Access and this example is extremely well noted as it explains what is going on. Thanks for sharing it
|Eric Hon says...|
22 July 2010
This is a great piece to learn how to utilize search criteria within Access. I do have an issue with the coding that maybe I could get some guidance on. On my "main table", there is a field called "Assigned To", which is linked to another table called "Staff". I had this done in order for users to add new staff without me having to do it. The "Staff" table has a primary key for each staff name.
So, is there a way to code VB so that the search combo box (Me.Staff) can read the primary key off of the main table?
I am getting an error message (previous operation canceled) and I believe the root cause is that the coding is confused by alpha characters being read against the primary key of the staff's name.
Have your say...