[AccessD] Setting A2K combo box rowsource using ADO - SOLVED

Try Rowsource instead of Recordset

This is an example I got somewhere

if g_oConnection = OpenConenction then
     Dim oRes As ADODB.Recordset
     Dim sListText As String

     Set oRes = New ADODB.Recordset

     Me.cboMyList.ColumnCount = 2
     Me.cboMyList.BoundColumn = 1
     Me.cboMyList.ColumnHeads = False
     Me.cboMyList.ColumnWidths = "0;1"
     Me.cboMyList.RowSourceType = "Value List"
     Me.cboMyList.DefaultValue = 0

     oRes.Open "select * from tbMyTable order by label", g_oConnection,
adOpenDynamic, adLockOptimistic

     sListText = "0;'<Anything>';"
     While oRes.EOF = False
           sListText = sListText & oRes("ID").Value & ";" &
oRes("Label").Value & ";"
           oRes.MoveNext
       Wend
       oRes.Close
       Me.cboMyList.RowSource = sListText

       Set oRes = Nothing
       CloseConnection
   end if



----------------------------------------------------------------------------
--------------------------------

----- Original Message ----- 
From: <jeffrey.demulling at usbank.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, August 14, 2003 9:32 AM
Subject: RE: [AccessD] Setting A2K combo box rowsource using ADO - SOLVED


>
> I am doing it using XP but the db format is A2K.  Can only do this through
> code.  I am doing this in the on open event and I leave the combo box's
> rowsource blank but the rowsource type is set to "Table/Query".  There are
> no tables with the program.  I am doing everything through ADO.  The
> thought being that when this moves from an Access db to a SQL server I
will
> only have to change the setconnection module to point to a SQL server and
> not an Access db for getting the app working quickly.  Then I would be
> going back and creating the SPs and changing the code where needed. This
> will not work in A97.
>
> Here is a code sample:
>
> Private Sub Form_Open(Cancel As Integer)
> Dim con As New ADODB.Connection
> Dim RS As New ADODB.Recordset
> Dim cmdtext As String
>
> Me.txtContactName = ""
> Me.txtCompanyName = ""
> Me.txtPhone = ""
> Me.txtFax = ""
> Me.txtE_Mail = ""
> Me.cmbDeliveryMethod = ""
>
> Me.txtContactName.SetFocus
>
> cmdtext = "SELECT DISTINCTROW"
> cmdtext = cmdtext & " tblDeliveryMethods.DeliveryNumber,"
> cmdtext = cmdtext & " tblDeliveryMethods.DeliveryName"
> cmdtext = cmdtext & " FROM tblDeliveryMethods;"
>
> Set rsName = New ADODB.Recordset
> rsName.CursorLocation = adUseClient
> rsName.Open cmdtext, setconnection, adOpenKeyset, adLockReadOnly
> Set Forms![frmContacts-Add].cmbDeliveryMethod.Recordset = rsName
> End Sub
>
>
>
>                     "John Colby"
>                     <jcolby at colbyconsulting.co        To:     "Access
Developers discussion and problem solving"
>                     m>
<accessd at databaseadvisors.com>
>                     Sent by:                          cc:
>                     accessd-bounces at databasead        Subject:     RE:
[AccessD] Setting A2K combo box rowsource using ADO -
>                     visors.com                        SOLVED
>
>
>                     08/14/2003 11:17 AM
>                     Please respond to "Access
>                     Developers discussion and
>                     problem solving"
>
>
>
>
>
>
> Is this A2K?  I don't find a recordset property for a combo in A2K.
>
> John W. Colby
> www.colbyconsulting.com
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
> jeffrey.demulling at usbank.com
> Sent: Thursday, August 14, 2003 11:51 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Setting A2K combo box rowsource using ADO - SOLVED
>
>
>
>
> Just wanted to let you know that I solved my problem.
>
> The first step was to use the right connection string (was using the MS
SQL
> provider instead of the SQLBASE).
>
> The next step was to change the following:
>
> Me.cmbACSName.RowSource = rsName
>
> to
>
> Set Forms![frmDisableUser].cmbACSName.Recordset = rsName
>
> Now everything is working just fine.
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>