Opening a report only for a specific company

  • Thread starter Thread starter caro
  • Start date Start date
C

caro

I have a report based off a form where many recommendations are listed for
many different companies. Instead of opening the report and seeing all the
recommendations for all companies, I want the user to be able to type in the
company ID immediately and for the report to pull up only records for that
company. I am sure there's an easy way to do this but have completely blanked
here.
Many thanks!
 
caro,
There are many ways to accomplish that.
One method is to go to the Company record you want to report
on, and run the report from there (leaving the form open). The open form
can provide the report query with the filtering value it needs. You should
use an unique value from your record... such as CompanyID.
You can use this criteria in the report's query... (ex.)
= Forms!YourMainFormName!CompanyID

Or... you can place this criteria in the CompanyID column of your
report query...
=[Enter Company ID]
When the rpeort runs, the query will ask you for a CompanyID.
Enter any legitimate ID, and the report will be filtered by that value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
I actually do not understand the code that is written in that example at all,
which is probably sad. How would it look if I was allowed to type in the
desired report, and not just look at the last report written?
Thanks,
Caro
 
So the expression I am using is:
= DoCmd.OpenReport "Initial CIP-MAA Report", acViewPreview, , "CAGE" = &
CAGE"
The goal is to build an on-open event where a person can type in the CAGE
number to filter the report and to only show that particular report
associated with that CAGE.
Thoughts? Can I do it this way or do I have to use macros?
Thanks,
 
Get the books out and start learning - Also use the help :) but most of the
advanced things you will want to do will involve understanding VBA. Because
even your what you asked next will require you to understand the coding that
was provided. So I will give you the start...

From the access Web
1 Dim strDocName As String
2 Dim strWhere As String
3 strDocName = "rptSomeReport"
4 strWhere = "[RunID]=" & me!RunID
5 DoCmd.OpenReport strDocName, acPreview, , strWhere

1 and 2 Declare variables used
3 Assignes the name of the report - and if you want the user to be able to
put in the name of the report as you just asked - it could be a textbox on
your form (I would use a combobox but will keep it simple) you would have
strDocName = me.txtReportName
Me refers to the form that you are on the txtReportName is the name of the
textbox on the form
4 For you something like - strWhere = "[CompanyID]=" & me!CompanyID
The [CompanyID]= is the name from your query in the report or table if
based on that - againd the me!companyID - is a combo box or text box on your
form - this is designed to limit it to the company id that you wan't
5 DoCmd.OpenReport strDocName, acPreview, , strWhere this is were you open
the report obviously - you have the document name then opeing it as a preview
instead of sending it straight to the printer then you have a blank variable
USE THE Help if you want to know what this is and then there is the limiting
string.

As stated in the example this code can goes into a command buttton's On
Click Event. (a property under the Event tab for the button - make sure you
use Expression Builder and you can also set this as the default when you
click the elipse (...).

You can even take a shortcut to start you off with using the wizard to put a
button on the form to open a report. This will start you off but then you
need to understand the code to change it to what you want to do.

Hope that gets you started.

Craig
 
Typically you would have a command button to open the report. This can be
created with the command button wizard to write the OpenReport code for you.

Then you should have a cage field value on the form or add a combo box that
allows your cage. Consider naming your combo box "cboCage". Then, modify the
code as per the solution I recommended on the web page resource. Your string
"strWhere" might look like
'assuming the CageField is numeric:
strWhere = "[CageField]=" & Me.cboCage

'If the CageField is Text:
strWhere = "[CageField]=""" & Me.cboCage & """"
 
Back
Top