Passing Edited Value List Combo Box Parameter to Stored Procedure Via VBA Problem

[dan4280]

Hi



This is a bit of an odd one. I have a form in access with multiple combo boxes (populated by a query) that happily pass their value (using VBA) to my stored procedure in sql server. I decided though to not limit users to the query list values, so set the "limit to list" option to no. The access form behaves as expected whereby a user can either select a value from the dropdown or type in their own.

Values selected from the list pass to my stored procedure no problem and the procedure works. However, if I choose to type my own value (not in the list) the value does not get passed to the stored procedure. There is no error message as my SP uses the passed value in an IN function in my sql. I think it seems to ignore non list values or sets them to null.

Anyone have any ideas?!

[orange]

I think you're going to have to post some of the code involved for readers to understand. There doesn't seem to be anything obvious in your description of the issue.

[dan4280]

I think you're going to have to post some of the code involved for readers to understand. There doesn't seem to be anything obvious in your description of the issue.

Like I say - this works fine if the parameters pick up a combo box list value. It doesn't work (but no error messages) when I enter a value manually not in the list:

Option Compare Database
Private Sub Apply_View_Permissions_Click()
If vbYes = MsgBox("Are you sure you want to create these permissions for this user?", vbQuestion Or vbYesNo, "Create Key Account Permissions") Then

Dim objConnection As New ADODB.Connection
Dim objCom As ADODB.Command
Dim provStr As String
Dim objparam1 As ADODB.Parameter
Dim objparam2 As ADODB.Parameter
Dim objparam3 As ADODB.Parameter
Dim objparam4 As ADODB.Parameter
Dim objparam5 As ADODB.Parameter
Dim objparam6 As ADODB.Parameter
Dim objparam7 As ADODB.Parameter
Dim objparam8 As ADODB.Parameter
Dim objparam9 As ADODB.Parameter
Dim objparam10 As ADODB.Parameter
Dim objparam11 As ADODB.Parameter

Set objCom = New ADODB.Command
objConnection.Provider = "sqloledb"
provStr = "Data Source=UKSWPMC130;" & "Initial Catalog=SalesBudget;Integrated Security=SSPI;"
objConnection.Open provStr

With objCom
.ActiveConnection = objConnection
.CommandText = "Proc_Permissions_Userkeyaccount"
.CommandType = adCmdStoredProc
.CommandTimeout = 360
End With

Set objparam1 = New ADODB.Parameter
With objparam1
.Name = "objparam1"
.Type = adVarChar
.Direction = adParamInput
.Size = 128
.Value = Me.UserId
End With
Set objparam2 = New ADODB.Parameter
With objparam2
.Name = "objparam2"
.Type = adVarChar
.Direction = adParamInput
.Size = 40
.Value = Me.keyacc1
End With
Set objparam3 = New ADODB.Parameter
With objparam3
.Name = "objparam3"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc2
End With
Set objparam4 = New ADODB.Parameter
With objparam4
.Name = "objparam4"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc3
End With
Set objparam5 = New ADODB.Parameter
With objparam5
.Name = "objparam5"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc4
End With
Set objparam6 = New ADODB.Parameter
With objparam6
.Name = "objparam6"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc5
End With

Set objparam7 = New ADODB.Parameter
With objparam7
.Name = "objparam7"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc6
End With
Set objparam8 = New ADODB.Parameter
With objparam8
.Name = "objparam8"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc7
End With
Set objparam9 = New ADODB.Parameter
With objparam9
.Name = "objparam9"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc8
End With
Set objparam10 = New ADODB.Parameter
With objparam10
.Name = "objparam10"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc9
End With
Set objparam11 = New ADODB.Parameter
With objparam11
.Name = "objparam11"
.Type = adVarChar
.Direction = adParamInput
.Size = 30
.Value = Me.keyacc10
End With
With objCom
.Parameters.Append objparam1
.Parameters.Append objparam2
.Parameters.Append objparam3
.Parameters.Append objparam4
.Parameters.Append objparam5
.Parameters.Append objparam6
.Parameters.Append objparam7
.Parameters.Append objparam8
.Parameters.Append objparam9
.Parameters.Append objparam10
.Parameters.Append objparam11
.Execute , , adExecuteNoRecords

Set objConnection = Nothing
Set objCom = Nothing
End With
End If
End Sub