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
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