Passing Parameter From Form To Sored Proc VBA error 1

[DomDom3]

Hi there,

I'm using the following code from a form's click event to pass a parameter from a combo box to a stored proc.

When running the I get a run time 3708 error, parameter not properly defined etc. The offending line is

.Parameters.Append .CreateParameter("@service1", char, adParamInput, 3, Me!SelectService)

THe code picks up the combobox value okay but can't pass it to the proc. WhenI run the proc it asks for the parameter. The paramete seems to be set in the stored proc okay. Is it a problem with the vba?


Dim adoCnn As ADODB.Connection
Dim adoCmd As ADODB.Command

Set adoCnn = New ADODB.Connection
Set adoCmd = New ADODB.Command

With adoCnn
.ConnectionString = "Driver={SQL Server};" & _
"Server=THIBERT-7;" & _
"Database=DIPartnerForecastTool;" & _
"Trusted_Connection=Yes"
If .State = 0 Then .Open
End With

With adoCmd
.ActiveConnection = adoCnn
.CommandText = "spPartner_spend_forecast"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@service1", char, adParamInput, 3, Me!SelectService)
.Execute

'Close the connection
adoCnn.Close

Thanks

 

[PWise]

why dont you use this syntex

.Parameters("@service1") = Me!SelectService

 

[DomDom3]

Thanks, I'm kicking myself now, I tried

.Parameters("@service1") = Me.SelectService

earlier, I'm still hung up on Access habits. Having got past that problem I'd expected the datasheet for the Stored Proc to appear but no such luck.

 

[PWise]

the way i do this is
create a form with the Sp as the record source
insert this as a subform on the form that you have the SelectService combobox

With adoCmd
      .ActiveConnection = adoCnn
      .CommandText = "spPartner_spend_forecast"
      .CommandType = adCmdStoredProc
      .Parameters("@service1") = Me.SelectService
      set me.subformname.form.recordset =.Execute

end with

 

[DomDom3]

Thanks it's a good suggestion, I'd previously seen a variation of this in a thread earlier but wanted to stay away from fomrs, but I think it seems the most plausible idea now.