Populating other controls on Excel sheet based on combo box value 2

[cyberbob2]

Hello, sorry if I didn't explain it very well before. Actaully, what I'm trying to do is populate other fields on the form based on the value from a combo box which displays the id number, but the other fields are from different fields in the table. I'm populating the order_ids into the combo box already, so the next step for me is if the user selects a certain order_id from the combo box, the other fields on the sheet update accordingly. I used to do this with the form/control wizard in Access but not sure how to do it in Excel. Do I need to use a query like 'SELECT order name, order details FROM Orders WHERE order_id = 'Excel combo box selection value'? Just not sure how to approach it.

Here is my code module:

Public Sub PopulateControl()

Dim cnRetailData As ADODB.Connection
Dim rsRetailData As ADODB.Recordset
Dim strCnn As String

' Open connection.
strCnn = "Provider=sqloledb; Data Source=TEST;Initial Catalog=TESTDB;" & _
"User Id=XX;Password=XXXXX; "
Set cnRetailData = New ADODB.Connection
cnRetailData.Open strCnn

' Open Orders table.
Set rsOrders = New ADODB.Recordset
rsOrders.CursorType = adOpenKeyset
rsOrders.LockType = adLockOptimistic
rsOrders.Open "Orders", cnRetailData, , , adCmdTable

' Moves to the first record in the record set.
rsOrders.MoveFirst

' Loops through each entry in the record set and adds the last name
' for each entry into the combo box.
Do Until rsOrders.EOF
ActiveSheet.ComboBox1.AddItem rsOrders!order_number
' To use a ListBox control, use the following statement instead
' of the one above:
' UserForm1.ListBox1.AddItem rsOrders!order_number
'
' If the ComboBox or ListBox is on a worksheet instead of
' a UserForm, reference the worksheet instead of the UserForm:
' ActiveSheet.ComboBox1.AddItem rsOrders!order_number
rsOrders.MoveNext
Loop

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

' Closes the table.
rsOrders.Close
' Closes the connection.
cnRetailData.Close


End Sub

Thanks in advance,
CB

 

[SkipVought]

Hi,

Based on a combobox selection, you want to execute a query to return that resordset. Piece o' cake!

Use MS Query via Data/Get External Data...

In the QBE Editor, enter your criteria value as
[tt]
[Enter Value]
[/tt]
to create a parameter query.

File/Return data to Excel, and specify that the VALUE is in a specific cell (the cell that contains the combobox selection) and Update on cell change if so desired.

VOLA!

Skip,
[sub]
[red][/red]
[/sub]

 

[cyberbob2]

Hi Skip,

Thanks again. Once more question on this though. I have this working with one field being returned to one cell, but what I need to do is return several fields from one table and have them mapped to several cells throughout the sheet. Can't seem to get this to happen. Hope this makes sense.

Thanks,
CB

 

[PHV]

Use a QueryTable in another (possibly hidden) sheet returning all the fields you need and then use formulas referencing the relevant cells where you need the retrieved values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cyberbob2]

Hi PHV,

Thanks, I can get all the table fields returned to one cell, but I can't figure out how to break them up so each field maps to a different cell on my hidden sheet. Once I get this I know I can simply copy the cells with formulas over to wherever I need them in the workbook.

The hard part now for me is trying to break up the fields to return into separate cells instead of all table fields returning to one cell. Hope that makes sense.

Thanks,
CB

 

[PHV]

instead of all table fields returning to one cell
???
What is the SQL code of your QueryTable ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cyberbob2]

SELECT Orders.order_name, Orders.order_number, Orders.order_status, Orders.dept, Orders.division, Orders.start_date
FROM TESTDB.dbrders Orders
WHERE (Orders.order_number=?)

 

[PHV]

So, this QueryTable should populate at least 6 cells ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cyberbob2]

Yes it does, but I cannot get those cells to map over to the cells I need populated. I tried copying the formulas from the populated by query cells to the cells where I need the data to display, (Copy-->Paste-->All), but the cells I need to update don't update when the value in the criteria cell is updated, only the cells that came in when I went through the query wizard. I've tried new cells and adjacent cells, both which work (Right-Click-->Data Range Properties), but I cannot get the cells I need (which are either on a different or same sheet) to behave this way. Hope I'm making sense.

 

[PHV]

Why not using formulas referencing the relevant cells of the QueryTable.Destination range ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cyberbob2]

I know this might seem strange, but I'm not sure how

 

[PHV]

Put the cursor in one cell "you need to get", type = and select the relevant cell in the data range.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cyberbob2]

OK, so if I want to have a hidden sheet with all my populated cells and reference those cells, then I can do something like:
=Sheet2!A1

Would this be correct?

 

[cyberbob2]

OK, I got it. Thanks for being patient with me. I'm a java guy, not used to their Microsoft stuff.

 

[PHV]

Provided that your hidden sheet is Sheet2, then yes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886