How to limit values in a list box based on selection from a combo box?

[Lori Brynlund]

Hi, I'm trying to create a form (MS Access 2003) that currently has 2 items in it: a combo box and a list box. I'd like to be able to make a selection from the combo box and have the list box display a filtered version of the data based on the selection from the combo box.

For example, the combo box has a list of different projects, (a, b, c, and d) and the list box has a list of ALL of the reports associated with all of the projects. I'd like to be able to select project "a" from the combo box and get ONLY the reports associated with project "a" displayed in the list box. The list box is currently based on a query that contains 2 columns, a column with the "report name" (which will remain VISIBLE) and a 2nd column (which will eventually become not visible) with the "report type" (the "report type" here should match up with the project selected from the combo box).

Any suggestions would be greatly appreciated!

[ADezii]

First, a couple of Assumptions:

1. Table name is tblReports with the following Fields:
   1. [Report Name] {TEXT}
   2. [Report Type] {TEXT}
2. Combo Box is named cboProjects and contains only the Unique Report Types that exist in tblReports, namely:
   1. NCAR
   2. OFI
   3. QI
   4. TIR
   5. etc...
3. List Box is named lstReports, and has the following characteristics :
   1. Row Source Type = Table/Query
   2. NO Row Source defined
   3. Column Count = 1
   4. Bound Column = 1
   5. Column Width = Width of widest Report Name
4. In the AfterUpdate() Event of cboProjects, Copy-N-Paste the following Code:
   

   Private Sub cboProjects_AfterUpdate()
   If IsNull(Me![cboProjects]) Then Exit Sub
   
   'Do any Reports exist for the selected projected?
   If DCount("*", "tblReports", "[Report Type] = '" & Me![cboProjects] & "'") = 0 Then
     Me![lstReports].RowSource = ""
       MsgBox "No Reports exist for Project [" & Me![cboProjects] & "]", vbExclamation, "Report(s) Not Found"
         Exit Sub
   End If
   
   'If you get here, you have 1 or more Reports for the selected Project
   Me![lstReports].RowSource = "SELECT [Report Name] FROM tblReports WHERE [Report Type] = '" & Me![cboProjects] & "' " & _
                               "ORDER BY [Report Name];"
   End Sub

5. Any questions, feel free to ask.

[Lori Brynlund]

Works like a charm! Thank you very much!

[ADezii]

You are quite welcome, Lori, glad to be able to help.