VB: finding associated data in SQL?


New Member
Oct 14, 2002
I have a query to populate a ComboBox with SQL data from a particular Column. After I choose the data I want, I'd like to have
a function run to pull the rest of the associated data (data in that same row in SQL) into specific cells. Anyone have a function to 'find the associated data in SQL' without pulling it all to a sheet first?


Code below...

I'm using ADO to access SQL and populate the ComboBox in a UserForm (see

To write the value chosen in the box to the appropriate cell:
Range(strEntryCell).Value = ChooseTag.Value
'strEntryCell is the excel sheet's cell value (it increments)
'ChooseTag is the ComboBox name

I did not bring the SQL data into a sheet, I want to avoid that.

Code in general module, used to access SQL via ADO:

Public Sub PopulateControl()
Dim cnn1 As ADODB.Connection
Dim rstUnit As ADODB.Recordset
Dim strCnn As String

' Open connection.
strCnn = "Provider=sqloledb;Data Source=desk;Initial Catalog=lrmstr;" &
"User Id=sa;Password=sa; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Open required table.
Set rstUnit = New ADODB.Recordset
rstUnit.CursorType = adOpenKeyset
rstUnit.LockType = adLockOptimistic
rstUnit.Open "tbLrmstr", cnn1, , , adCmdTable

' Moves to the first record in the record set.

' Loops through each entry in the record set and adds the last name
' for each entry into the combo box.
Do Until rstUnit.EOF
QuoteDataQuery.ChooseTag.AddItem rstUnit!Unit


' Displays the user form. You don't need this if you are not using
' a UserForm object.

' Closes the table.

' Closes the connection.
End Sub

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.


Well-known Member
Sep 3, 2002
The code below shows how I've taken information from a database by using a select statement. In your case you would have to modify the SQLStatement line to include a where clause : eg

SQLStatement = "select * from testtable where myfield =" & mycombobox.text



Dim SQLStatement
Dim ConnectString
Dim MyODBCConnection

MyODBCConnection = "UsersDB"

' Open connection
' You need to set the ADO reference up in the Tools/Refence menu (Microsoft Actice X data objects Library)
Set ConnectDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
ConnectDB.Open ConnectString

' Open recordset from database table
Set rsNames = New ADODB.Recordset
rsNames.CursorLocation = adUseClient

' Use client cursor to enable AbsolutePosition property
SQLStatement = "SELECT * from testtable"
rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText

' Setup a counter to run down the spreadsheet rows
LineCount = 1

' Loop through returned records
Do While Not rsNames.EOF
Worksheets("Sheet1").Cells(LineCount, 1).Value = rsNames.Fields("NameField")
Worksheets("Sheet1").Cells(LineCount, 2).Value = rsNames.Fields("DetailsField")
LineCount = LineCount + 1


New Member
Oct 14, 2002
Sorry... I'm a newbie!

I get the following error:
Run-time '424'
Object required

here's my code:

SQLSelect = "SELECT * from lrmstr where Unit =" & ChooseTag.Value

lrmstr is the DB Table
ChooseTag is the ComboBox on the UserForm
Unit is the DB Column
I have 'Public SQLSelect' in Declarations in the same module
I have two functions PopulateControl() & GetAssociatedData() in this module
PopulateControl opens the database
GetAssociatedData has the Select & Open and Do While statements.

thanks for the help!
This message was edited by alevey on 2002-10-16 18:47
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

Latest member

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