How to pass a variable into an SQL statement
I am trying to pass a variable into an SQL statement as part of the criteria.
Here is what my code looks like: The ** indicate where the SQL statement is. Selec is the the variable i am trying to pass instead of hardcoding it. Public Sub QryBatch_Click() Dim Selec As Variant Dim Cnct As String, Src As String Dim Connection2 As ADODB.Connection Dim Recordset As ADODB.Recordset Dim Col As Integer Dim Row As Integer Dim w As Integer Sheets("Sheet3").Cells.Clear 'Assigns the selected value inside the List Box to a variable For w = 0 To Sheets("Sheet1").LstBatchnum.ListCount - 1 If Sheets("Sheet1").LstBatchnum.Selected(w) Then _ Selec = Selec & Sheets("Sheet1").LstBatchnum.List(k) & vbCrLf Next w 'Delete Previous Contents in the Tire number ListBox For i = 1 To Worksheets("Sheet1").LstTirenum.ListCount Sheets("Sheet1").LstTirenum.RemoveItem (0) Next i Set Connection2 = New ADODB.Connection Cnct = "Driver={Microsoft Access Driver (*.mdb)};Dbq=O:\SITERW\Carl Lamar\Access\databases\ely.mdb;Uid=Admin;Pwd=;" Connection2.Open ConnectionString:=Cnct Set Recordset = New ADODB.Recordset With Recordset **Src = "DEFINE Selec SELECT * FROM tblResults WHERE Batch = '&Selec'" .Open Source:=Src, ActiveConnection:=Connection2 'For Col = 0 To Recordset.Fields.Count - 1 ' Sheets("Sheets3").Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name 'Next Sheets("Sheet3").Range("A1").Offset(1, 0).CopyFromRecordset Recordset 'Copies the data inside the recordset into a List Box For Row = 2 To Recordset.Properties.Count - 1 Sheets("Sheet1").LstTirenum.AddItem Sheets("Sheet3").Cells(Row, 4) Next Row End With Set Recordset = Nothing Connection2.Close Set Connection2 = Nothing End Sub |
All times are GMT +1. The time now is 07:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com