vba help - update value in listbox1 from recordset

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
963
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,957
Messages
5,767,308
Members
425,403
Latest member
MellieD

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
Top