[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]
>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 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030225/feabfc3c/attachment.html>