Custom Reports by Form input (user picks report options on Form and gets report showing options picked)

[RC]

I can't quite grasp the concept of creating custom reports depending
upon what options a user picks on a Form. For example, the user
clicks on a "Print Reports" button and a Form pops up. On the Form
the user can choose to get a report that shows the Box Numbers that
are at Warehouse A, Warehouse B or Warehouse C. and then click on the
Print Preview button and get a report showing only the Box Numbers in
whichever warehouse was selected.
My project is 99% done but I just can't get this last report bit. I am
using Access 2002 but the database (for some reason) says that it is
in Access 2000 format. I've tried altering code from the Northwind
database but I got hopelessly lost in the code and got one error after
another. Thanks in advance for your help.

[Bradley]

RC wrote:

I can't quite grasp the concept of creating custom reports depending
upon what options a user picks on a Form. For example, the user
clicks on a "Print Reports" button and a Form pops up. On the Form
the user can choose to get a report that shows the Box Numbers that
are at Warehouse A, Warehouse B or Warehouse C. and then click on the
Print Preview button and get a report showing only the Box Numbers in
whichever warehouse was selected.
There is a number of ways to achieve this. We build a WHERE clause and
then some code behind the report adds it to the report's RecordSource
(We have a wizard that allows the user to build filters using english
rather than the field names)

eg. Code behind form build a WHERE clause and assigns it to a global
variable then opens the selected report

Report OnOpen event

If Len(GV_REPORT_CRITERIA) > 0 then
Me.RecordSource = "SELECT * FROM (" & Me.RecordSource & ") WHERE " &
GV_REPORT_CRITERIA & ";"
End If
My project is 99% done but I just can't get this last report bit. I am
using Access 2002 but the database (for some reason) says that it is
in Access 2000 format.
AccessXP (2002 and 2003) use the Access2000 file format by default. Not
a lot is gained by using AccessXP formatted database files and it makes
it easier for clients using different versions of Office.
I've tried altering code from the Northwind
database but I got hopelessly lost in the code and got one error after
another. Thanks in advance for your help.

A simple method is to reference some fields on the form in the query
behind the report.

eg. SQL query behind report (the form remains open behind the report or
hidden)

SELECT * FROM [qryMyReportQuery] WHERE [MyField] =
[Forms]![frmReportFilter]![MyFilterValue];

Hope that gives you some ideas...
--
regards,

Bradley

[RC]

Where/how do you put the variable (intWarehouseB) that you get from
the Form into the Report?

rc*********@yahoo.com (RC) wrote in message news:<3c**************************@posting.google. com>...

I can't quite grasp the concept of creating custom reports depending
upon what options a user picks on a Form. For example, the user
clicks on a "Print Reports" button and a Form pops up. On the Form
the user can choose to get a report that shows the Box Numbers that
are at Warehouse A, Warehouse B or Warehouse C. and then click on the
Print Preview button and get a report showing only the Box Numbers in
whichever warehouse was selected.
My project is 99% done but I just can't get this last report bit. I am
using Access 2002 but the database (for some reason) says that it is
in Access 2000 format. I've tried altering code from the Northwind
database but I got hopelessly lost in the code and got one error after
another. Thanks in advance for your help.

[RC]

Thank You ! I think I now grasp the concept. Instead of passing a
variable to the report, I fill the report with data based on a query
and the query is controlled by the user input on the form. OR, on
loading the report restrict the record source to specific data with a
Where clause.
Thanks again, I will try these methods and see if I can get it to
work.

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message news:<40******@nexus.comcen.com.au>...

RC wrote:

I can't quite grasp the concept of creating custom reports depending
upon what options a user picks on a Form. For example, the user
clicks on a "Print Reports" button and a Form pops up. On the Form
the user can choose to get a report that shows the Box Numbers that
are at Warehouse A, Warehouse B or Warehouse C. and then click on the
Print Preview button and get a report showing only the Box Numbers in
whichever warehouse was selected.

There is a number of ways to achieve this. We build a WHERE clause and
then some code behind the report adds it to the report's RecordSource
(We have a wizard that allows the user to build filters using english
rather than the field names)

eg. Code behind form build a WHERE clause and assigns it to a global
variable then opens the selected report

Report OnOpen event

If Len(GV_REPORT_CRITERIA) > 0 then
Me.RecordSource = "SELECT * FROM (" & Me.RecordSource & ") WHERE " &
GV_REPORT_CRITERIA & ";"
End If

My project is 99% done but I just can't get this last report bit. I am
using Access 2002 but the database (for some reason) says that it is
in Access 2000 format.

AccessXP (2002 and 2003) use the Access2000 file format by default. Not
a lot is gained by using AccessXP formatted database files and it makes
it easier for clients using different versions of Office.

I've tried altering code from the Northwind
database but I got hopelessly lost in the code and got one error after
another. Thanks in advance for your help.

A simple method is to reference some fields on the form in the query
behind the report.

eg. SQL query behind report (the form remains open behind the report or
hidden)

SELECT * FROM [qryMyReportQuery] WHERE [MyField] =
[Forms]![frmReportFilter]![MyFilterValue];

Hope that gives you some ideas...