Access Projects: How to reference form input parms using a report

  • Thread starter Thread starter Keven Lonesome
  • Start date Start date
K

Keven Lonesome

Ladies & Gentlemen,

I'm trying my darnest to capture form input parameters
into my report selection criteria using Access projects.
using the syntax:
[FORMs]!Me!entry.date within Access reporting
is not accepted.

Note: I do not want to create a SP that embed its
parameters within the code to produce the report.
Instead, I want to capture the Form's parameters
to populate the criteria for the report.
(Some of my forms contain entering 3 to 5
parameters, most with date ranges)

Question: 1. Are there any conventional methods meeting
my requests
2. Do I have to create
@parameters within my SP report every time
to populate its report criteria?
(This method sounds real corny!)

Your correspondence will be highly appreciable!

Thanx.
 
While I don't use ADPs, I do use pass-through queries to SQL Server all the
time. What I do is dynamically regenerate the SQL to hard-code the values
into it, then update the SQL property of the query.
 
The problem is that putting direct forms refs in your sql was always a bad
practice anyway.

First, putting forms refs in the sql makes it very ugly to read...harder to
maintain. Further, it means that the sql is now attached to ONE form that
MUST BE opened. That means you can't use the sql anywhere else.

Further, if you ever migrate your system to sql server, or a dap
project...then again having a direct forms ref n the sql don't make senese.
i
I mean, the database server might be in new York..and you are working in
LA.....all of a sudden, that sql server has to find some desktop sitting in
LA..and try and ref the form.

So, as a general design practice and rule..I never EVER attempt to place
forms refs in the sql. I am sure I could write a few more pages as to more
problems...and just generally difficulties that tying a query to a
PARTICULAR form.

What is he solution?

The solution is to simply use the where clause of the form, or report hat
you are opening. This gives you FAR more flexibility..and and solves
problems like when you want to have several parameters...but leave some
blank.

Lets assume we have a report ...and we need to prompt the user for the
SalesRep...and also the Location. We would build a nice un-bound prompt
form.

dim strWhere as string

if isnull(me.cboSalesRep) = false then
strWhere = "SalesRep = " & me.cboSalesRep
end if

if isnull(me.cboSalesLocation) = false then
if strWhere <> "" then
strWhere = strWhere " and "
end if
strWhere = strWhere & "SalesLocation = " & me.cboSalesRep
end if

Note that you can continue the above process for as many conditions as you
want. You then launch the report with:

docmd.OpenReport "theReportName",acViewPreview,,strWhere

So, we get:

* Nice clean sql..with no funny forms refs (Oracle, sq-server, Sybase,
MySql. etc etc etc don't support such funny sql anyway...you would do well
to avoided this too!).

* Nice clean sql that is NOT attached to a particular form. That mans the
report, or the sql can be used..and not some form that happens to NOT be
open will cause a program failure.

* Nice clean sql that can be used in MORE THEN ONE report. Since the sql
is now not attached to a particular form, then you can free to use the sql
for the report many times..and not have to worry that it has some special
form opened.

* As mentioned, you can easily get around the problem where sometimes the
user does NOT want to enter parameters. Take a look at the following screen
shots...note how often I give the users the instructed "blank = all". So, if
the user does NOT enter some value..then it default to all (trying to
accomplish that with a direct forms ref is a real pain).

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html


* The approach works with mde, mdb, and adp projects. In fact, the
approach will work even if your back end server is oracle..and you link via
odbc. So, you get total freedom as to why kind of database engine you will
use.
 
ADP is kind of different from MD
what you need to do is in the recordsource, key in something lik
Select * from customer where customerID =
then under the input parameters, key i

customerid varchar(10) = forms!Me!entry.I

Edmun
MCP - Access and SQL Serve

----- Keven Lonesome wrote: ----

Ladies & Gentlemen

I'm trying my darnest to capture form input parameter
into my report selection criteria using Access projects
using the syntax
[FORMs]!Me!entry.date within Access reportin
is not accepted

Note: I do not want to create a SP that embed it
parameters within the code to produce the report
Instead, I want to capture the Form's parameter
to populate the criteria for the report
(Some of my forms contain entering 3 to
parameters, most with date ranges

Question: 1. Are there any conventional methods meetin
my requests
2. Do I have to creat
@parameters within my SP report every tim
to populate its report criteria
(This method sounds real corny!

Your correspondence will be highly appreciable

Thanx
 
Back
Top