By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,159 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
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...

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.