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

Call a stored procedure with user inputted parameter

P: 23
I am trying to call a stored SQL procedure while either passing or accepting a used inputted parameter. Currently I have a variable coded to accept the number I need to pass, but I do not know how to pass that number along with the the SQL call.
My current code is as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub butMoveNDStoBO_Click()
  2. On Error GoTo Err_butMoveNDStoBO_Click
  3.  
  4.     Dim RecordNo As Long
  5.     Dim SQL As String
  6.  
  7.     RecordNo = InputBox(Prompt:="Enter Record Number")
  8.     SQL = "EXEC usp_MoveNDS_to_BO"
  9.  
  10.     DoCmd.RunSQL SQL
  11.  
  12.  
  13. Exit_butMoveNDStoBO_Click:
  14.     Exit Sub
  15.  
  16. Err_butMoveNDStoBO_Click:
  17.     MsgBox Err.Description
  18.     Resume Exit_butMoveNDStoBO_Click
  19.  
  20. End Sub
  21.  
Jun 1 '07 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I'm guessing this is for SQL Server from Access?
Can you be a little more clear with the question please.
Jun 2 '07 #2

P: 23
I am sorry... Yes I am making a call to an SQl server from Access. Should I be using the SQL forum instead? I was a little confused on which forum I should use.

I am trying to modify an existing ADP to avoid some error messages we started getting when we switched from using access 02 to access 07. We initially had a button which activated a macro that accepted user input (6 digit number) to determine which record to move/delete.

I am not terribly fluent with Access or SQL (kinda learning as I go...). In Access '07's help file I found the command "DoCmd.RunSQL" and was able to adapt the majority of out macro calling buttons, but the basic fix did not work with the buttons that require user input.

The stored procedure expects to get passed a field titles "RecordNo" to select the record and move/delete it. I do not know how to provide this parameter with the DoCmd.RunSQL command.
Jun 4 '07 #3

P: 23
Solved the problem a different way. Please close this thread
Jun 4 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
I'll try to look out for something tomorrow.
If I don't reply in one way or another by end of play, then bump the thread again.
Jun 4 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
Solved the problem a different way. Please close this thread
Sorry, I missed your later post yesterday. We'll treat this as done then :)
Good luck with your project.
Jun 5 '07 #6

Post your reply

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