<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1126" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=633155517-25022003><FONT face=Arial color=#0000ff size=2>I have
also used this method successfully, but it is cumbersome.
</FONT></SPAN></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> Mike and Doris
Manning [mailto:mikedorism@ntelos.net] <BR><B>Sent:</B> Tuesday, February 25,
2003 7:34 AM<BR><B>To:</B>
dba-sqlserver@databaseadvisors.com<BR><B>Subject:</B> RE:
[dba-SQLServer]Sending parameters to a report<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BODY></HTML>