Create a form that lets you drill down to the data you want to see by just clicking on a field in the form.  This example is built for/with Access 2000.  It requires that you have a moderate level of experience with Access 2000 and creating Access Applications.    

 The Drill Down form allows you to quickly sort down to information that you want to see.  In this picture example you can click on a county name and it will sort the table for that county.  If you click on Douglass in the county column the form will filter and sort and just show you every client that lives in Douglass county.  If you subsequently double click on Douglass the form will un-apply the filter and reshow all counties.  It is a really fast way to drill down to specific data and adds a powerful form to any application that your users will love to use.   

 You can set up as many or as few columns as you wish to be active for sorting and filtering.  In this example I have set up the Firstname, City, County and DOB columns to drill down as you select.  The filter is also cumulative, so if you select more than one item for filtering then you continue to drill down until you get to one record.    

 The column headings also are used to perform an Ascending sort based on the data in their respective columns.  So, by clicking on the City label, the data would sort alphabetically on the city names.    

 Try it and see.  You and your users will find it a very powerful addition to any application.   

 Create a Module and name it DDFMod then key in or copy these lines into the module.     

 Option Compare Database  Public DDFFname As String  Public DDFLname  As String  Public DDFSort As String  Public DDFCity As String  Public DDFStreet As String  Public DDFState As String  Public DDFCounty As String  Public DDFDate As String    

 Public Function GetDDFSort() As String  GetDDFSort = DDFSort  End Function   

 Public Function GetDDFCity() As String  GetDDFCity = DDFCity  End Function   

 Public Function GetDDFStreet() As String  GetDDFStreet = DDFStreet  End Function    

 Public Function GetDDFCounty() As String  GetDDFCounty = DDFCounty  End Function    

 Public Function GetDDFState() As String  GetDDFState = DDFState  End Function    

 Public Function GetDDFdate() As String  GetDDFdate = DDFDate  End Function    

 Public Function GetDDFFname() As String  GetDDFFname = DDFFname  End Function    

 Public Function GetDDFLname() As String  GetDDFLname = DDFLname  End Function      

 Next Create a Client table and Name the new table client    

 Next Create a Query (you can do this by copying the following sql into the query designer in Access.      

 Create a new query and view it in SQL View.  Then cut and paste the following sql into the form.  Then save the query as DDFExample.    

 You can type following SQL into the SQL view in Access or better yet cut and paste it.  If you then switch back to design view you will see the query in the form shown above.     

 SELECT client.Fname, client.Lname, client.Street, client.City, client.St, client.Zip, client.county, client.Phone, client.DoB, IIf(GetDDFFname()="ALL","ALL",[fname]) AS DDFFname, IIf(GetDDFLname()="ALL","ALL",[Lname]) AS DDFLname, IIf(GetDDFCity()="ALL","ALL",[City]) AS DDFCity, IIf(GetDDFdate()="ALL","ALL",Str([DOB])) AS DDFDate, IIf(GetDDFState()="ALL","ALL",[St]) AS DDFState, IIf(GetDDFCounty()="ALL","ALL",[County]) AS DDFCounty, IIf(getddfsort()="city",[city],IIf(getddfsort()="county",[county],[fname])) AS sort  FROM client  WHERE (((IIf(GetDDFFname()="ALL","ALL",[fname]))=GetDDFFname()) AND ((IIf(GetDDFLname()="ALL","ALL",[Lname]))=GetDDFLname()) AND ((IIf(GetDDFCity()="ALL","ALL",[City]))=GetDDFCity()) AND ((IIf(GetDDFdate()="ALL","ALL",Str([DOB])))=GetDDFDate()) AND ((IIf(GetDDFState()="ALL","ALL",[St]))=GetDDFState()) AND ((IIf(GetDDFCounty()="ALL","ALL",[County]))=GetDDFCounty()))  ORDER BY IIf(getddfsort()="city",[city],IIf(getddfsort()="county",[county],[fname]));       

 Next Create a Form    

 Use the DDFExample Query you just defined as the data source for this form.    

 In Design View  Lay your form out as below.    

 The following are the label names as shown on the Other Tab on the properties form.  from left to right on the above form.  

 namelabel, Lnamelabel, Streetlabel, Citylabel, Countylabel, Doblabel. phonelabel    

 Name your form and save it.    

 Form Code    

 Create the following VB code for each the respective controls on the form.  Again you can cut and paste the next section right into your form in design mode.     

 Option Compare Database    

 Private Sub county_click()  DDFCounty = county  DoCmd.Requery  End Sub    

 Private Sub county_dblclick(Cancel As Integer)  DDFCounty = "All"  DoCmd.Requery  End Sub    

 Private Sub clientname_click()  DDFFname = ClientName  DoCmd.Requery  End Sub    

 Private Sub clientname_dblclick(Cancel As Integer)  DDFFname = "ALL"  DoCmd.Requery  End Sub    

 Private Sub City_click()  DDFCity = City  DoCmd.Requery  End Sub    

 Private Sub City_dblclick(Cancel As Integer)  DDFCity = "ALL"  DoCmd.Requery  End Sub    

 Private Sub dob_click()  DDFDate = Str(DoB)  DoCmd.Requery  End Sub    

 Private Sub dob_dblclick(Cancel As Integer)  DDFDate = "ALL"  DoCmd.Requery  End Sub    

 Private Sub Form_Open(Cancel As Integer)  DDFSort = "fname"  DDFFname = "ALL"  DDFLname = "ALL"  DDFCity = "ALL"  DDFCounty = "ALL"  DDFDate = "ALL"  DDFState = "ALL"  DoCmd.Requery  End Sub    

 Private Sub Lname_Click()  DDFLname = Lname  DoCmd.Requery  End Sub    

 Private Sub Lname_DblClick(Cancel As Integer)  DDFLname = "ALL"  DoCmd.Requery  End Sub    

 Private Sub namelabel_click()  DDFSort = "Fname"  DoCmd.Requery  End Sub    

 Private Sub closeqb_click()  DoCmd.Close  End Sub    

 Private Sub Citylabel_click()  DDFSort = "City"  DoCmd.Requery  End Sub    

 Private Sub countylabel_click()  DDFSort = "CCY"  DoCmd.Requery  End Sub    

 Private Sub ReqQB_click()  DDFSort = "fname"  DDFFname = "ALL"  DDFLname = "ALL"  DDFCity = "ALL"  DDFCounty = "ALL"  DDFDate = "ALL"  DDFState = "ALL"  DoCmd.Requery  End Sub    

 This should do it.  Seems like a lot of work for such a short form, but the more data that you have the more powerful this tool becomes.  We have users sorting through more than 4000 customer demographic data records using this tool and they love it.  

 What could BioMation Systems do for you?    

 For a more detailed article including graphics see www.biomationsystems.com.   

 You can also download a more powerful and easy to use version of the drill down tool now.  The new version eliminates a lot of the setup needed.   Find it by looking for the Drill Down Designer download page at either of our sites listed below.

 Thank you for your interest and I hope you find this article useful in your efforts to develop powerful applications for your users. 

 BioMation Systems, Inc is an Atlanta, Georgia based consulting company that develops custom database solutions that increase the efficiency of businesses around the world. BioMation's range of services can be found at www.biomationsystems.com     

 You can find help for Access at

 http://www.accessdatabasehelp.com

 http://www.accesshelpebook.com

 http://www.biomationsystems.com/AccessTips.htm

 Contact:  jonw@biomationsystems.com

Article Source: http://www.articlesbase.com/databases-articles/improve-your-access-applications-in-minutes-243687.html

About the Author:
Jon Watson founded BioMation Systems after 26 years of consulting with Fortune 500 companies to bring fast and affordable process improvement to smaller companies.