Populate Combo box using recordset in vba (1 Viewer)

Hello All,

I am using the foll. code to populate my combo box.
Private Sub cmb_firststage_Click()
Dim rs_main As ADODB.Recordset
Set rs_main = New ADODB.Recordset
Dim cnn_main As ADODB.Connection
Set cnn_main = CurrentProject.Connection
rs_main.ActiveConnection = cnn_main
rs_main.LockType = adLockOptimistic
rs_main.CursorType = adOpenDynamic
rs_main.Source = "SELECT DISTINCT [MFS_Sheet].[Field1] FROM MFS_Sheet"
rs_main.Open
rs_main.MoveFirst
Do While rs_main.EOF
cmb_firststage.additem rs_main!Field1
rs_main.MoveNext
Loop

'==============='

Dim rs_firststage As ADODB.Recordset
Set rs_firststage = New ADODB.Recordset
Dim cnn_firststage As ADODB.Connection
Set cnn_firststage = CurrentProject.Connection
rs_firststage.ActiveConnection = cnn_firststage
rs_firststage.LockType = adLockOptimistic
rs_firststage.CursorType = adOpenDynamic
rs_firststage.Source = "SELECT [MFS_first].[Field2] FROM MFS_first"
rs_firststage.Open
If Me.cmb_firststage = "1st Stage Drum" Then
rs_firststage.MoveFirst
Do While rs_firststage.EOF
Forms!FilterList!cmb_firststage_elements.additem rs_firststage!Field2
rs_firststage.MoveNext
Loop
rs_firststage.Close
End If


Now, my first recordset rs_main works and populates the combo box (cmb_firststage) perfectly. However, I am not able to populate the other combo box (cmb_firststage_elements). Where am I going wrong ?

Thanks!

Nik

Why not just use a select query as the row source? It'd be a million times easier.

well, I tried :

If Me.cmb_firststage = "1st Stage Drum" Then
Do While rs_firststage.EOF
cmb_firststage_elements.RowSource = rs_firststage!Field2
Loop

..but does not work..

I did not get the SQL statment method ?

thanks for your concern!

npatil,

Take a look at this free video tutorial. It shows how to populate a combo with some SQL

http://www.datapigtechnologies.com/flashfiles/combobox1.html

There are samples with 2 comboboxes also.
http://www.datapigtechnologies.com/flashfiles/combobox2.html
http://www.datapigtechnologies.com/flashfiles/combobox3.html

Good luck

I'd have to agree.
You're using the CurrentProject.Connection returned object, and so this is a locally accessible table or query that you're opening. So you can assign a RecordSource property directly.

I should point out that there are occasions where you might still want to assign a recordset to a list control - but you're not using anything which looks like that is likely.
FWIW if you're using newer than Access 2000, you can directly assign the recordset to a list control (with its RowSourceTye property set at Table/Query).
Set Me.cmb_firststage_elements.Recordset = rs
(There are also caveats about how you open the recordset...)

But generally here you'd be using
Me.cmb_firststage_elements.RowSource = "SELECT [MFS_first].[Field2] FROM MFS_first"

The above mentioned video tutorial will show you where to specify that manually, in case you're not familiar with it.

Cheers.