Problem with Returning value from Parametrized Sp

[sknaina]

Hi, I Am New in Access arena and moderate knowledge of Sql 2000 Server
Now , I'm trying to Buildup an Application with Access2003 ADP & Sql 2000 Server. Over there I create a stored procedure to return balance calculation
My Sp performs okay When I run this via Query analyzer. Problem is I can't fired this Stored procedure on Forms After update event
any one help me to solve the problem ........

Here is the Particulars

Unbound Form Name : donorbudgetadju stF

Form parameter are : CBOfiscalyear and cbocode

afterupdate control : cbocode_afterup date

return value focus on me.txtitembalan ce

I have try to overcome but failed !

Private Sub CboCode_AfterUpdate(Cancel As Integer)
Me.TxtiTemBalance.RowSource = "EXEC CodeBalance @fiscalYear= me.cbofiscalyear "
Me.TxtiTemBalance.Requery
End Sub

Below I Post my Sp

CREATE PROCEDURE DBO.CodeBalance
	
	@FiscalYear AS INT,
	@Itemcode  AS INT
	
AS

SET NOCOUNT ON
--SET ANSI_NULLS ON
--SET QUOTED_IDENTIFIER ON
	
	BEGIN
	

	DECLARE @inflow INT
	DECLARE @outflow INT
	DECLARE @credit INT
	DECLARE @debit INT

	SET  @inflow=(SELECT ISNULL(SUM(Amount),0) 
								FROM FisCalYearDonorBudgetT 
									WHERE FisCalYear=@FiscalYear 
										AND DACode=@Itemcode)

	SET  @outflow=(SELECT ISNULL(SUM(Amount),0) 
								FROM DonorTranSmasterT 
									WHERE FisCalYear=@FiscalYear 
										AND ItemiD=@Itemcode)

	SET  @credit=(SELECT ISNULL(SUM(Credit),0) 
								FROM DonorBudgetAdjustT 
									WHERE FisCalYear=@FiscalYear 
										AND ItemCr=@Itemcode)

	SET  @debit=(SELECT ISNULL(SUM(Debit),0) 
								FROM DonorBudgetAdjustT 
									WHERE FisCalYear=@FiscalYear 
										AND ItemDr=@Itemcode)

	SELECT (((@inflow-@outflow)-@credit)+@debit) AS CodeBalance
END
GO

............... <naina>........ .........

[Delerna]

Try changing this line on your form

Me.TxtiTemBalan ce.RowSource = "EXEC CodeBalance @fiscalYear= me.cbofiscalyea r "


To this

Me.TxtiTemBalan ce.RowSource = "EXEC CodeBalance " & me.cbofiscalyea r



also your stored procedure has two parameters
you must also provide a value for it .... @Itemcode

with Me.TxtiTemBalance
   .RowSource = "EXEC CodeBalance  " _
              & me.cbofiscalyear & "," & ItemCodeValue
end with

[sknaina]

Dear Delerna,


Now it works fine.
Thanks a lot for your great solution!


Naina

[Delerna]

You are very welcome