How to to bind recordset to subform using ADO

[Dima09]

I am trying to bind recordset to subform using ADO and it shows only one record. Do you know what is missing here? Thank you.

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = New ADODB.Connection
conn.Open DbConnection
Set rs = New ADODB.RecordsetstrSQL = "SELECT * FROM Treatment WHERE " & strCriteria & ""

rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

PurchaserID = rs("PurchaserID")
TreatmentProvidedID = rs("TreatmentID")
ServiceDate = rs("ServiceDate")
ServiceTime = rs("ServiceTime")

End Sub

 

[MajP]

http://support.microsoft.com/kb/281998

You need to set the forms recordsource property to the recordset. You are simply setting the values of the controls equal to the values in the first record of your recordset.

 

[Dima09]

I did figure out, but now I cannot edit. Here is the code:

Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = New ADODB.Connection
conn.Open DbConnection
Set rs = New ADODB.Recordset

strSQL = "SELECT * FROM TreatmentProvided WHERE " & strCriteria & ""

rs.CursorLocation = adUseClient
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

Set Me.Recordset = rs

 

[MajP]

Only certain bound rs are editable, do you meet the conditions as outlined in the link?

 

[Dima09]

What do you mean "Only certain bound rs are editable"? Can you be more specific?

 

[MajP]

from the link

The Recordset property was introduced in Microsoft Access 2000, and it allows you to bind forms to either DAO or ADO Recordset objects. However, forms in Access 2000 support updateability only if the ADO connection is opened by using the MSDataShape and SQL Server OLEDB providers. For additional information about this limitation in Access 2000, click the article number below to view the article in the Microsoft Knowledge Base:
227053 (

http://support.microsoft.com/kb/227053/EN-US/

) ACC2000: Forms Based on ADO Recordsets Are Read-Only
In Microsoft Access 2002 or later, you can create an updateable form that is bound to an ADO recordset that uses other OLEDB providers. A form must meet several general requirements for the form to be updateable when it is bound to an ADO recordset. These general requirements are:

1. The underlying ADO recordset must be updateable.
2. The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.

The other requirements for updateability vary between different providers. This article describes what the other requirements are when you use the Microsoft SQL Server, Jet, ODBC, and Oracle OLEDB providers.