RowSource ListBox 1

[johnaregan]

In Access I have set the RowSource of a list box to get its' values from a query, one of the results is then selected from the list box to be used in another 2nd query.

I also the user to have the option to select an option called "All" which would then be used in the 2nd query to return all the values from the table.

So how would I add the "All" option to the the listbox? And how would I use this in an SQL statement?

Thanks in advance
John

 

[PaulF]

I was just reading an article in this months Inside Microsoft Access from ElementK Journals that discussed exactly this problem. It uses a Union Query to get the results. This example would display the unique cities in the table Employees, and also (ALL). Then for your case, you'd need to check the value of the ComboBox in the AfterUpdate event and make the set the Row Source for the second ComboBox based on the results of the First

This is the Row Source for CombBox 1 (cboCity)
Select DISTINCT city From Employees Union Select "(ALL)" From Employees;

Assuming that you want to show LastName, FirstName and City in the 2nd ComboBox (cboEmployees), you'd use this in the AfterUpdate Event for cboCity

Private Sub cboCity_AfterUpdate()
If cboCity = "(ALL)" Then
cboEmployees.RowSource = "Select LastName, FirstName, city From Employees Order By [LastName], [FirstName]"
Else
cboEmployees.RowSource = "Select LastName, FirstName, city From Employees Where [City] = '" & _
cboCity & "' Order By [LastName], [FirstName]"
End If
cboEmployees.Requery
End Sub

PaulF