[dba-SQLServer]Sending parameters to a report

I have also used this method successfully, but it is cumbersome.  

	-----Original Message-----
	From: Mike and Doris Manning [mailto:mikedorism at ntelos.net] 
	Sent: Tuesday, February 25, 2003 7:34 AM
	To: dba-sqlserver at databaseadvisors.com
	Subject: RE: [dba-SQLServer]Sending parameters to a report
	
	
	David,
	 
	The only way I have been able to set them through code is to
open the form or report in design view, set the parameters, close the
design view copy, and then open the form or report.  Here is the code I
use but this code will not work if the database window is hidden.
	 
	
	Public Sub PrepareFormRept(ItemName As String, Param As String,
_
	                        IsForm As Boolean, Optional prt As
Printer)
	                        
	    On Error GoTo ErrorHandler
	    
	    If IsForm = True Then
	        DoCmd.Echo False, "Preparing Form"
	        DoCmd.OpenForm ItemName, acViewDesign
	        Forms(ItemName).InputParameters = Param
	        DoCmd.Close acForm, ItemName, acSaveYes
	    Else
	        DoCmd.Echo False, "Preparing Report"
	        DoCmd.OpenReport ItemName, acViewDesign
	        Reports(ItemName).InputParameters = Param
	        If Not IsMissing(prt) And Not prt Is Nothing Then
	            Reports(ItemName).Printer = prt
	        End If
	        DoCmd.Close acReport, ItemName, acSaveYes
	    End If
	        
	    DoCmd.Echo True
	    
	    Exit Sub
	    
	ErrorHandler:
	    DoCmd.Echo True
	    Call HandleErrors(Err, strMyName, "PrepareFormRept")
	End Sub
	 
	Doris Manning
	Database Administrator
	Hargrove Inc.
	www.hargroveinc.com

		-----Original Message-----
		From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of David
Emerson
		Sent: Monday, February 24, 2003 04: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/20030225/a32df13d/attachment.html>