Generally, it would go in the Click event of a command button on your form.
Assuming you have a command button named cmd_OpenReport, then click on that
control, then right click and view the properties of the control.
If you don't already have a command button, create one, and when the
"Command Button Wizard" dialog opens, just hit Cancel (this will put the
button on your form with no code behind it). Click on the button and
display the properties window. In the Property Sheet, on the Other tab,
enter "cmd_OpenReport" in the space to the right of the Name property.
Next, on the Event tab, you will see an option "On Click". Click the down
arrow that is to the right of the blank space on that line, and select
[Event Procedure]. Then click on the button with the "..." to the right of
that button. This will open your code window to the Click event of the
command button. It should look like:
Private Sub cmd_OpenReport_Click()
End Sub
I like to add some error code to my buttons, so you will probably want to
check to make sure that a name has been selected in cbo_Employee, and that
the starting and Ending Dates have been filled in. So, expand the code to
look like:
Private Sub cmd_OpenReport_Click()
if Len(me.cbo_Employee & "") = 0 then
msgbox "Select an employee!"
me.cbo_Employee.setfocus
elseif Len(me.txt_StartDate & "") = 0 then
msgbox "Enter a start date!"
me.txt_StartDate.setfocus
elseif Len(me.txt_EndDate & "") = 0 then
msgbox "Enter an end date!"
me.txt_EndDate.setfocus
Else
DoCmd.OpenReport "ReportName", _
acViewPreview,,_
"[Employee] = '" & Me.cbo_Employee & "' " & _
"AND [StartDate] >= #" & Me.txt_StartDate & "# " & _
"AND [EndDate] < #" & Me.EndDate & "#"
endif
End Sub
Assuming that you replace the various field names and control names in the
above text with the values that are appropriate for your data structure,
this should work.
Since I don't know what the SQL RowSource of your combo box is, I'll advise
you that I generally use a SQL statement that looks something like:
SELECT Emp_ID, Emp_Name_Last & ", " & [Emp_Name_First] as EmpName
FROM tbl_Employees
ORDER BY Emp_Name_Last & ", " & [Emp_Name_First]
as the RowSource for my employee combo boxes. I also generally hide the
Emp_ID, and only display the Employee name, or occassionally also display
the employees department or boss, to identify between two people with the
same first and last name. If you are doing this, and your bound column is
the Emp_ID (numeric), then you will need to change the code above to reflect
a numeric value for the Emp_ID field. Something like:
DoCmd.OpenReport "ReportName", _
acViewPreview,,_
"[Emp_ID] = '" & Me.cbo_Employee & "' " & _
"AND [StartDate] >= #" & Me.txt_StartDate & "# " & _
"AND [EndDate] < #" & Me.EndDate & "#"
HTH
Dale
vladi16 said:
Rick,
Thanks for taking the time... First, let me explain that when it comes to
writing this stuff I am as green as it gets so would you be willing to
break
it down a bit more for me?
Such as: The code you included in your respons... Where does that go? On
the report or on the Form I will create?
:
On Mon, 22 Dec 2008 10:34:05 -0800, vladi16 wrote:
I have seen several posts which come close to explaining what I'd like
to do but nothing with the whole package... So here goes:
I have a form that I enter employee observations on that includes the
date of the observation and the employee's name. I have a report
(rptByEmployee) that I would like to have opened by a form that would
allow me to filter the observations by the employee's name (combo box)
and between two dates.
So basically, I need three fields on the form: Employee (combo box),
Starting Date, and Ending Date.
Does this make sense and is this enough info?
Thanks,
Taking this in steps. There is a method you can call when a button on
your form is pressed that will open your report...
DoCmd.OpenReport _
"ReportName", _
acViewPreview
The OpenReport method has an optional WHERE argument that you can use to
filter the report being opened...
DoCmd.OpenReport "ReportName", _
acViewPreview,,_
"SomeField = 'Some Text'"
The WHERE clause can reference controls on your form instead of being a
hard-coded value as I have above...
DoCmd.OpenReport "ReportName", _
acViewPreview,,_
"SomeField = '" & Me.ControlName & "'"
The WHERE clause can use "AND" and "OR" just as a query can so you can
have more than one criteria...
DoCmd.OpenReport "ReportName", _
acViewPreview,,_
"SomeTextField = '" & Me.ControlName & "' " & _
"AND SomeNumberField = " & Me.OtherControlName & " " & _
"AND SomeDateField >= #" & Me.StartDate & "# " & _
"AND SomeDateField < #" & Me.EndDate & "#"
Is that enough to get you started?
