[dba-SQLServer]Sending parameters to a report

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] 
	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 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030224/40ddb91c/attachment.html>