Francisco H Tapia
my.lists at verizon.net
Mon Feb 24 18:13:10 CST 2003
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
:
:
: DALYN Software Ltd
:
:
: 25b Cunliffe St, Johnsonville
:
:
: Wellington, New Zealand
:
:
: Ph/Fax (877) 456-1205
:
:
:
:
:
:
: Regards
:
: David Emerson
: DALYN Software Ltd
: 25b Cunliffe St, Johnsonville
: Wellington, New Zealand
: Ph/Fax (877) 456-1205
:
: