vba help - update value in listbox1 from recordset

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
983
Office Version
  1. 2010
Platform
  1. Windows
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.


1629702016609.png


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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top