By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

Problem with Returning value from Parametrized Sp

P: 11
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 !
Expand|Select|Wrap|Line Numbers
  1. Private Sub CboCode_AfterUpdate(Cancel As Integer)
  2. Me.TxtiTemBalance.RowSource = "EXEC CodeBalance @fiscalYear= me.cbofiscalyear "
  3. Me.TxtiTemBalance.Requery
  4. End Sub

Below I Post my Sp
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE DBO.CodeBalance
  2.  
  3.     @FiscalYear AS INT,
  4.     @Itemcode  AS INT
  5.  
  6. AS
  7.  
  8. SET NOCOUNT ON
  9. --SET ANSI_NULLS ON
  10. --SET QUOTED_IDENTIFIER ON
  11.  
  12.     BEGIN
  13.  
  14.  
  15.     DECLARE @inflow INT
  16.     DECLARE @outflow INT
  17.     DECLARE @credit INT
  18.     DECLARE @debit INT
  19.  
  20.     SET  @inflow=(SELECT ISNULL(SUM(Amount),0) 
  21.                                 FROM FisCalYearDonorBudgetT 
  22.                                     WHERE FisCalYear=@FiscalYear 
  23.                                         AND DACode=@Itemcode)
  24.  
  25.     SET  @outflow=(SELECT ISNULL(SUM(Amount),0) 
  26.                                 FROM DonorTranSmasterT 
  27.                                     WHERE FisCalYear=@FiscalYear 
  28.                                         AND ItemiD=@Itemcode)
  29.  
  30.     SET  @credit=(SELECT ISNULL(SUM(Credit),0) 
  31.                                 FROM DonorBudgetAdjustT 
  32.                                     WHERE FisCalYear=@FiscalYear 
  33.                                         AND ItemCr=@Itemcode)
  34.  
  35.     SET  @debit=(SELECT ISNULL(SUM(Debit),0) 
  36.                                 FROM DonorBudgetAdjustT 
  37.                                     WHERE FisCalYear=@FiscalYear 
  38.                                         AND ItemDr=@Itemcode)
  39.  
  40.     SELECT (((@inflow-@outflow)-@credit)+@debit) AS CodeBalance
  41. END
  42. GO
...............<naina>.................
Mar 3 '10 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. with Me.TxtiTemBalance
  2.    .RowSource = "EXEC CodeBalance  " _
  3.               & me.cbofiscalyear & "," & ItemCodeValue
  4. end with
  5.  
Mar 3 '10 #2

P: 11
Dear Delerna,


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


Naina
Mar 4 '10 #3

Delerna
Expert 100+
P: 1,134
You are very welcome
Mar 4 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.