<html>
<body>
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.<br><br>
David<br><br>
At 25/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">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.</font><br>
<br>
<font face="arial" size=2 color="#0000FF"><br>
Public Sub PrepareFormRept(ItemName As String, Param As String, _<br>
IsForm As Boolean, Optional prt As Printer)<br>
<br>
On Error GoTo ErrorHandler<br>
<br>
If IsForm = True Then<br>
DoCmd.Echo False,
"Preparing Form"<br>
DoCmd.OpenForm ItemName,
acViewDesign<br>
Forms(ItemName).InputParameters = Param<br>
DoCmd.Close acForm, ItemName,
acSaveYes<br>
Else<br>
DoCmd.Echo False,
"Preparing Report"<br>
DoCmd.OpenReport ItemName,
acViewDesign<br>
Reports(ItemName).InputParameters = Param<br>
If Not IsMissing(prt) And Not
prt Is Nothing Then<br>
Reports(ItemName).Printer = prt<br>
End If<br>
DoCmd.Close acReport,
ItemName, acSaveYes<br>
End If<br>
<br>
DoCmd.Echo True<br>
<br>
Exit Sub<br>
<br>
ErrorHandler:<br>
DoCmd.Echo True<br>
Call HandleErrors(Err, strMyName,
"PrepareFormRept")<br>
End Sub<br>
<br>
</font><font face="arial" size=2>Doris Manning</font><br>
<font face="arial" size=2>Database Administrator</font><br>
<font face="arial" size=2>Hargrove Inc.</font><br>
<font face="arial" size=2 color="#0000FF"><u><a href="http://www.hargroveinc.com/" eudora="autourl">www.hargroveinc.com</a></u></font><br>
<dl>
<dd><font face="tahoma" size=2>-----Original Message-----<br>
<dd>From:</b> dba-sqlserver-admin@databaseadvisors.com
[<a href="mailto:dba-sqlserver-admin@databaseadvisors.com" eudora="autourl">mailto:dba-sqlserver-admin@databaseadvisors.com</a>]
On Behalf Of </b>David Emerson<br>
<dd>Sent:</b> Monday, February 24, 2003 04: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>
</dl></blockquote></body>
</html>