<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1141" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=942103113-25022003><FONT face=Arial color=#0000ff
size=2>David,</FONT></SPAN></DIV>
<DIV><SPAN class=942103113-25022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=942103113-25022003><FONT face=Arial color=#0000ff size=2>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></SPAN></DIV>
<DIV><SPAN class=942103113-25022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=942103113-25022003><FONT face=Arial color=#0000ff
size=2><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</DIV>
<DIV> </DIV>
<DIV></FONT></SPAN>
<DIV align=left><FONT face=Arial size=2>Doris Manning</FONT></DIV>
<DIV align=left><FONT face=Arial size=2>Database Administrator</FONT></DIV>
<DIV align=left><FONT face=Arial size=2>Hargrove Inc.</FONT></DIV>
<DIV align=left><FONT face=Arial color=#0000ff
size=2><U>www.hargroveinc.com</U></FONT></DIV></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
dba-sqlserver-admin@databaseadvisors.com
[mailto:dba-sqlserver-admin@databaseadvisors.com] <B>On Behalf Of </B>David
Emerson<BR><B>Sent:</B> Monday, February 24, 2003 04:37 PM<BR><B>To:</B>
dba-SQLServer@databaseadvisors.com<BR><B>Subject:</B> [dba-SQLServer]Sending
parameters to a report<BR><BR></FONT></DIV>I have a problem with a report in
an AXP/SQL2K database.<BR><BR>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> The main report sproc then uses the
InputParameters to select the correct record (theoretically).<BR><BR>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><FONT
face=Tahoma>ALTER PROCEDURE
sprptAccounts<BR><BR><X-TAB> </X-TAB>(<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>@txtCustID
int,<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>@StatementNumber
int<BR><X-TAB> </X-TAB>)<BR><BR>AS<BR><X-TAB> </X-TAB>
SET NOCOUNT ON
<BR><X-TAB> </X-TAB>
<BR><X-TAB> </X-TAB>SELECT
tblCustomers.CustomerID, tblCustStatement.StatementID,
<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>case<BR><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB>when
[Residential]=1<BR><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB>then
[CSurname] +
[C1stName]<BR><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB>else
[TradingName]<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>end<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>AS
SortName, tblCustStatement.SMName, tblCustStatement.SMAddress1,
tblCustStatement.SMAddress2,
<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>tblCustStatement.SMSuburb,
tblCustStatement.SMCity, tblCustStatement.SMPostCode,
tblCustStatement.SAccountNo,
<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>case<BR><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB>when
Not ([PrevStatementDate] is
null)<BR><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB>then
DateAdd("d",1,[PrevStatementDate])<BR><X-TAB> </X-TAB><X-TAB> </X-TAB><X-TAB> </X-TAB>else
[PrevStatementDate]<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>end<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>AS
StatFirstDate, tblCustStatement.StatementDate,
tblCustStatement.StatementNumber, tblCustStatement.CurrentMth,
<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>[OneMonth]+[TwoMonths]+[ThreeMonths]
AS Overdue, tblCustStatement.OneMonth, tblCustStatement.TwoMonths,
<BR><X-TAB> </X-TAB><X-TAB> </X-TAB>tblCustStatement.ThreeMonths,
tblCustStatement.ComBondBal, tblCustStatement.Processed,
tblCustomers.AccStatus, tblCustomers.AccFreq,
tblCustStatement.DDRegistered<BR><X-TAB> </X-TAB>FROM
tblCustomers INNER JOIN tblCustStatement ON tblCustomers.CustomerID =
tblCustStatement.CustIDNo<BR><X-TAB> </X-TAB>WHERE
(tblCustomers.CustomerID = @txtCustID) and (tblCustStatement.StatementNumber =
@StatementNumber)<BR><BR><BR></FONT>The call to open the report is
-<BR> DoCmd.OpenReport "rptAccounts", acViewPreview, , , , "@CustID int =
" & txtCustIDNo & ", @StatementNumber int = " &
txtStatementNumber<BR><BR>An example of the openarg sent is -<BR>@CustID int =
773500661, @StatementNumber int = 11<BR><BR>The report's Open event
is<BR><BR>Private Sub Report_Open(Cancel As Integer)<BR><BR>
Me.InputParameters = Me.OpenArgs<BR><BR>End Sub<BR><BR>Am I going about this
all wrong? <BR><X-SIGSEP>
<P></X-SIGSEP>Regards<BR><BR>David Emerson<BR>DALYN Software Ltd<BR>25b
Cunliffe St, Johnsonville<BR>Wellington, New Zealand<BR>Ph/Fax (877) 456-1205
</P></BLOCKQUOTE></BODY></HTML>