VB: finding associated data in SQL?

[alevey]

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

 

[ChrisUK]

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]

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