How to fill in a combo box with data from a FoxPro table

I want to create a combo box that will list the items in my FoxPro table. I have created the recordset, connection, etc. but I can't get it to populate in the form.

Also, I want the combo box to store the index value of each option instead of the full name (i.e. 1 instead of "Disconnected Call"). However, I am not sure if it is best to create an auto-index based on the recordset or just pull the primary key value from the table. Either way, how do i accomplish this?

I'm still new to this whole VB thing so any help is appreciated!

Below is what my code looks like:


Sub QuestionLookup()

Dim cnn2 as new ADODB.Connection
Dim rs as new ADODB.Recordset
Dim dbLookup as string
Dim sql_q as string
Dim indexq as Integer
Dim listitem as string
Dim I


sql_q = "SELECT * From tbl_rsd_helpnet"
dbLookup = "DB_Tracking"

cnn2.Open dbLookup

rs.Open sql_q, dbLookup, adOpenStatic, adLockReadOnly
'Create auto-index number for each value in recordset
Do While Not rs.EOF
for I = 1 to 175
listitem = rs!helpnet
'add list items to combo box
cboQuestion.AddItem listitem, I
next I
rs.MoveNext
Loop


End Sub

cnn2.Open dbLookup
rs.Open sql_q, cnn2, adOpenStatic, adLockReadOnly
'Create auto-index number for each value in recordset
I = 0
Do Until rs.EOF
I = I + 1 'Do it this way as you may have more than 175 item in your recordset although you probably should use the autoincrement field in your db
'add list items to combo box
cboQuestion.AddItem rs!helpnet & " ," & I
'You can store 'I' in the combo itemdata if you do not need it displayed
'cboQuestion.Itemdata(cboQuestion.NewIndex) = I
'You can access this number this way
'cboQuetion.Itemdata(cboQuestion.ListIndex)
rs.MoveNext
Loop

David Paulson

Thank you so much for your help!

I get an "invalid use of property" on this line of code

cboQuestion.ItemData (cboQuestion.ListIndex)

??

That was just an example of how to assess it. You must assign it to something like text1 = cboQuestion.ItemData (cboQuestion.ListIndex) and the value will appear in a text box or to find this record in your database.
rs.Find "QuestionID = " & cboQuestion.ItemData(cboQuestionn.ListIndex), , adSearchForward, adBookmarkFirst . This is assuming that in you db you have an autoincrement field named QuestionID (This is why I suggested using the autoincrement field in my earlier post.)



David Paulson

Again, Thanks for your help on this one!