Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
This one's more serious
I need help with this syntax... I have been able to successfully load my
ComboBox from a SQL Server Recordset. However, I am only getting one column in my combo box, and I need both columns from the recordset to show. Can one of you masters show me what is wrong with my code, or how to tweak it to make the combo box load both columns of my query. 'Set Up cmbxCustomerSelection With cmbxCustomerSelection .ColumnCount = 2 .BoundColumn = 2 .TextColumn = 1 End With 'Load cmbxCustomerSelection Do While Not adoRsA.EOF cmbxCustomerSelection.AddItem adoRsA(0).Value adoRsA.movenext Loop Much appreciation! DBAL |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
This one's more serious
DBAL,
Try this: Do While Not adoRsA.EOF cmbxCustomerSelection.AddItem adoRsA(0).Value cmbxCustomerSelection.List(cmbxCustomerSelection.L istCount - 1,1) = adoRsA(1).Value adoRsA.movenext Loop Robin Hammond www.enhanceddatasystems.com "DBAL" wrote in message ... I need help with this syntax... I have been able to successfully load my ComboBox from a SQL Server Recordset. However, I am only getting one column in my combo box, and I need both columns from the recordset to show. Can one of you masters show me what is wrong with my code, or how to tweak it to make the combo box load both columns of my query. 'Set Up cmbxCustomerSelection With cmbxCustomerSelection .ColumnCount = 2 .BoundColumn = 2 .TextColumn = 1 End With 'Load cmbxCustomerSelection Do While Not adoRsA.EOF cmbxCustomerSelection.AddItem adoRsA(0).Value adoRsA.movenext Loop Much appreciation! DBAL |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
This one's more serious
Robin has already shown you one way. This is another way. Here you first
store all the values in an array (from a recordset) and then use them to fill the list box all at once. Sub test() Dim a2DimArray(1 To 4, 1 To 2) As String a2DimArray(1, 1) = "a1" a2DimArray(2, 1) = "a2" a2DimArray(3, 1) = "a3" a2DimArray(4, 1) = "a4" a2DimArray(1, 2) = "b1" a2DimArray(2, 2) = "b2" a2DimArray(3, 2) = "b3" a2DimArray(4, 2) = "b4" With Sheet1 .ListBox1.List = a2DimArray End With End Sub Alok Joshi "DBAL" wrote: I need help with this syntax... I have been able to successfully load my ComboBox from a SQL Server Recordset. However, I am only getting one column in my combo box, and I need both columns from the recordset to show. Can one of you masters show me what is wrong with my code, or how to tweak it to make the combo box load both columns of my query. 'Set Up cmbxCustomerSelection With cmbxCustomerSelection .ColumnCount = 2 .BoundColumn = 2 .TextColumn = 1 End With 'Load cmbxCustomerSelection Do While Not adoRsA.EOF cmbxCustomerSelection.AddItem adoRsA(0).Value adoRsA.movenext Loop Much appreciation! DBAL |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
This one's more serious
Very nice Robin. I'll give this a try. Thanks alot!
DBAL. "Robin Hammond" wrote: DBAL, Try this: Do While Not adoRsA.EOF cmbxCustomerSelection.AddItem adoRsA(0).Value cmbxCustomerSelection.List(cmbxCustomerSelection.L istCount - 1,1) = adoRsA(1).Value adoRsA.movenext Loop Robin Hammond www.enhanceddatasystems.com "DBAL" wrote in message ... I need help with this syntax... I have been able to successfully load my ComboBox from a SQL Server Recordset. However, I am only getting one column in my combo box, and I need both columns from the recordset to show. Can one of you masters show me what is wrong with my code, or how to tweak it to make the combo box load both columns of my query. 'Set Up cmbxCustomerSelection With cmbxCustomerSelection .ColumnCount = 2 .BoundColumn = 2 .TextColumn = 1 End With 'Load cmbxCustomerSelection Do While Not adoRsA.EOF cmbxCustomerSelection.AddItem adoRsA(0).Value adoRsA.movenext Loop Much appreciation! DBAL |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
This one's more serious
Ahh Alok, thanks again. I will keep this solution handy... I am going to try
the other way first. But I am wondering, if I had mulitple columns to work with would the array method be the best one? Either way, thanks! DBAL. "Alok" wrote: Robin has already shown you one way. This is another way. Here you first store all the values in an array (from a recordset) and then use them to fill the list box all at once. Sub test() Dim a2DimArray(1 To 4, 1 To 2) As String a2DimArray(1, 1) = "a1" a2DimArray(2, 1) = "a2" a2DimArray(3, 1) = "a3" a2DimArray(4, 1) = "a4" a2DimArray(1, 2) = "b1" a2DimArray(2, 2) = "b2" a2DimArray(3, 2) = "b3" a2DimArray(4, 2) = "b4" With Sheet1 .ListBox1.List = a2DimArray End With End Sub Alok Joshi "DBAL" wrote: I need help with this syntax... I have been able to successfully load my ComboBox from a SQL Server Recordset. However, I am only getting one column in my combo box, and I need both columns from the recordset to show. Can one of you masters show me what is wrong with my code, or how to tweak it to make the combo box load both columns of my query. 'Set Up cmbxCustomerSelection With cmbxCustomerSelection .ColumnCount = 2 .BoundColumn = 2 .TextColumn = 1 End With 'Load cmbxCustomerSelection Do While Not adoRsA.EOF cmbxCustomerSelection.AddItem adoRsA(0).Value adoRsA.movenext Loop Much appreciation! DBAL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables are showing One's where real value should be | Excel Discussion (Misc queries) | |||
Can AutoSave's default settings be changed to one's preference? | Excel Discussion (Misc queries) | |||
Match, Copy, Merge, Delete and Repeat... MVP's this one's for you. | Excel Discussion (Misc queries) | |||
Pleeze Help! Export Excel data into a form in a Word Doc...this one's tough! | Excel Discussion (Misc queries) | |||
can one write one's own Excel Function? | Excel Programming |