[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/37ba55d0/attachment.html>