Hi Todd,
There are a couple of ways of doing this. You can open a form, which
includes a combo box for selecting an employee. Add a command button that
includes code to read the selected value from the combo box, and incorporate
it into a filter. Something like this:
1.) Create a new report in the sample Northwind database, based on the
Employees table. Name the report rptEmployees. Create a new form in this
database as well.
2.) Add a combo box to the form without having the wizard selected (or close
the combo box wizard if it opens). Name the combo box cboSelectEmployee.
3.) On the data tab of the properties dialog (View > Properties in form
design), set the Row Source Type for the combo box to Table/Query. Copy the
following SQL statement, and paste it into the Row Source:
SELECT Employees.EmployeeID, [LastName] & ", " & [FirstName] AS Employee
FROM Employees ORDER BY Employees.LastName, Employees.FirstName;
On the format tab, set the Column Count to 2, and Column Widths to: 0";1.5"
4.) Add a command button to the form (without the wizard...or cancle the
wizard). Name the command button cmdOpenEmployeeReport. Add the following
code to the click event procedure for the command button:
Option Compare Database
Option Explicit
Private Sub cmdOpenEmployeeReport_Click()
On Error GoTo ProcError
If Not IsNull(Me.cboSelectEmployee) Then
DoCmd.OpenReport ReportName:="rptEmployees", View:=acPreview, _
WhereCondition:="EmployeeID = " & Me.cboSelectEmployee
Else
MsgBox "Please select an employee first.", vbInformation, "Employee
unknown..."
Me.cboSelectEmployee.SetFocus
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdOpenEmployeeReport_Click..."
Resume ExitProc
End Sub
The other method involves opening a selection form in dialog mode via the
report's On_Open event procedure. If you send me a private e-mail message
with a valid reply-to address, I will send you a sample database with
PowerPoint file that explains how to implement this procedure. My e-mail
address is available at the bottom of the contributor page indicated below.
Please do not post your real e-mail address in a reply, unless you want to
invite the attention of spammers.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
:
I need some help with a report I've created. I’ve created a report where you
can get a detailed report about an employee’s expense by typing in the name.
What I want to do is, in my SQL statement have a list names to choose from
when you click on the report.
Thanks
Todd
