Button to filter a report

  • Thread starter Thread starter RandyH
  • Start date Start date
R

RandyH

TIA,

I have a report (Time Sheet Report) that I would like filtered to a single
employee during a particular time span. I started to make a form with an
unbound combo box showing the employees (employee_combo), with two unbound
text boxes (PayPeriodBegin_Text and PayPeriodEnd_Text). The idea was to have
the user select the employee, enter the time span, click the ok button and
the filtered report would come up. I have not been able to get the code to
work on the ok button. I would prefer to keep this intermediate screen
because I think it would be less confusing to the user, but I may be swayed
to a different approach.

Thanks again, Randy
 
Hi Randy

the basic steps to achieving this are:
1. create the query that the report is to be based on
2. use the report wizard to build a report based on the query
3. create the "report options" form - ensuring that i name each control
something easy to find later (ie if its the start date that i want i name
the control startdate) - close & save form
4. return to the query, click in the criteria line of the field that i
need to reference the control on the form
5. click on the expression builder icon on the toolbar
6. go to forms / all forms / find my "report options" form, in the centre
section double click on the form control that i am using as the criteria
do this for all of the criteria that i'm setting
7. close & save query
8. return to the form & put a command button on it to open the report in
print preview mode - close & save form
9. open form, set criteria, click print button

hope this helps
Cheers
JulieD
 
Thanks Julie, I've got the query working (it asks for the right parameters
and returns the correct results), but when I click on command button (see
step 8), the report comes up with no information what so ever. Any thoughts?

Randy
 
Hi Randy

just checking - is the query the report's record source?
what happens when you go into the query via the record source property & run
the query from in there?

Cheers
JulieD
 
Julie, the query is the report's record source. And I don't know how to do
your second suggestion...

Randy
 
Hi Randy

you go into design view of the report - bring up the properties window,
click on the record source line, click on the ... and the query window will
open - you should see the window & the criteria line there - click on the
red exclaimation mark

this runs the query inside the report - you should get prompted for all your
fields, enter some parameters and see if the recordset that you expect is
returned.

Cheers
JulieD
 
In design mode for the report, set the report's RecordSource property to the
whole table that contains your data, or a query statement that contains all
the data you want to include in the report (possibly a joined query
expression). In the On_Click() event of the command button on your interim
form, set a where condition to filter the query for the one record you want
in the report, and open the report with the where condition represented
thus:

cmdOpenReport_Click()
dim stLinkCriteria as String
stLinkCriteria="EmployeeID=" & cboEmployee & _
" AND (PayPeriod BETWEEN #" & PayPeriodBegin_Text & _
"# And #" & PayPeriodEnd_Text & "#)"
DoCmd.OpenReport "Time Sheet Report", acViewPreview, , stLinkCriteria
End Sub

I have made some assumptions to the setup of your comboBox and its
underlying data source, and the names of your controls and report. You will
need to adjust to your actual circumstances.
The missing parameter in the DoCmd line is for a Filter Name. Yours is a
where condition, so leave the empty parameter as it is.

HTH.
Paul Johnson
 
Back
Top