Form Selections Runs Opens Reports...Separate Queries?

[Heisall]

I have a database to track unpaid invoices that contains a single form for previewing and printing reports for unpaid invoices. My objective is to allow users to select a Location (meaning the project mgr's office) and then an Employee (project mgr), and then specify the number of days the invoice is unpaid.

The form has a combo box for "Location" and another for "Employee". It also has an Option Group "Days Unpaid" with two criteria: Less Than and More Than. Lastly, I have a text box displayed to the right of the option group where the user inputs the number of days.

For example:
Location = Office1
Employee = Smith, J.
Days Unpaid = More Than
Days = 60

I also have three command buttons: "Preview Report", "Print Report", and "Cancel".

First question: What is the best way to preview or print the report based on criteria selected by the user? I currently have a select query for all records sorted by Location, Employee, and Days Unpaid as well as a report based on the query.

Second question: How do I add an "ALL" option to the "Employee" combo box to so that users can have the option to print reports for the entire office instead of an individual employee?

Thanks for your interest -- Don

 

[jmersing]

Your first question:

In your select query you can use the form name and control name to trap the value in those combos and text boxes. Especially helpful for unbound fields like this:

Select location, employee, days_unpaid
from yourtablename
where location = forms.yourformname.yourcomboname & " "
and employee = forms.yourformname.yourtextboxname & " "
and days_unpaid >= (or <=) forms.yourformname.youroptiongroupname & " "
Second question
You need to use a union query as the row source for your employee combobox like this
SELECT DISTINCT yourtable.employee FROM yourtable UNION SELECT DISTINCT '' FROM yourtable Order by 1 ;

Then add this to the On Change event of that combo:

Dim strsql As String
strsql = "SELECT * " _
& "FROM yourselectqueyname " _
& "WHERE 1 = 1 "
forms.yourformname.yourtextboxname
If Forms![yourformname]!youremployeetextboxname
> "" Then
strsql = strsql & "AND employee = '" & Forms![yourformname]!youremployeetextboxname & "' "
End If
Forms![yourformname].RecordSource = strsql

When you select the dropdown for your combo there will be one blank selection at the top of the list, that is your Select * option
HTH