[AccessD] Setting A2K combo box rowsource using ADO

The rowsource property for a combobox doesn't accept a recordset, it
accepts the name of a table or query or a SQL statement.  In other
words, a string.

Charlotte Foust

-----Original Message-----
From: jeffrey.demulling at usbank.com [mailto:jeffrey.demulling at usbank.com]

Sent: Wednesday, August 13, 2003 7:40 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Setting A2K combo box rowsource using ADO


Below is the code I have for a form.  I am using A2K and connecting to a
SQLBase db using ADO.  What I want to do is set my combo box's rowsouce
using the code below.  First I check to make sure that I can get to the
database and if so I want to set the rowsouce accordingly.

The problem I run into is that I get a compile error for a Type
MisMatch. Any help is greatly appreciated.

TIA
-Jeff

Code:

Option Compare Database
Option Explicit
Private rsName As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)
Dim con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim cmdtext As String
Dim mymessage As String

'Set the default status of the buttons here
Me.btnExit.Enabled = True
Me.btnSearch.Enabled = True
Me.lblProgress.Visible = False
Me.cmbACSName.Visible = False
DoEvents

Me.lblProgress.Caption = "Retrieving active ACS users...."
Me.lblProgress.Visible = True DoEvents

'Check to see if we can connect to ACS
con = ACSconnection
con.Open

If Not CBool(con.State And adStateOpen) Then
    con.Close
    'Could NOT connect to ACS
    mymessage = "ACS User Center could not establish a connection to
ACS." & Chr(13) & Chr(10)
    MsgBox mymessage, vbCritical, "Error: Unable to Connect to ACS"

    Me.lblProgress.Caption = "Error: Could not connect to ACS..."
    DoEvents
    Exit Sub
Else
    con.Close
End If

cmdtext = "SELECT ALL"
cmdtext = cmdtext & " UADMINKEY,"
cmdtext = cmdtext & " UADMINNO,"
cmdtext = cmdtext & " (CLAST || ', ' || CFIRST) AS FULLNAME," cmdtext =
cmdtext & " USERNAME" cmdtext = cmdtext & " FROM PASSWRD," cmdtext =
cmdtext & " NAME" cmdtext = cmdtext & " WHERE" cmdtext = cmdtext & "
PASSWRD.UADMINNO = NAME.CNAMEIN(+)" cmdtext = cmdtext & " AND" cmdtext =
cmdtext & " (PASSWRD.UACTIVE = 'N')"

Set rsName = New ADODB.Recordset
rsName.CursorLocation = adUseClient
rsName.Open cmdtext, setconnection, adOpenKeyset, adLockReadOnly
Me.cmbACSName.RowSource = rsName End Sub


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com