Improve your Access Applications in Minutes

Database Tips and Techniques

Databases are very powerful tools used to find, sort, reformat, manage, send and do all kinds of other things with data on your computer. The more data you have the more you need a database to access it quickly, but that makes the data harder to find. So, we built a tool that lets a user ¡§drill down¡¨ to data very quickly in a form by just clicking on a field that has the type of data that he wants to find. Lets say you have a customer database with thousands of records from customers all over the country and you need to find every one born in June in Georgia with the last name of Smith to do a promotion. Quick how do you find that data? With this tool you can find it with three clicks of your mouse! Take this technology and make it yours to create very powerful forms for your user applications.

Create a Drill Down Form

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