Macro Helpurgently needed on using Update SQL Command

[kensaffer]

I've writtema macro that executes a stored procedure as well as writing the stored procedure.
Here's the SP in my VBA
DoCmd.OpenStoredProcedure "dbo.change_mrn_alltables_sp"
the procedure is :
Alter PROCEDURE dbo.Change_MRN_AllTables_SP @OLD_MRN_TO_BE_CHANGED varchar(25) , @NEW_MRN varchar(25)
AS
update accountstbl set MRN = @NEW_MRN where mrn = @OLD_MRN_TO_BE_CHANGED

When I run I get message:
RUN TIME ERROR "2001"
You cancelled the previous operation
I'm lost and spent over 5 hours on this>

If I run as macro executing the stored procedure the code is...
DoCmd.RunMacro "change_mrn"
and the macro executes the stored procedure BUT I get message
Action Failed
Condition = True
Action name:Openstoredprocedure
Arguments: dbo.change_mrn_alltables_sp,print preview,edit

Please help!

 

[cmmrfrds]

It is recommended to use the ADO Command for working with stored procedures instead of Macros. Here is a fairly complete example with multiple parameters.


Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn

Set param1 = cmd.CreateParameter(, adDBDate, adParamInput, , "10/1/2001")
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate
Set param3 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param3
param3.Value = 3
Set param4 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param4
param4.Value = 3

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command to run stored procedure
cmd.Execute

 

[kensaffer]

Thank you for the reply and help. What would the code be to ask the user to enter the @OLD_MRN_TO_BE_CHANGED ( a parameter)?

 

[kensaffer]

I ran the code you rpovided to see if it workls and I get a type mismatch on thios line:
Set param1 = cmd.CreateParameter(, adDBDate, adParamInput, , "10/1/2001")

 

[kensaffer]

I ran the code you rpovided to see if it workls and I get a type mismatch on thios line:
Set param1 = cmd.CreateParameter(, adDBDate, adParamInput, , "10/1/2001")

 

[cmmrfrds]

Do you get the error if you structure it like the end date.

endDate = #10/31/2001#

Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate

If so, then it must not be finding the adDBDate constant in the library. In that case check the library.

 

[kensaffer]

Thank you for the reply and help. What would the code be to ask the user to enter the @OLD_MRN_TO_BE_CHANGED ( a parameter)? or to use your example, to ask the user for a date when executing a stored procedure without a form ?