How do you populate Combo Box from ADO recordset?

  • Thread starter Thread starter Barmaley
  • Start date Start date
B

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
 
So, is there a way to do it in Access 2000?

Van T. Dinh said:
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)


Barmaley said:
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
Click to expand...
Click to expand...
 
AddItem is not available in A2K0. It was introduced in A2K2.

Check Access VB Help on the Recordset Property of the ComboBox.
 
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!)



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

Van T. Dinh said:
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)


Barmaley said:
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
Click to expand...
Click to expand...
Click to expand...
 
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???
 
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"
 
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
 
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




Douglas J. Steele said:
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!)



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

Van T. Dinh said:
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
Click to expand...
items
to
it.
List Box has AddItem, but not Combo Box.

Your help greately appretiated

AvP
Click to expand...
Click to expand...
Click to expand...
 
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?
 
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!)



Barmaley said:
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




Douglas J. Steele said:
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!)



Barmaley said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top