[AccessD] AXP ADP Report setting Recordsource to Stored Proc andpassing parameters via code

Jeff,
 
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. 
 
The way I do this is:
 
1.  Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "dbo.p_DM_NC_REG_BY_DATE_TO_EVENT_FY"
 
2.  In the input parameters box on the properties window:
 
@MyInputParameter1 = Forms!frmWiz!subcrit!txtParam_FY,
@MyInputParameter2 = Forms!frmWiz!subcrit!txtParam_Desn
 
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.
 
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.
 
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.
 
--Susan
 
 

	-----Original Message-----
	From: jeffrey.demulling at usbank.com
[mailto:jeffrey.demulling at usbank.com] 
	Sent: Wednesday, March 26, 2003 4:52 PM
	To: accessd at databaseadvisors.com
	Subject: [AccessD] AXP ADP Report setting Recordsource to Stored
Proc andpassing parameters via code
	
	

	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? 
	
	Option Compare Database 
	
	Private Sub Report_Close() 
	Forms![frmReportsWeeklyRevenue].Visible = True 
	End Sub 
	
	Private Sub Report_Open(Cancel As Integer) 
	Dim mystartdate As Date 
	Dim myenddate As Date 
	
	mystartdate =
FirstofMonth([Forms]![frmReportsWeeklyRevenue]![txtFromDate]) 
	myenddate =
CFMReportEndDate([Forms]![frmReportsWeeklyRevenue]![txtFromDate]) 
	
	Me.RecordSource = "dbo.SEL_qryClosedMTD" 
	Me.InputParameters = "@stardate=" & mystartdate & ", @enddate="
& myendate 
	
	
	varReportName = "rptWeeklyRevenueClosedMTD" 
	varDisplayName = "Weekly Revenue - Closed Month to Date" 
	
	
	DoCmd.Maximize 
	End Sub 
	
	TIA 
	-Jeff

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030327/488562a9/attachment-0001.html>