Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   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  Buy Now

Welcome

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!

Resources

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.

Questions?

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

 

Dim and Resources Upload Images   Link 
Ole Simonsen 
2 days 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
2 days 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 
8 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
8 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.

V/r
Juan
Richard Rost
8 months ago
My pleasure!
Add a Reply

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.
 
Subscribe
 

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

10/21/2021Conditional Formatting
10/20/2021Text Messages
10/19/2021Late Binding
10/18/2021Open Form
10/17/2021Date Only?
10/16/2021Relink Tables
10/16/2021Access Developer 34
10/16/2021Access Developer 34 Lessons
10/16/2021Access Developer 34
10/10/2021Copy Text from Field
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
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