vba help - update value in listbox1 from recordset

[Mallesh23]

Hi Team,

I am reading value from excel Using Recordset , giving search Criteria via userform.
Now I am looking for extracted value to pasted in listbox1. where user can select single value.

Attempted code ----
s = TextBox1.Value
SQLQuery = "SELECT * FROM [Film$] where Title like '" & s & "%'" ' filtering via wildcard value entered in textbox
ListBox1.RowSource = SQLQuery ............something like this or copy from recordset.

How to put recordset value into listbox1.

Private Sub TextBox1_Enter()
Dim s As String
Dim SQLQuery As String
s = TextBox1.Value
SQLQuery = "SELECT * FROM [Film$] where Title like '" & s & "%'"

'Run the query with the SQL string
GetQueryResults (SQLQuery)

End Sub


Sub GetQueryResults(SQLQuery As String)

Dim MovieFilePath As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws As Worksheet
Dim i As Integer
Dim RowCount As Long, ColCount As Long

'Exit the procedure if no query was passed in
If SQLQuery = "" Then
MsgBox _
Prompt:="You didn't enter a query", _
Buttons:=vbCritical, _
Title:="Query string missing"
Exit Sub
End If

'Check that the Movies workbook exists in the same folder as this workbook
MovieFilePath = ThisWorkbook.Path & "\Movies.xlsx"


'Create and open a connection to the Movies workbook
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & MovieFilePath & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"

'Try to open the connection, exit the subroutine if this fails
On Error GoTo EndPoint
cn.Open

'If anything fails after this point, close the connection before exiting
On Error GoTo CloseConnection

'Create and populate the recordset using the SQLQuery
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.CursorType = adOpenStatic

rs.Source = SQLQuery 'Use the query string that we passed into the procedure

' ListBox1.RowSource = SQLQuery

'Try to open the recordset to return the results of the query
rs.Open

'If anything fails after this point, close the recordset and connection before exiting
On Error GoTo CloseRecordset

'Get count of rows returned by the query
RowCount = rs.RecordCount



Debug.Print RowCount & " row(s)", SQLQuery

'Exit the procedure if no rows returned
If RowCount = 0 Then
MsgBox _
Prompt:="The query returned no results", _
Buttons:=vbExclamation, _
Title:="No Results"
Exit Sub
End If

'Get the count of columns returned by the query
ColCount = rs.Fields.Count

'Create a new worksheet
Set ws = ThisWorkbook.Worksheets.Add

'Select the worksheet to avoid the formatting bug with CopyFromRecordset
ThisWorkbook.Activate
ws.Select

'Copy values from the recordset into the worksheet
ws.Range("A2").CopyFromRecordset rs

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

Exit Sub


End Sub[/CODE]


Thanks
mg

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Listbox fill via recordset
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Mallesh23]

Hi Fluff,

I Found solution, did some trouble shooting, I put loop in recordset and updated listbox value.

Actually I was looking whether we can directey add all value into listbox without loop.

it was urgent requirement. hence got posted in both the places.

In on this forum Uploading of workbook along with module option not there. thanks.



Thanks
mg

 

[Fluff]

it was urgent requirement. hence got posted in both the places.

That is no excuse for not supplying the link. You know full well that you are meant to do it, so in future please ensure that you ALWAYS supply links.