How can I populate combo box from a database query?

Question

I want to populate a combo box with the results of a query in Access. I'm just not seeing how to do it. As far as I understand, one must first create a record set, read the query results into the record set, then write the record set to the combo box's row source property. Is this correct? is there a simple example somewhere that I can follow? I haven't found one in any of the other threads.

Here's my attempt so far:

    Dim RS As Recordset
    Dim myDB As Database

    Set RS = myDB.OpenRecordset("SourcesNotDisposed", dbOpenDynaset)
    Do While Not RS.EOF
    With Me.cmbSN
        RowSource.AddItem
    End With
    Loop

With this code, I'm getting an "Object required" error at the RowSource line. cmbSN has data properties: Row source Type = Table/Query Bound Column = 0 Limit to List = Yes Allow value list edits = Yes Inherit value list = Yes Show only row source = No

The query only has one visible column called "Serial Number"

Thanks in advance

Answer 1

Thanks for all the suggestions everyone. I've worked it out and found a very simple solution. With the combo box's Row Source Type property set to Table/Query, all I needed to do was set the Row Source property to a valid SQL string. e.g.:

strSQL = "SELECT Sources.[Serial Number] FROM Sources " & _
         "WHERE (((Sources.Nuclide)='Cf-252') " & _
         "AND ((Sources.[Location / Status])<>'Disposed')) " & _
         "ORDER BY Sources.[Serial Number];"
Me.cmbItem.RowSource = strSQL
Me.cmbItem.Requery

Answer 2

Below code insert table fields into combo box. Add this code under onEnter event of combo

 Private Sub  CM_Enter()

  'CM is  combobox name

    Dim strItem1 As String
    Dim strItem2 As String

   On Error Resume Next

   Dim i As Integer 'Index for loop
   With Me.CM
    .RowSourceType = "Value List" 'Set rowsource type as Value list
    .RowSource = "" 'Clean combo contents
  End With

 'Loop through field names of table and add them to your combo:
    For i = 1 To CurrentDb.TableDefs("table1").Fields.Count - 1
    Me.CM.AddItem (CurrentDb.TableDefs("table1").Fields(i - 1).Name)

  Next i

 '/***Delete unwanted items from the combo
   strItem1 = "col1"
   strItem2 = "col2"
  'CM.RemoveItem strItem1
  'CM.RemoveItem strItem2

  End Sub

Answer 3

I think you might need to do a 'first read a record' before starting the loop.

Try using a RS.MoveFirst before the Do-While loop?

I think you may also need to do a .MoveNext inside your loop, just before the Loop statement; it's been a long while since I did anything like this in VBA, but it looks to me like it'll just add the same item over and over until it runs out of memory? I don't think the AddItem moves the record pointer to the next record by itself.

You may also need to check what happens if you MoveNext off the end of the record set...

HTH :)