Francisco H Tapia
my.lists at verizon.net
Tue Feb 25 12:10:57 CST 2003
ARG!, I've tried testing this method and was sure that I was using it in my
production ADP, but I am not :o( I have a workaround in Access2k where I
decalre the input params into a hidden form that contains the dates and
other paramters information that I need. It seems you can set the
recordsource only as the name of the sproc, but the method works fine for a
form as I threw together a simple form that feeds from the same report sproc
and works w/o a hitch... this is most disturbing.. I had not realized this
before (or maybe I did and had a severe lapse of C.R.S.)...
-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Susan Zeller" <szeller at cce.umn.edu>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Tuesday, February 25, 2003 9:53 AM
Subject: RE: [dba-SQLServer]Sending parameters to a report
: I've not gotten Francisco's method a to work, but I don't remember why
: anymore. It seems right, but I think he means the report's
: recordsource, not the rowsource.
:
: I have successfully executed a variation on his method B. Many of my
: reports require me to take multiple selections from a listbox. I have
: an invisible text box that my report input paramenters points to and
: that text box gets populated with the string I want to pass to the
: sproc. In your case, since this is called from different forms, I think
: you'd have to go with Francisco's common form approach.
:
: --Susan
:
:
: -----Original Message-----
: From: Francisco H Tapia [mailto:my.lists at verizon.net]
: Sent: Monday, February 24, 2003 6:13 PM
: To: dba-sqlserver at databaseadvisors.com
: Subject: Re: [dba-SQLServer]Sending parameters to a report
:
:
: So I see not everything is so bright over on the XP side, This is a bug
: in ADP's one that I am hoping will be fixed for the next version of
: ADPs. The best way to solve your problem is one of 2 things... EITHER
: a) set your rowsource through code... as me.Rowsource = "EXEC
: stp_MyProcedure " & VariableContainingOpenArgs1 & ", " &
: VariableContainingOpenArgs2 remember that the syntax is SQL so if you
: are passing date or string parameters you'll also need to include the '
: single quotes.
:
: b) create a generic form that contains the values to your parameters and
: set them prior to opening your report. For some reason the sproc runs
: before the input paramters are set on the form or report...
:
: HTHs
: -Francisco
: http://rcm.netfirms.com
: ----- Original Message -----
: From: "David Emerson" <davide at dalyn.co.nz>
: To: <dba-sqlserver at databaseadvisors.com>
: Sent: Monday, February 24, 2003 3:53 PM
: Subject: RE: [dba-SQLServer]Sending parameters to a report
:
:
: : Thanks for the response. Unfortunately there are two complications
: with
: : this report -
: : 1) It could be called from two different forms with different fields
: : that are used for the parameters
: : 2) It also is used in a situation where there might be several
: customers
: : selected from a list box and copies of the report are to be printed
: for
: : all of them. In the A97 version it was easy by just including the
: where
: : clause in the openReport command but it doesn't seem as easy with SQL.
: :
: : David
: :
: : At 24/02/2003, you wrote:
: :
: :
: : David,
: :
: : I've never been successful in setting the input parameters through
: code.
: : What I do, is in the property box, under Input Parameters I reference
: : somethiung on an open form such as:
: :
: : @DeptID = Forms!frmWiz!subcrit!txtParam_Dept
: :
: : I usually set the record source for the report in the on_open of the
: : code such as:
: :
: : Me.RecordSource = "dbo.proc_DM_CCE_Phone_List_dyn"
: :
: : Seems to me that the way you describe below should work, but it's
: never
: : worked for me.
: :
: : --Susan
: :
: :
: :
: :
: : -----Original Message-----
: :
: :
: : From: David Emerson [ mailto:davide at dalyn.co.nz
: : <mailto:davide at dalyn.co.nz> ]
: :
: :
: : Sent: Monday, February 24, 2003 3:37 PM
: :
: :
: : To: dba-SQLServer at databaseadvisors.com
: :
: :
: : Subject: [dba-SQLServer]Sending parameters to a report
: :
: :
: :
: : I have a problem with a report in an AXP/SQL2K database.
: :
: :
: :
: : I have a button on a customer form which is supposed to open up a
: report
: : with just the current customers information on it. In the OpenReport
: : statement I send the inputParameters via the OpenArgs to the report
: and
: : then in the report's Open event set me.InputParameters = me.OpenArgs.
: :
: :
: : The main report sproc then uses the InputParameters to select the
: : correct record (theoretically).
: :
: :
: :
: : But I must be missing something because even though the inputparameter
: : property is being updated, the sproc is not using the information to
: : retrieve the correct data. Here is the main parts of the sproc -
: :
: :
: :
: : ALTER PROCEDURE sprptAccounts
: :
: :
: :
: : (
: :
: :
: : @txtCustID int,
: :
: :
: : @StatementNumber int
: :
: :
: : )
: :
: :
: :
: : AS
: :
: :
: : SET NOCOUNT ON
: :
: :
: :
: :
: :
: : SELECT tblCustomers.CustomerID, tblCustStatement.StatementID,
: :
: :
: : case
: :
: :
: : when [Residential]=1
: :
: :
: : then [CSurname] + [C1stName]
: :
: :
: : else [TradingName]
: :
: :
: : end
: :
: :
: : AS SortName, tblCustStatement.SMName,
: : tblCustStatement.SMAddress1, tblCustStatement.SMAddress2,
: :
: :
: : tblCustStatement.SMSuburb, tblCustStatement.SMCity,
: : tblCustStatement.SMPostCode, tblCustStatement.SAccountNo,
: :
: :
: : case
: :
: :
: : when Not ([PrevStatementDate] is null)
: :
: :
: : then
: DateAdd("d",1,[PrevStatementDate])
: :
: :
: : else [PrevStatementDate]
: :
: :
: : end
: :
: :
: : AS StatFirstDate, tblCustStatement.StatementDate,
: : tblCustStatement.StatementNumber, tblCustStatement.CurrentMth,
: :
: :
: : [OneMonth]+[TwoMonths]+[ThreeMonths] AS Overdue,
: : tblCustStatement.OneMonth, tblCustStatement.TwoMonths,
: :
: :
: : tblCustStatement.ThreeMonths,
: : tblCustStatement.ComBondBal, tblCustStatement.Processed,
: : tblCustomers.AccStatus, tblCustomers.AccFreq,
: : tblCustStatement.DDRegistered
: :
: :
: : FROM tblCustomers INNER JOIN tblCustStatement ON
: : tblCustomers.CustomerID = tblCustStatement.CustIDNo
: :
: :
: : WHERE (tblCustomers.CustomerID = @txtCustID) and
: : (tblCustStatement.StatementNumber = @StatementNumber)
: :
: :
: :
: :
: : The call to open the report is -
: :
: :
: : DoCmd.OpenReport "rptAccounts", acViewPreview, , , , "@CustID int = "
: &
: : txtCustIDNo & ", @StatementNumber int = " & txtStatementNumber
: :
: :
: :
: : An example of the openarg sent is -
: :
: :
: : @CustID int = 773500661, @StatementNumber int = 11
: :
: :
: :
: : The report's Open event is
: :
: :
: :
: : Private Sub Report_Open(Cancel As Integer)
: :
: :
: :
: : Me.InputParameters = Me.OpenArgs
: :
: :
: :
: : End Sub
: :
: :
: :
: : Am I going about this all wrong?
: :
: :
: :
: : Regards
: :
: :
: :
: : David Emerson