<html>
<body>
Thanks for the response. Unfortunately there are two complications
with this report -<br>
1) It could be called from two different forms with different fields that
are used for the parameters<br>
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.<br><br>
David<br><br>
At 24/02/2003, you wrote:<br>
<blockquote type=cite class=cite cite><font face="arial" size=2 color="#0000FF">David,</font><br>
<br>
<font face="arial" size=2 color="#0000FF">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:</font><br>
<br>
<font face="arial" size=2 color="#0000FF">@DeptID =
Forms!frmWiz!subcrit!txtParam_Dept</font><br>
<br>
<font face="arial" size=2 color="#0000FF">I usually set the record source
for the report in the on_open of the code such as:</font><br>
<br>
<font face="arial" size=2 color="#0000FF">Me.RecordSource =
"dbo.proc_DM_CCE_Phone_List_dyn"</font><br>
<br>
<font face="arial" size=2 color="#0000FF">Seems to me that the way you
describe below should work, but it's never worked for me.
</font><br>
<br>
<font face="arial" size=2 color="#0000FF">--Susan</font><br>
<br>
<br>
<dl>
<dd><font face="tahoma" size=2>-----Original Message-----<br>
<dd>From:</b> David Emerson
[<a href="mailto:davide@dalyn.co.nz" eudora="autourl">mailto:davide@dalyn.co.nz</a>]
<br>
<dd>Sent:</b> Monday, February 24, 2003 3:37 PM<br>
<dd>To:</b> dba-SQLServer@databaseadvisors.com<br>
<dd>Subject:</b> [dba-SQLServer]Sending parameters to a report<br><br>
</font>
<dd>I have a problem with a report in an AXP/SQL2K database.<br><br>
<dd>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.<br>
<dd> The main report sproc then uses the InputParameters to select
the correct record (theoretically).<br><br>
<dd>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 -<br><br>
<dd><font face="tahoma">ALTER PROCEDURE sprptAccounts<br><br>
<dd><x-tab> </x-tab>(<br>
<dd><x-tab> </x-tab><x-tab> </x-tab>@txtCustID
int,<br>
<dd><x-tab> </x-tab><x-tab> </x-tab>@StatementNumber
int<br>
<dd><x-tab> </x-tab>)<br><br>
<dd>AS<br>
<dd><x-tab> </x-tab>SET
NOCOUNT ON <br>
<dd><x-tab> </x-tab><br>
<dd><x-tab> </x-tab>SELECT
tblCustomers.CustomerID, tblCustStatement.StatementID, <br>
<dd><x-tab> </x-tab><x-tab> </x-tab>case<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>when
[Residential]=1<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>then
[CSurname] + [C1stName]<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>else
[TradingName]<br>
<dd><x-tab> </x-tab><x-tab> </x-tab>end<br>
<dd><x-tab> </x-tab><x-tab> </x-tab>AS
SortName, tblCustStatement.SMName, tblCustStatement.SMAddress1,
tblCustStatement.SMAddress2, <br>
<dd><x-tab> </x-tab><x-tab> </x-tab>tblCustStatement.SMSuburb,
tblCustStatement.SMCity, tblCustStatement.SMPostCode,
tblCustStatement.SAccountNo, <br>
<dd><x-tab> </x-tab><x-tab> </x-tab>case<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>when
Not ([PrevStatementDate] is null)<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>then
DateAdd("d",1,[PrevStatementDate])<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>else
[PrevStatementDate]<br>
<dd><x-tab> </x-tab><x-tab> </x-tab>end<br>
<dd><x-tab> </x-tab><x-tab> </x-tab>AS
StatFirstDate, tblCustStatement.StatementDate,
tblCustStatement.StatementNumber, tblCustStatement.CurrentMth, <br>
<dd><x-tab> </x-tab><x-tab> </x-tab>[OneMonth]+[TwoMonths]+[ThreeMonths]
AS Overdue, tblCustStatement.OneMonth, tblCustStatement.TwoMonths, <br>
<dd><x-tab> </x-tab><x-tab> </x-tab>tblCustStatement.ThreeMonths,
tblCustStatement.ComBondBal, tblCustStatement.Processed,
tblCustomers.AccStatus, tblCustomers.AccFreq,
tblCustStatement.DDRegistered<br>
<dd><x-tab> </x-tab>FROM
tblCustomers INNER JOIN tblCustStatement ON tblCustomers.CustomerID =
tblCustStatement.CustIDNo<br>
<dd><x-tab> </x-tab>WHERE
(tblCustomers.CustomerID = @txtCustID) and
(tblCustStatement.StatementNumber = @StatementNumber)<br><br>
<br>
</font>
<dd>The call to open the report is -<br>
<dd> DoCmd.OpenReport "rptAccounts", acViewPreview, , , ,
"@CustID int = " & txtCustIDNo & ",
@StatementNumber int = " & txtStatementNumber<br><br>
<dd>An example of the openarg sent is -<br>
<dd>@CustID int = 773500661, @StatementNumber int = 11<br><br>
<dd>The report's Open event is<br><br>
<dd>Private Sub Report_Open(Cancel As Integer)<br><br>
<dd> Me.InputParameters = Me.OpenArgs<br><br>
<dd>End Sub<br><br>
<dd>Am I going about this all wrong? <br><br>
<dd>Regards<br><br>
<dd>David Emerson<br>
<dd>DALYN Software Ltd<br>
<dd>25b Cunliffe St, Johnsonville<br>
<dd>Wellington, New Zealand<br>
<dd>Ph/Fax (877) 456-1205 <br>
</blockquote>
<x-sigsep><p></x-sigsep>
</dl>Regards<br><br>
David Emerson<br>
DALYN Software Ltd<br>
25b Cunliffe St, Johnsonville<br>
Wellington, New Zealand<br>
Ph/Fax (877) 456-1205</body>
</html>