This is for a reporting problem using:
Access 2000
SQL Server 2000
Both at SP3
I've a stored procedure in SQL Server 2000 which builds a result set
from a disparate set of tables, utilizing a temp table. The procedure
takes two parameters to specify the criteria on selecting the table
information for inclusion in the result set, builds the temp table,
and the returns the rows.
I intend on using the result set in a report in an ADP, and eventually
and ADE.
I want to be able to, at execution time, have the report pass the two
parameters to the stored procedure during "OnOpen" and then process
the result set.
When designing the report, I get the message:
Microsoft Access can't find the object 'Microsoft Access can't find
the object 'procedure name''
According to the information I've been able to find, because the
procedure must be executed to develop the result set, this is normal
processing and one should simply type in the column names.
And indeed, the report works perfectly if I place the stored procedure
name and call parameters in the report properties (RecordSource and
InputParameters ) and then run the report.
However, I've been unable to pass the parameters at run time.
Per a Microsoft article, InputParameters does not work at runtime and,
by golly, they don't.
But the articles and news group postings imply that appending the call
parameters to the RecordSource property (either named or positional)
will do the job.
But all I get is "Microsoft Access can't find the object 'Microsoft
Access can't find the object 'procedure name parm1 parm2'' or prompts
for the two parameters.
Regardless of the format. Even if the format that works fine when
hardcoded in the report's properties RecordSource and InputParameters .
I gotta believe there's a way to do this.
Does anyone have a working piece of code where they pass parameters to
a stored procedure from a report at runtime in an adp? 8 9868
Mark Flippin <me******@comca st.net> wrote in
news:q5******** *************** *********@4ax.c om: Per a Microsoft article, InputParameters does not work at runtime and, by golly, they don't.
When would they work?
Try
Set the inputparameters property to = SomePublicFunct ionInAStandardM odule()
Program the function to return a string of the parameters you want to use as:
@ParameterName int=32, @OtherParamterN ame varchar(50)='Bi zarro'
--
Lyle
--
use iso date format: yyyy-mm-dd http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
On 14 Sep 2004 02:36:33 GMT, Lyle Fairfield <Lo******@FFDBA .Com>
wrote:
Bizarre: the spelling error is at the 34th character.
-Tom. @ParameterNa me int=32, @OtherParamterN ame varchar(50)='Bi zarro'
Why not write the values of the input parameters into unbound text fields on
a hidden form at run time and have your report's input parameter refer to
the controls' properties? Alternately, launch the report from this form to
ensure that it is open.
@Parm1 = Forms!frmHidden !Parm1, @Parm2 = Forms!frmHidden !Parm2
Alternately, launch the report from this form to ensure that it is open.
Hope this helps.
Randy Shore
"Mark Flippin" <me******@comca st.net> wrote in message
news:q5******** *************** *********@4ax.c om... This is for a reporting problem using: Access 2000 SQL Server 2000
Both at SP3
I've a stored procedure in SQL Server 2000 which builds a result set from a disparate set of tables, utilizing a temp table. The procedure takes two parameters to specify the criteria on selecting the table information for inclusion in the result set, builds the temp table, and the returns the rows.
I intend on using the result set in a report in an ADP, and eventually and ADE.
I want to be able to, at execution time, have the report pass the two parameters to the stored procedure during "OnOpen" and then process the result set.
When designing the report, I get the message:
Microsoft Access can't find the object 'Microsoft Access can't find the object 'procedure name''
According to the information I've been able to find, because the procedure must be executed to develop the result set, this is normal processing and one should simply type in the column names.
And indeed, the report works perfectly if I place the stored procedure name and call parameters in the report properties (RecordSource and InputParameters ) and then run the report.
However, I've been unable to pass the parameters at run time.
Per a Microsoft article, InputParameters does not work at runtime and, by golly, they don't.
But the articles and news group postings imply that appending the call parameters to the RecordSource property (either named or positional) will do the job.
But all I get is "Microsoft Access can't find the object 'Microsoft Access can't find the object 'procedure name parm1 parm2'' or prompts for the two parameters.
Regardless of the format. Even if the format that works fine when hardcoded in the report's properties RecordSource and InputParameters .
I gotta believe there's a way to do this.
Does anyone have a working piece of code where they pass parameters to a stored procedure from a report at runtime in an adp?
Nope, that don't work. Access just complains about the number of
parameters.
The only method I've been able to get to work is to have the
parameters as text boxes on an existing, open form.
In which case, you hardcode in the report's "InputParameter s"
=forms!form1.tb xReportParm1,fo rms!form1.tbxRe portParm2
Oh, by the way, you have to hardcode the procedure name in the
report's "RecordSour ce" property or that don't work either. If you
provide the procedure name in the "onOpen" event, you get the
parameter prompts.
Interestingly enough, you can modify the contents of these text boxes
in the report's "onOpen" event and have it take. Which is kinda weird,
trying to comprehend the timing of the events. Why can I change a text
box in the parm string, yet not supply the parm directly?
This will work, but it seems ugly
Mark
On 14 Sep 2004 02:36:33 GMT, Lyle Fairfield <Lo******@FFDBA .Com>
wrote: Try
Set the inputparameters property to = SomePublicFunct ionInAStandardM odule()
Program the function to return a string of the parameters you want to use as:
@ParameterNa me int=32, @OtherParamterN ame varchar(50)='Bi zarro'
Mark Flippin <me******@comca st.net> wrote in
news:t2******** *************** *********@4ax.c om: Nope, that don't work. Access just complains about the number of parameters.@ParameterNam e int=32, @OtherParamterN ame varchar(50)='Bi zarro'
Actually it does work when it is implemented correctly.
--
Lyle
--
use iso date format: yyyy-mm-dd http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
OK, I'm game.
I call bull****
Please provide the correct implementation, including the stored
procedure definition and project references.
On 15 Sep 2004 02:36:47 GMT, Lyle Fairfield <Lo******@FFDBA .Com>
wrote: Actually it does work when it is implemented correctly. Lyle
Randy,
That's exactly what I've decided to do (option 2, launch the report
frm the form to ensrue it's open). This works fine for the current
project.
Mark
On Tue, 14 Sep 2004 09:23:57 -0700,
"vtashore@Ta*** ******@adelphia .net" <vt******@adelp hia.net> wrote: Why not write the values of the input parameters into unbound text fields on a hidden form at run time and have your report's input parameter refer to the controls' properties? Alternately, launch the report from this form to ensure that it is open.
@Parm1 = Forms!frmHidden !Parm1, @Parm2 = Forms!frmHidden !Parm2
Alternately, launch the report from this form to ensure that it is open.
Hope this helps. Randy Shore
I found a lot of misunderstandin g in this thread in the sense of "this
works" or "this does not work". I work with Access ADP 2000 plus SQL
Server. Let me give some hints:
- I could use statements like: rptActive.Recor dSource =
"dbo.procMyRepo rt".
Just do not state it in the Report_Open, but in the Report_Activate
event.
- I can stretch this idea a little bit further (but won't be too
specific):
"Always try the Report_Activate event (and NOT the Report_Open) for
data processing".
- It is correct to have input parameter specifications like:
"@Parm1 = Forms!frmHidden !Parm1, @Parm2 = Forms!frmHidden !Parm2".
Still, I think this is an awkward kind of programming. I made a Public
property and had a parameter specification like: "@Parm1 =
Forms!frmMyForm !MyProperty". What's the advantage:
- I do not need an additional form to reach my goal.
- Neither do I need an additional control somewhere. Make it a
property, that's a more parsimoneous (lightweight) approach.
- The above parameter specification works for a public property on an
open form, it dis not work for a public property in a standard module
(sorry, did not test yet for a property in a class).
Let me provide some more information. The most informative source for
this kind of questions is the widely-acclaimed Chipman and Byron
(2001). "Microsoft Access Developer's Guide to SQL Server". In their
chapter 12 on reports, they describe a few techniques that don't work
but should. I did not check every detail, but I found a few of their
non-working techniques working for me (in ADP as well as in ADE). My
guess for now: "It's the Report_Activate event that makes the
difference".
Leendert van Staalduinen,
van Staalduinen Data Management bv.
Mark Flippin <me******@comca st.net> wrote in message news:<71******* *************** **********@4ax. com>... Randy,
That's exactly what I've decided to do (option 2, launch the report frm the form to ensrue it's open). This works fine for the current project.
Mark
On Tue, 14 Sep 2004 09:23:57 -0700, "vtashore@Ta*** ******@adelphia .net" <vt******@adelp hia.net> wrote:
Why not write the values of the input parameters into unbound text fields on a hidden form at run time and have your report's input parameter refer to the controls' properties? Alternately, launch the report from this form to ensure that it is open.
@Parm1 = Forms!frmHidden !Parm1, @Parm2 = Forms!frmHidden !Parm2
Alternately, launch the report from this form to ensure that it is open.
Hope this helps. Randy Shore This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come from the same database. So in this
case, I will have a main report and a subreport. I've already tried...
|
by: lauren quantrell |
last post by:
When I open an Access form I can have no recordset specified, then in
the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"
But I can't do this in a report as it will prompt me for the
parameters, even though they seem to be defined ahead of the
recordsource.
I have worked around this by opening reports to a bogus recordsource
|
by: Bob |
last post by:
I'm new to Access projects and SQL server and am not a veteran VB
programmer. There's a cry for help! I'm attempting to print the
current form on screen by using a command button which the user clicks
once they have selected the desired record. The button calls a report
which uses a stored procedure as its record source. The SP has 2 input
parameters, one of which is a datetime data type. In the input
parameters data field of the...
|
by: Bill Nguyen |
last post by:
What's the VB syntax to run the CR report using the following SP?
I use CrystalreportViewer and ReportDocument.
Thanks
Bill
Here's the SP in SQLserver 2K:
CREATE proc mysp_ReportSubmission
@salesdate as varchar(20),
|
by: Dean Slindee |
last post by:
I would like to pass multiple parameters from a VB.NET application to an
existing Access 2003 application's reports (essentially using Access as a
report writer where the reports already exist).
The reports' datasource is a stored procedure with multiple @parameters.
This code works where the report requires no parameter:
Dim strAccessDBPath As String
Dim booReturn As Boolean = False
strReportName = "rptCMOTeam1"
strAccessDBPath =...
| |
by: Dean Slindee |
last post by:
I would like to pass multiple parameters from a VB.NET application to an
existing Access 2003 application's reports (essentially using Access as a
report writer where the reports already exist).
The reports' datasource is a stored procedure with multiple @parameters.
This code works where the report requires no parameter:
Dim strAccessDBPath As String
Dim booReturn As Boolean = False
strReportName = "rptCMOTeam1"
strAccessDBPath =...
|
by: Mariana |
last post by:
Hello,
I am exporting reports in pdf format from .net 2005 . The export is working fine; however the user values of stored procedure parameters does not show in the report. What do I need to do in order to see the parameters value in the report?
The report has a report parameter "reportTitle" and i see the value in the report.
It has also another 2 stored procedure parameters @BeginDate and @EndDate.
The values are entered be the user;...
|
by: Andy |
last post by:
Hi guys
I having a problem creating a report in Access 2003 project talking to
a SQL database through and ODBC connect.
After hours of trying things from Access Help, MSDN and Google I still
can't get it working.
I have a query defined (view) and want the end user to put in a start
date and end date to filter a report.
|
by: Oko |
last post by:
I'm currently developing an MS Access Data Project (.adp) in MS Access
2002.
One of the reports within the DB uses data that is Dynamic and cannot
be stored on the SQL Server. To resolve this, I have created an
ADODB.Recordset in the reports OPEN event, built the necessary records
inside of it, and then bound the report to this newly created
recordset.
Here's the rub:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |