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. 

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.

 

Developer 26 Lessons Up Upload Images   Link 
Richard Rost 
7 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
7 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
7 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:

9/25/2021Access Developer 32 Lessons
9/25/2021Access Developer 33
9/25/2021Access Developer 33 Lessons
9/25/2021Access Developer 33
9/24/2021Export HTML
9/22/2021Access DevNet Testimonials
9/20/2021Letter Writer
9/19/2021Multi Field Combo
9/19/2021Project Management
9/17/2021Overlapping Windows
 

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   Page Tag: whatsnew  PermaLink