Susan Zeller
szeller at cce.umn.edu
Tue Feb 25 11:53:52 CST 2003
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
:
:
: 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
:
:
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com