Re: Reports Question
Rick Brandt
news:D6B8B59E-6A02-4AC0...@microsoft.com...
>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.
Can't do it in the query. You need to create a form that offers the choices in
a ComboBox. Then you have a choice.
You can change the query so it pulls its criteria from the form...
SELECT *
FROM SomeTable
WHERE SomeField = Forms!FormName!ComboBoxName
...or remove criteria from the query and pass the choice in the WHERE clause of
OpenReport...
DoCmd.OpenReport "ReportName", acViewPreview,,"SomeField = '" & MeComboBoxName &
"'"
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Tom Wickerath
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
__________________________________________
"Todd" wrote:
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
mmellring
"Todd" <To...@discussions.microsoft.com> wrote in message
news:D6B8B59E-6A02-4AC0...@microsoft.com...