[dba-SQLServer]Sending parameters to a report - Thanks

Thanks everyone for your input.  It looks like I will need to run with the 
hidden form method as the database will be a runtime with the database 
window hidden.

David

At 25/02/2003, you wrote:
>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/20030226/1e3ae724/attachment.html>