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

You will have to use a call-back in A2K or A97 if you are only using ADO and
not binding the control to a query or table.

IF you jump up to SQL, you can simply set the rowsource to a view (as you
would currently a query) or Stored procedure.

A common line of code I use in my A2K ADP (when passing an input parameter)
is 

Private Sub Form_Load()
	Me.lstYourListBox.rowsource = "EXEC YourStoredProcedure
YourParameter,rs"
End Sub

HTH

David McAfee
-----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 9:32 AM
To: Access Developers discussion and problem solving
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