How do you populate Combo Box from ADO recordset?

[Barmaley]

Access 2000
SQL tables on the back end

I am trying to create database without linked tables
I am openning recordset directly from SQL server

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB_Path = "Provider=sqloledb;" & _
"Data Source=Myserver_Name;" & _
"Initial Catalog=My_Database_Name;" & _
"Integrated Security=SSPI"

Set cnMain = New ADODB.Connection
cnMain.Open DB_Path

Set rsExtract = New ADODB.Recordset
With rsExtract
.ActiveConnection = cnMain
.Source = "SELECT * FROM [Ministry]"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open
End With
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have ComboBox "Min_Drop_Box" and seems can't find a way to add items to
it.
List Box has AddItem, but not Combo Box.

Your help greately appretiated

AvP

 

[Brendan Reynolds]

A combo box does have an AddItem method. It was new in, if I remember
correctly, Access 2002, but it was added to the combo box in the same
version in which it was added to the list box, so if you're using a version
of Access in which list boxes have an AddItem method, then you're using a
version in which combo boxes have it, too. Unless you're using some ActiveX
control instead of the intrinsic Access controls?

You can also assign a recordset directly to the RecordSet property. Here's
examples of both methods ...

Option Compare Database
Option Explicit

Dim mrst As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=(local)"
.Open
End With

Set mrst = New ADODB.Recordset
With mrst
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Open "SELECT CategoryID, CategoryName FROM Categories"
.ActiveConnection = Nothing
End With

Set Me.Combo0.Recordset = mrst

Me.Combo2.RowSourceType = "Value List"
Do Until mrst.EOF
Me.Combo2.AddItem mrst.Fields("CategoryID") & _
";" & mrst.Fields("CategoryName")
mrst.MoveNext
Loop

End Sub

 

[Barmaley]

About my previous comment " List Box has AddItem, but not Combo Box."
I put it after reading some articles on the web without checking if it
actually has it