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 : donorbudgetadjustF

Form parameter are : CBOfiscalyear and cbocode

afterupdate control : cbocode_afterupdate

return value focus on me.txtitembalance

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.TxtiTemBalance.RowSource = "EXEC CodeBalance @fiscalYear= me.cbofiscalyear "


To this

Me.TxtiTemBalance.RowSource = "EXEC CodeBalance " & me.cbofiscalyear



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