how to use DAO library to fill combo box

[hananfathy]

hello every body

i want to ask how to load combo box from database access 2000 using DAO library code?

best regards

[SamOscarBrown]

What a loaded question.

One, do you know how to query your database and return items in a recordset?

If so, then you simply cycle through the recordset and use the combobox additem feature:

ex: Combo1.Additem(recordsetname!fieldname)

Two, if you don't know how to return items from a database, I'd need a whole lot more information to help you.

Sammi

[Elroy]

Yep, Sam pretty much nailed it. Hananfathy, I'm going to assume you're talking about an unbound ComboBox, as that's always the way I use the DAO (and I use the DAO extensively).

Basically, you've got to get your records accessible in a recordset. You can do that by simply opening a table or possibly running a SQL query. Or, there are yet other ways.

Once you get your recordset object created, you can use RecordCount, MoveFirst, MoveNext, and EOF members to spin through the recordset. I mentioned RecordCount because I always check to see if there are records before I start spinning through them. (Also, RecordCount isn't always accurate, but it always works as a boolean check.)

If there are records, go MoveFirst, put the record in the ComboBox as Sam suggested, check EOF, and then MoveNext until all done.

That's the gist of it.

Good Luck,
Elroy

[hananfathy]

What a loaded question.

One, do you know how to query your database and return items in a recordset?

If so, then you simply cycle through the recordset and use the combobox additem feature:

ex: Combo1.Additem(recordsetname!fieldname)

Two, if you don't know how to return items from a database, I'd need a whole lot more information to help you.

Sammi

i used this code but it didn't work

vn.MoveFirst
Combo1.Clear
Do While Not vn.EOF
Combo1.AddItem ("vn_nm")
vn.MoveNext
Loop

thanks a lot for your help

[hananfathy]

i used the code

vn.MoveFirst
Combo1.Clear
Do While Not vn.EOF
Combo1.AddItem ("vn_nm")
vn.MoveNext
Loop

[ChrisE]

Hi Hanan,

here is one way of doing it.
you will have to change the SQL to your Table in the Database.

'Public Mydb As Database is declared in a Modul

Sub LoadCallTypes(objCombo As Object)
   
   Dim SQL As String
   Dim rsListing
   
   SQL = "SELECT CallTypeID, CallDescription FROM CallType "
   SQL = SQL & "ORDER BY CallDescription"
   
   Set rsListing = Mydb.OpenRecordset(SQL)
   
   With rsListing
      If (.RecordCount > 0) Then
         .MoveFirst
      Do While Not .EOF
            If (Not IsNull(!CallTypeID)) Then
               If (Not IsNull(!CallDescription)) Then objCombo.AddItem Trim(!CallDescription)
               objCombo.ItemData(objCombo.NewIndex) = !CallTypeID
            End If
            .MoveNext
         Loop
      End If
   End With
   
   rsListing.Close
   Set rsListing = Nothing
   
   Exit Sub
End Sub

then in Form..

 
Private Sub Form_Load()

Call LoadCallTypes(Combo1)
    Combo1.ListIndex = 0

End Sub

regards
Chris

[hananfathy]

Hi Hanan,

here is one way of doing it.
you will have to change the SQL to your Table in the Database.

'Public Mydb As Database is declared in a Modul

Sub LoadCallTypes(objCombo As Object)
   
   Dim SQL As String
   Dim rsListing
   
   SQL = "SELECT CallTypeID, CallDescription FROM CallType "
   SQL = SQL & "ORDER BY CallDescription"
   
   Set rsListing = Mydb.OpenRecordset(SQL)
   
   With rsListing
      If (.RecordCount > 0) Then
         .MoveFirst
      Do While Not .EOF
            If (Not IsNull(!CallTypeID)) Then
               If (Not IsNull(!CallDescription)) Then objCombo.AddItem Trim(!CallDescription)
               objCombo.ItemData(objCombo.NewIndex) = !CallTypeID
            End If
            .MoveNext
         Loop
      End If
   End With
   
   rsListing.Close
   Set rsListing = Nothing
   
   Exit Sub
End Sub

then in Form..

 
Private Sub Form_Load()

Call LoadCallTypes(Combo1)
    Combo1.ListIndex = 0

End Sub

regards
Chris

i will try this code

thank you very much

[SamOscarBrown]

i used the code

vn.MoveFirst
Combo1.Clear
Do While Not vn.EOF
Combo1.AddItem ("vn_nm")
vn.MoveNext
Loop

So that you can LEARN (and not just copy code---i trust you wouldn't do that anyway), when you add something to your combobox, you need to reference what is in your recordset, vn.

Instead of

Combo1.AddItem ("vn_nm")

it would be something like this:

Combo1.AddItem (vn!fieldname)

where 'fieldname' is the name of the field in your table that you want to add to the combo box.

What you had would add (if there were at least one record in vn), "vn_nm" to your combobox (as many times as there were records returned).