Here is a snipit of the VBA code containing the parameter setup and Execute command. The "Pricing_Calc_sp" stored procedure brings data together, populates, and updates some tables. This is the stored procedure that is working when run using Query Analyzer but it does not run correctly when executed here. Please understand, I cannot tell if it is failing in some way when run, but it does run to completion and the VBA code continues on to the next stored procedure ( "Pricing_CalcReport_sp" ) which creates the tables used by the report ( "Pricing_Calc_Rpt" ) which is called at the end of the code below. All of this happens without any errors that I am aware of.
As you can see below I have put Debug.Print code within the VBA to check the parameter settings. They are correct.
Dim CMD As New ADODB.Command
Dim Param As ADODB.Parameter
Dim errLoop As ADODB.Error
CMD.ActiveConnection = NGMSConnect
CMD.CommandText = "Pricing_Calc_sp"
CMD.CommandType = adCmdStoredProc
Set Param = New ADODB.Parameter
Param.Name = "Start"
Param.Type = adDBTimeStamp
Param.Value = dteStart
Param.Direction = adParamInput
CMD.Parameters.Append Param
Debug.Print "Pricing_Calc_DialogFrm, cmdOK: dteStart=" & Param.Value & "|"
Set Param = New ADODB.Parameter
Param.Name = "End"
Param.Type = adDBTimeStamp
Param.Value = dteEnd
Param.Direction = adParamInput
CMD.Parameters.Append Param
Debug.Print "Pricing_Calc_DialogFrm, cmdOK: dteEnd=" & Param.Value & "|"
Set Param = New ADODB.Parameter
Param.Name = "Username"
Param.Type = adVarChar
Param.Size = 15
Param.Value = strUserID
Param.Direction = adParamInput
CMD.Parameters.Append Param
Debug.Print "Pricing_Calc_DialogFrm, cmdOK: strUserID=" & Param.Value & "|"
Set Param = New ADODB.Parameter
Param.Name = "LocContract"
Param.Type = adVarChar
Param.Size = 20
Param.Value = strLocContract
Param.Direction = adParamInput
CMD.Parameters.Append Param
Debug.Print "Pricing_Calc_DialogFrm, cmdOK: strLocContract=" & Param.Value & "|"
Set Param = New ADODB.Parameter
Param.Name = "Trans"
Param.Type = adInteger
Param.Value = intTrans
Param.Direction = adParamInput
CMD.Parameters.Append Param
Debug.Print "Pricing_Calc_DialogFrm, cmdOK: intTrans=" & Param.Value & "|"
Set Param = New ADODB.Parameter
Param.Name = "RunID"
Param.Type = adDecimal
Param.Precision = 18
Param.NumericScale = 4
Param.Value = varRunID
Param.Direction = adParamInput
CMD.Parameters.Append Param
Debug.Print "Pricing_Calc_DialogFrm, cmdOK: varRunID=" & Param.Value & "|"
Set Param = New ADODB.Parameter
Param.Name = "Revision_Flag"
Param.Type = adVarChar
Param.Size = 1
Param.Value = "N"
Param.Direction = adParamInput
CMD.Parameters.Append Param
Debug.Print "Pricing_Calc_DialogFrm, cmdOK: Revision_Flag=" & Param.Value & "|"
CMD.Execute
Me!txtMessage = "Calculating ... Pricing_CalcReport_sp."
DoCmd.Hourglass True
Me.txtMessage.Requery
Me.Repaint
Me.Requery
CMD.CommandText = "Pricing_CalcReport_sp"
CMD.CommandTimeout = 0
CMD.Execute
strRevisionFlag = "N"
strScreen = "Pricing_Calc_Rpt"
strScreenReturn = "Pricing_Calc_DialogFrm"
DoCmd.Close acForm, "Pricing_Calc_DialogFrm", acSaveNo
DoCmd.OpenReport "Pricing_Calc_Rpt", acViewPreview
DoCmd.Maximize
The stored procedure parameters are as follows:
CREATE procedure dbo.Pricing_Calc_sp
(
@Start datetime = '2/1/2007',
@End datetime = '2/28/2007',
@Username varchar(15) = 'Randy Harding',
@LocContract varchar(20) = 'NG-50071-017',
@Trans int = 7,
@RunID decimal(18,4) = 9994599.9998,
@Revision_Flag varchar(1) = 'N'
)
AS
