Filter a Report Using a Form's Filter

  • Thread starter Thread starter amr
  • Start date Start date
A

amr

Hi,
i'm working on a payroll program
so i want to filter a report by building a form
filter by month ,city,site
but i want to know how i can set the properties of the form step by step !!
 
Is this a form or report question? Do you have any field names you can share?

Do you understand that your first step might be to create some combo boxes
that allow you to select a Month, City, and State?

If you have combo boxes, they should be given names like: cboMonth,
cboCity,....
 
You probably want to filter your report by year also!

Presumably you have these two tables:
TblCity
CityID
City

TblSite
SiteID
Site

Your form, named ReportCriteria, needs four comboboxes. The first, cboCity,
has a rowsource of TblCity. The second has a rowsource of TblSite. The
third, cboYear, has a rowsource of a query that returns a list of the Years
in your TblPayroll. The fourth, cboMonth, has a value list fom 1 to 12.

You need a query that contains the fields, CityID, SiteID, Year([PayDate])
and Month([PayDate]).
The criteria for CityID needs to be:
Forms!ReportCriteria!cboCity

The criteria for SiteID needs to be:
Forms!ReportCriteria!cboSite

The criteria for Year([PayDate]) needs to be:
Forms!ReportCriteria!cboYear

The criteria for Month([PayDate]) needs to be:
Forms!ReportCriteria!cboMonth

Set the recordsource of your report to this query.

Finally, you need a button on ReportCriteria that opens your Report. You
need code in the click event of he button that first checks to see if all
comboboxes have a value and then if they do, opens the report.

Steve
(e-mail address removed)
 
so i want to filter a report by building a form
filter by month ,city,site
but i want to know how i can set the properties of the form step by step !!
Click to expand...

You've gotten a couple of other responses, but I wanted to suggest
doing it a different way. Instead of the form calling the report,
have the report call the form. Here are some details:

To have a report prompt the user for criteria before it runs, open a
form from the report's Open event. Open the form in Dialog mode so
that the report waits for the form to be closed or hidden before it
proceeds. That way you can collect criteria from the user and build a
Where clause for the report. It also means that you can call the
report directly - you don't need to call it from a form. And the
selection form is reusable - it can be called from multiple reports if
they need the same criteria.

I've posted examples of this technique on our J Street Downloads page
at http://ow.ly/M58Y
See "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top