Listed Box Pass selected to a sql sp where statment (1 Viewer)

I am trying to pass the results of what is selected in a list box to sql through an ado statement, when i select one result i can pass the value with no issue, when i select multiple item i get a run time error incorrect syntax near ';' i am guessing it is passing the data as a csv to sql and the where statement does not like that as criteria but i am not sure how to handle that either in vba or on the sql side thanks so much

here is my vba code from access

Private Sub Command49_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strItems As String
Dim intItem As Integer
For intItem = 0 To List45.ListCount - 1
If List45.Selected(intItem) Then
strItems = strItems & List45.Column(0, intItem) & ";"
End If
Next intItem


Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim refnumber As String



refnumber = strItems




SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "14dec2012"
SQLString = "Exec sp_test " & refnumber & " "






'Create a new ADO Connection object
Set cn = New ADODB.Connection

'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With

'Create an instance of the ADO Recordset class, and
'set its properties


Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With


'Set the form's Recordset property to the ADO recordset
' Set Me.Recordset = rs
'Set rs = Nothing
'Set cn = Nothing



here is the simple sql sp

USE [14dec2012]
GO
/****** Object: StoredProcedure [dbo].[sp_DeltaClear] Script Date: 09/11/2013 09:35:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_test]
@refnumber int

As
Begin
update inventory
set no_bgs_in = 100

where [reff number] = @refnumber
end

First of all, please use CODE tags to post your code. This makes it easier for us to read.

Second, the reason your code is breaking is because you are not creating a correct SQL statement. The line below in red is where the root of your problem lies. You need to modify this to something of the effect of (strItems = strItems & List45.Column(0, intItem) & " OR "). In SQL syntax the ";" signifies the end of the statement.


Check out this link for help with the correct syntax

http://www.w3schools.com/sql/sql_where.asp

thanks for the help but i am still get a run time error incorrect syntax near the keyword 'or'

Did you check the link? You will need to construct a WHERE clause