Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
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

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.

 

Comments for Access Developer 26
 
Age Subject From
7 monthsRecordset in recordsetOle Simonsen
7 monthsDim and ResourcesOle Simonsen
15 monthsDeveloper 26 Lessons UpRichard Rost

 

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:

5/27/2022Add Anything
5/26/2022List Items Edit Form
5/25/2022Column Colors
5/24/2022Multi-Table Forms
5/23/2022Variables
5/21/2022Access Developer 39
5/21/2022Access Developer 39 Lessons
5/21/2022Access Developer 39
5/21/2022Select Items in Related Table
5/21/2022Clear List Box
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
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  Microsoft Access Developer 26