ComboBox Source 1

[Taff07]

Anyone,

I have a form/Report linked to a combobox which filters the results produced. My problem is that the combo source is coming from the main table and produces a list of all the departments in the table. I require also to be able to produce the report with all the departments and not limited to a single choice. To this end, is it possible, somehow, to have an extra option within the combo box or does anyone have any other solutions?

Thanks in Advance

Ian Oldreive

 

[dzshanno]

How About:-

You could add another entry to the combo box by using a UNION query

e.g.

Select department from tbldepartments
UNION
Select "All" from tbldepartments

then do some code to select a different query if the value of the combo box is "All" when you fire the report

 

[930driver]

You can use a union query, for example, if your combo's RowSource is this SQL statement

SELECT DepartmentID, DepartmentName FROM tblDepartments
ORDER BY DepartmentName;

you can then easily add "(All)" as the first choice. Also, if DepartmentID is the bound field but it's width is set to zero (so that the user only sees DepartmentName), you can store a NULL (if the bound field is not the primary key of the table), or some other value in the bound field.

SELECT DepartmentID, DepartmentName FROM tblDepartments
UNION Select Null as AllChoice , "(All)" as Bogus From Departments
ORDER BY DepartmentName;

What you do programmatically with All I'll leave up to you

 

[Taff07]

Thanx for both your quick responses, Thought the Union query would be the way but have never touched on them before. It worked a treat.

I couldn`t use a different Value as it came from the raw data as I only wanted the list to show those values used and not all possible values available.

Thanks Again

Ian