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

Use of recordset in this way is new in AXP.  I know this because I was
having a lot of trouble with a form that kept getting corrupt in A2K and
I ended up tracing it back to the use of code like what is below for
listboxes or combo boxes.

	Me.lstYourListBox.rowsource = "EXEC YourStoredProcedure
YourParameter,rs" End Sub

After a lot of trouble shooting with MS, they ended up telling me to
switch my code to the use of recordsets for listboxes and combo boxes
when I upgraded to XP.  I did and it has eliminated the corruption.
That said, I still use the rowsource syntax in lots of places b/c it's a
lot less code.

--Susan



-----Original Message-----
From: David McAfee [mailto:DMcAfee at haascnc.com] 
Sent: Thursday, August 14, 2003 1:38 PM
To: 'Access Developers discussion and problem solving'
Cc: 'jeffrey.demulling at usbank.com'
Subject: RE: [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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com