Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Home > Courses > Access > Developer > D26 > < D25 | D27 >
Access Developer 26

Two Search Forms: Filter Boxes, Criteria Table

 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark Buy Now


In this class we will start out by fixing a bug from Developer 25 that one of the students discovered. Then we will build two different search forms. The first will use filter boxes over the column headers. These filter boxes will be used to construct a dynamic SQL statement that feeds a list box. The column headers will also be used to control the sort. Then the second search form will have criteria stored in a table that you can easily manipulate. We will then use a recordset to read in the criteria from the table to build an SQL statement. Cool, new stuff!


Topics Covered

In Lesson 1, we will fix a bug that one of our students found from D25. When you go to ship an order, the system sees the product groups as products which have zero stock. We'll fix that.

In Lesson 2, we will begin building a search form that will filter records in a list box using a Dynamic SQL Statement. This essentially creates an SQL statement based on the values selected on the form. In this lesson we'll create the form, set up header labels that will control the sort with an ORDER BY condition. Click on a header to sort by that column. We'll add spiffy colors. We'll see how to always sort NULL values on the bottom of the list. 

In Lesson 3, we will add filter boxes and build the dynamic WHERE condition. Filter by whatever columns you want. We'll add a combo box so you can select either an OR or AND condition.

In Lesson 4, we will take the search form to a whole new level. We will create a criteria table. We'll use a recordset to read the conditions out of the table and construct the SQL statement. This way you can add as many different criteria as you want for multiple fields, or the same field. For example you could say "State equals Florida AND FirstName starts with R AND CreditLimit <= 5000 AND CreditLimit > 1000." Sky's the limit with this one! We'll learn how to read field data types from a TableDef.

In Lesson 5, we will create a combo box that has a list of all of the fields in the selected table. We will read that list of fields from the TableDef Fields collection. Then we will make a button to add the criteria to the table and update the results. 

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.


Please feel free to post your questions or comments below. Thanks.


Recordset in recordset Upload Images   Link  
Ole Simonsen 
3 months ago
Hi Richard!
I try to open an record set within a recordset. It runs. But I can not close the second recordset.
Adam Schwanz
3 months ago
What do you mean you can not close the second record set? Do you get an error or something (if so what does it say?). Could you share your code or some images so we have something to go off of? Snip & Sketch
Richard Rost
3 months ago
Slam the lid harder. :)

Yeah... need more info for this one.
Add a Reply
Dim and Resources Upload Images   Link  
Ole Simonsen 
3 months ago
Hi Richard!
You talked about how each dim in VBA took up resources. Is there a max. Or is it ok with many ?
Richard Rost
3 months ago
There is a max, but realistically you should never reach it. Every variable you declare with a DIM takes up a tiny bit of resources, and every object you declare with SET should be destroyed. Access USUALLY does a pretty good job of clearing up that memory with the sub or function exits... but not always. So make sure you restart your database often and reboot your PC every couple of days. :)
Add a Reply
Developer 26 Lessons Up Upload Images   Link  
Richard Rost 
11 months ago
I just posted Lessons 1 thru 5 of Developer 26. Lots of fun in this class building a really cool recordset-powered search form! The level is basically finished. It's over 2 hours long. I just need to record the "bookends" tomorrow (Intro and What's Next videos, which I always do last) and polish it up, but you can watch the actual videos now if you don't want to wait. Enjoy!
Juan C Rivera
11 months ago
WOW!  I asked for gas powered search nice job with the sorting, But WOW! Nuke power criteria.  This is stuff you will never learn in txt books.  Thank you for showing us.

Richard Rost
11 months ago
My pleasure!
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

You may want to read these articles from the 599CD News:

1/22/2022Next Appointment
1/18/2022Access B2 Upgrade
1/17/2022Access Beginner 2 Links
1/17/2022Access Beginner 2
1/17/2022New Record on Top
1/13/2022Missing References
1/13/2022Access Beginner 1 Links
1/11/2022Access Fast Tips
1/11/2022System Defaults

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access developer 26 dynamic sql statement search form dynamic where condition tabledefs fields collection for each sort nulls on bottom  Page Tag: whatsnew  PermaLink