<!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=086505315-27032003><FONT face=Arial color=#0000ff
size=2>Jeff,</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff size=2>The
method you are trying will not work although I no longer remember why
exactly. You need to set the input parameters in the properties window
directly. </FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff size=2>The
way I do this is:</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2>1. Private Sub Report_Open(Cancel As Integer)<BR>Me.RecordSource =
"dbo.p_DM_NC_REG_BY_DATE_TO_EVENT_FY"</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2>2. In the input parameters box on the properties
window:</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2>@MyInputParameter1 = Forms!frmWiz!subcrit!txtParam_FY, @MyInputParameter2
= Forms!frmWiz!subcrit!txtParam_Desn</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff size=2>where
the parmeters get their value from a textbox on the form that calls the
report. In my case these are hidden textboxes because I take what the user
has chosen and format it as needed so that it passes correctly to my
sproc.</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2>Optional: In the report, set the control source in the properties
window to teh base table so that when you are building the sproc, you can see
the fields in the field list. Otherwise, you can set it to the sproc or
leave it blank.</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff size=2>Note,
that you can't set the recordsource and input parameters from the calling form
b/c these can only be set when the report is open in design view. There is
a work around to this where thorugh code you open the report in design view,
assign the sproc and the parameters, save the report, and close the
report. then in code open the report again. I found this way too
cumbersome so eventually rejected it.</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2>--Susan</FONT></SPAN></DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=086505315-27032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE 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>
jeffrey.demulling@usbank.com [mailto:jeffrey.demulling@usbank.com]
<BR><B>Sent:</B> Wednesday, March 26, 2003 4:52 PM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> [AccessD] AXP ADP Report
setting Recordsource to Stored Proc andpassing parameters via
code<BR><BR></FONT></DIV><BR><FONT face=sans-serif size=2>One more question.
How can I get the code below to work so that I can set the recordsource
of the report to the referened stored proc and pass it the parameters that are
required?</FONT> <BR><BR><FONT face=sans-serif size=2>Option Compare
Database</FONT> <BR><BR><FONT face=sans-serif size=2>Private Sub
Report_Close()</FONT> <BR><FONT face=sans-serif
size=2>Forms![frmReportsWeeklyRevenue].Visible = True</FONT> <BR><FONT
face=sans-serif size=2>End Sub</FONT> <BR><BR><FONT face=sans-serif
size=2>Private Sub Report_Open(Cancel As Integer)</FONT> <BR><FONT
face=sans-serif size=2>Dim mystartdate As Date</FONT> <BR><FONT
face=sans-serif size=2>Dim myenddate As Date</FONT> <BR><BR><FONT
face=sans-serif size=2>mystartdate =
FirstofMonth([Forms]![frmReportsWeeklyRevenue]![txtFromDate])</FONT> <BR><FONT
face=sans-serif size=2>myenddate =
CFMReportEndDate([Forms]![frmReportsWeeklyRevenue]![txtFromDate])</FONT>
<BR><BR><FONT face=sans-serif size=2>Me.RecordSource =
"dbo.SEL_qryClosedMTD"</FONT> <BR><FONT face=sans-serif
size=2>Me.InputParameters = "@stardate=" & mystartdate & ", @enddate="
& myendate</FONT> <BR><BR><BR><FONT face=sans-serif size=2>varReportName =
"rptWeeklyRevenueClosedMTD"</FONT> <BR><FONT face=sans-serif
size=2>varDisplayName = "Weekly Revenue - Closed Month to Date"</FONT>
<BR><BR><BR><FONT face=sans-serif size=2>DoCmd.Maximize</FONT> <BR><FONT
face=sans-serif size=2>End Sub</FONT> <BR><BR><FONT face=sans-serif
size=2>TIA</FONT> <BR><FONT face=sans-serif
size=2>-Jeff</FONT></BLOCKQUOTE></BODY></HTML>