Multicolumn ComboBox

[Rod Haywood]

I'm trying to populate a multicolumn combo box with data from a sql query.
First problem is that I have to click the down arrow in the cbox to see the
first record. How do I change that? Second problem is populating each column.
That I can't figure out. Below is the function. As you can see the line that
isn't commented out will get the first column, the second gets the second
column of the record, but both are put in the first column of the combo box.
How do I populate the three columns with corresponding fields and will this
loop through and get all db records specified in the query.

As you can see I commented out some lines that i thought would work, but
don't. I recieve a 424 run-time error, "object required". Would this work if
I solve that problem?

Public Function AddToCombo()
   Dim dbs As Database, rst As Recordset, strSQL As String, strSQL1 As
String
'now get the rest of the data from OrderInfo where ordernumber is same
'and set to variables
   strSQL = ""
   strSQL1 = ""
   Set dbs = CurrentDb
   strSQL1 = "SELECT * FROM OrderData where ordernum = "
   strSQL = strSQL1 & OrderNum
   Set rst = dbs.OpenRecordset(strSQL)
 
   With rst
   i = 0
     Do Until .EOF
     cmbOrders.AddItem .Fields(1).Value
     cmbOrders.AddItem .Fields(2).Value
'      cmbOrders.AddItem (i)
'      cmbOrders.Column(0, i) = .Fields("Quantity")
'      cmbOrders.Column(1, i) = .Fields("Description")
'      cmbOrders.Column(2, i) = .Fields("Price")
     .MoveNext
     i = i + 1
     Loop
     End With
 
   rst.Close
End Function

[NeoPa]

Rod, We only allow one question per thread, but I will quickly deal with the question about how to set the ComboBox to reflect the first record without having to select it manually. The solution is to set the ComboBox value to equal that of the first record. For a MultiColumn ComboBox the value would reflect that column which is the default (or bound) column.

Moving on to your code we find a bit more to deal with. As you have made an attempt though, I will see what I can do about pointing out the mistakes :

1. In Line #2 Database and Recordset should be defined more specifically as DAO.Database and DAO.Recordset.
2. Using AddItem is straightforward enough. See below (An excerpt from the Help Page) for how to add rows for MultiColumn controls :
   For multiple-column lists, use semicolons to delimit the strings for each column (for example, "1010;red;large" for a three-column list). If the Item argument contains fewer strings than columns in the control, items will be added starting with the left-most column. If the Item argument contains more strings than columns in the control, the extra strings are ignored.
3. Context-sensitive help (A page relevant to exactly what you're working on) is easy to use and is a simple keystroke away (Press F1 with the cursor over the word you need help on).
4. Actually, that long list of problems I noticed all came down to just those two in the end. You have the .MoveNext included already (Frequently overlooked in my experience), so not such a bad attempt after all ;-)

There is another major point here of course though. One that makes most of your code redundant anyway. Why not simply set up the ComboBox as having a Row Source of the query you require. That's actually the normal (and more efficient) way of populating such a control.