VB: finding associated data in SQL?

alevey

New Member
Joined
Oct 14, 2002
Messages
3
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?

thanks!

Code below...

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

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.
rstUnit.MoveFirst

' 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

rstUnit.MoveNext
Loop

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

' Closes the table.
rstUnit.Close

' Closes the connection.
cnn1.Close
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

HTH

Chris

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
rsNames.MoveNext
Loop
 

alevey

New Member
Joined
Oct 14, 2002
Messages
3
Chris,
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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