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

 

[Barmaley]

So, is there a way to do it in Access 2000?

AddItem is not available in A2K0. It was introduced in A2K2.

Check Access VB Help on the Recordset Property of the ComboBox.

--
HTH
Van T. Dinh
MVP (Access)

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

 

[Van T. Dinh]

AddItem is not available in A2K0. It was introduced in A2K2.

Check Access VB Help on the Recordset Property of the ComboBox.

 

[Douglas J. Steele]

Why not create a pass-through query, and use it as the source for the
ComboBox?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, is there a way to do it in Access 2000?

AddItem is not available in A2K0. It was introduced in A2K2.

Check Access VB Help on the Recordset Property of the ComboBox.

--
HTH
Van T. Dinh
MVP (Access)

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

 

[Van T. Dinh]

Try setting the Recordset of the ComboBox like:

Set Me.ComboBox.Recordset = rsExtract

Did you really read Access VB help as I advised in the previous post???

 

[Barmaley]

I did try to find in Access Help anything that would help me.
I browsed through all properties of Combo Box trying to find anything that
would do what I need
'Recordset' isn't available in Access 2000 - it gives me a Compile Error :
"Method or data member not found"

 

[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

 

[Barmaley]

If I understand correct, to do pass-through query you would need to do ODBC
connection and linked tables (thats what I wanted to avoid)
If I am wrong - please correct me

Why not create a pass-through query, and use it as the source for the
ComboBox?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, is there a way to do it in Access 2000?

AddItem is not available in A2K0. It was introduced in A2K2.

Check Access VB Help on the Recordset Property of the ComboBox.

--
HTH
Van T. Dinh
MVP (Access)


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]

I don't have Access 2000 on this PC to test, but, I think both the AddItem
method and the Recordset property may have been new in Access 2002. If the
number of records is small, you could do it by changing the RowSourceType
property of the combo box to Value List, and concatenating the values from
the recordset into a semi-colon delimited string, and assigning that to the
ControlSource property of the combo box. But I believe there's a fairly
stringent limit on the length of that string. I can't remember exactly what
the limit is. Anyone else?

Why so anxious to avoid linked tables anyway?

 

[Douglas J. Steele]

You don't need linked tables for pass-through queries. A pass-through query
runs on the server, so it doesn't know anything about tables defined in
database.

Yes, you need to use ODBC, but that doesn't mean you have to have a DSN.
When you've created the pass-through query, set its ODBC Connect Str
property to

ODBC;DRIVER={sql
server};DATABASE=My_Database_Name;SERVER=MyServer_Name;Trusted_Connection=Yes;"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If I understand correct, to do pass-through query you would need to do
ODBC
connection and linked tables (thats what I wanted to avoid)
If I am wrong - please correct me

Why not create a pass-through query, and use it as the source for the
ComboBox?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, is there a way to do it in Access 2000?

message
AddItem is not available in A2K0. It was introduced in A2K2.

Check Access VB Help on the Recordset Property of the ComboBox.

--
HTH
Van T. Dinh
MVP (Access)


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