Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Dec 1, 2002

Microsoft Access 2000 How To's Series - Page 3

By David Nishimoto


How To Build a Combo Box as a Data Filter

This Access 2000 How To's article explains how to apply a filter to an Access 2000 form by using a combo box as a filter parameter. You can use the filter property to specify a subset of recordset to be displayed.

 

Create the People Table

  1. PeopleId, AutoNumber
  2. PeopleAffected, Text

Create the Process Table

  1. ProcessId, AutoNumber
  2. ProcessName, Text

Create the ProcessToPeople Table

  1. Id, Autonumber
  2. PeopleId, Number
  3. ProcessId, Number

Create a Query called qryPeopleAffected

  1. Join the Process table to the ProcessToPeople table on the ProcessId field.
  2. Join the People table to the ProcessToPeople table on the PeopleId field.

Create a Form called PeopleAffected

  1. Select the form wizard.
  2. Select the qryPeopleAffect query as the date source.
  3. Select all the fields.
  4. Select Columnar as the type of form.
  5. Title the form "People Affected".
  6. In the Form Header, drag and drop an unbound combo box and change the caption to "filter".
  7. Open the Properties dialog for the combo box. Name the combo box "cboSelectableProcess".
  8. Start the Query Builder (QB) from the "Record Source" item property.
  9. QB: Select the Processes table and drag and drop the ProcessId and ProcessName fields into the design grid. Click X to close the window and answer "yes" to updating the property with the SQL. Change column count property item to 2. This will allow you to see the processid and processname in the combo box.
  10. Launch the Code builder from "On Click" property item for the combo box. Choose the code builder option.
  11. Insert the following code fragment:
  12. Private Sub cboSelectableProcess_Click()
       Dim sProcessId
       With cboSelectableProcess
          If .ListIndex <> -1 Then
             'sProcessId = .SelText
             'Retrieves the value of the bound column which may more may not be
             'displayed in the list box
             sProcessId= .Column(.boundcolumn-1)
             Filter = "ProcessId=" & sProcessId
             FilterOn = True
             MsgBox "Filter Applied"
          End If
       End With
    End Sub
    
  13. Dataview: The final result is all records displayed during the initial load, and as the user selects a processid from the cboSelectableProcess combo box, the code parameterizes the filter's "Where" clause with the selected process id.


Next Access "How To": Building a Query Command Function

Back to Access 2000 How To's Series Home

 



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM