On Tue, 25 Aug 2009 17:15:45 -0700 (PDT), Lostguy wrote:

Hello!

I have tblEmployee with 400 employees and a tblDepartment with 20
departments.

What is the best way to set it up so that I can print a report of ALL
employees and their associated department (no filter) and then have
the user select (via a dropdown) the department that they want to see
a report on.

I was thinking that a button on my Report Switchboard form opens
another form with just a combobox tied to tblDepartment, and then that
combobox supplies the filter for the report...
This is OK.
Create an unbound form with your Combo Box control on it.
Set the rowsource of the combo box to display all the department
names.
"Select tblDepartment.[DepartmentName] from tblDepartment Order By
[DepartmentName]"

Add a command button.
Code the Command Button's Click event:

DoCmd.OpenReport "EmployeeReport", acPreview, , "[DepartmentName] = '"
& Me.[ComboName] & "'"
DoCmd.Close acForm, Me.Name

Name this form "frmDeptSelect".

Code the Switchboard to open the frmDeptSelect.
DoCmd.OpenForm "frmDeptSelect"

When ready to run the report, from the switchboard open the
frmDeptSelect. Find the correct department in the combo box. Select
it.
Click on the Command button and the report will run, filtered, and the
form will close.


I currently have one query with 20 reports and that seems crazy.

If you wish to have the ability to print "All" the departments as well
as filtered, code the frmDeptSelect Command Button Click event
(instead of as above):

If IsNull(Me.[ComboName]) then
DoCmd.OpenReport "EmployeeReport", acPreview
Else
DoCmd.OpenReport "EmployeeReport", acPreview, , "[DepartmentName] =
'" & Me.[ComboName] & "'"
End If
DoCmd.Close acForm, Me.Name

Click the command button without selecting any department and all the
departments will be shown.

And 20 queries with one report doesn't seem right either. So there has to
be a way to do one query, one report, and then some intermediate user
thing.

??
VR/Lost


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail