Can't input in Textbox controls using Continuous Form bound to an ADO Recordset

Question

Hi all,

Tried to use a Continuous Forms bound to na ADO Recorset and can't make it work.

My form has a few Textboxes and if I use na ADO recodset those controls (textbox) doesn't receive key input. This means none of the textbox events fires up.

If I change to a single sql sentence in the RecordSource property of the form all works well (obvious creating a native DAO recordset)

I even use the  Reference Manual code exemple to to this and nothing happens

Private Sub Form_Open(Cancel As Integer)
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
            
      'Use the ADO connection that Access uses
      Set cn = CurrentProject.AccessConnection
      'Create an instance of the ADO Recordset class, 
      'and set its properties
      Set rs = New ADODB.Recordset
      With rs
         Set .ActiveConnection = cn
         .Source = "SELECT * FROM Customers"
         .LockType = adLockOptimistic
         .CursorType = adOpenKeyset
         .Open 
      End With
      'Set the form's Recordset property to the ADO recordset
      Set Me.Recordset = rs
      Set rs = Nothing
      Set cn = Nothing
   End Sub

Wierd, don't know what to think ... Have to use DAO where I need to make input.

Try with Access 2007 in Win7 and 2010 in Win 8, same result.

João

---

Joao Simplicio Rodrigues

Answer 1

Tried to use a Continuous Forms bound to na ADO Recorset and can't make it work.

My form has a few Textboxes and if I use na ADO recodset those controls (textbox) doesn't receive key input. This means none of the textbox events fires up.

If I change to a single sql sentence in the RecordSource property of the form all works well (obvious creating a native DAO recordset)

I even use the  Reference Manual code exemple to to this and nothing happens

Private Sub Form_Open(Cancel As Integer)
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
            
      'Use the ADO connection that Access uses
      Set cn = CurrentProject.AccessConnection
      'Create an instance of the ADO Recordset class, 
      'and set its properties
      Set rs = New ADODB.Recordset
      With rs
         Set .ActiveConnection = cn
         .Source = "SELECT * FROM Customers"
         .LockType = adLockOptimistic
         .CursorType = adOpenKeyset
         .Open 
      End With
      'Set the form's Recordset property to the ADO recordset
      Set Me.Recordset = rs
      Set rs = Nothing
      Set cn = Nothing
   End Sub

Wierd, don't know what to think ... Have to use DAO where I need to make input.

You also have to set the recordset's CusorLocation property to adUseClient:

      With rs
         Set .ActiveConnection = cn
         .Source = "SELECT * FROM Customers"
         .LockType = adLockOptimistic
         .CursorType = adOpenKeyset
         .CursorLocation = adUseClient
         .Open 
      End With

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Hi,

Thank you for reply. Did that you sugest, but didn't work.

For full disclousere, the form is use as a subform.

Nevertheless if I use the RecordSource property (creating the native DAO recordset) with the same "query string" that I use to create the ADO recordset, all works fine. Also try name the fields instead of *.

My empiric conclusion is that the question aren't around the controls settings/properties in the form.

I try bound the textbox directly in design mode, tried programmatically the result is the same. It shows the data, if there are data in the table, can't change or add, even doesn't activate the textbox events

João

---

Joao Simplicio Rodrigues

Answer 3

When I open a recordset for editing I include the argument dbSeeChanges.

Set rsTemplate = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

When I open a recordset for editing I include the argument dbSeeChanges.

Set rsTemplate = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

Bill, in this case the issue is with binding an ADO recordset to a form.  There's no problem with DAO.

Incidentally, I thought dbSeeChanges was only necessary when dealing with SQL server, or maybe some other client-server database, not when working with a JET/ACE database.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Interesting.  Almost the only difference between your code and the code I used in my own form is that I used CurrentProject.Connection for the connection, while you used CurrentProject.AccessConnection.  I wouldn't expect that to make a difference, but I just tested it, and it does.  Try this:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
          
    'Use the ADO connection that Access uses
    Set cn = CurrentProject.Connection
    'Create an instance of the ADO Recordset class,
    'and set its properties
    Set rs = New ADODB.Recordset
    With rs
        Set .ActiveConnection = cn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .Source = "SELECT * FROM Customers"
        .Open
    End With
    'Set the form's Recordset property to the ADO recordset
    Set Me.Recordset = rs
    Set rs = Nothing
    Set cn = Nothing

It works for me, though I didn't try it in a subform.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 6

Well, in the begin I use CurrentProject.Connection and didn't work.
So I looked into the Access Help and saw the CurrentProject.AccessConnection code and I tried to use it.
But the result was the same.
The most strange thing is that the control (textbox) event has no reaction, but if I change the recordset to DAO works as expected.
In doubt my colleague tested on another machine with Office 2007 and Win 2007 and confirmed the situation.

João

---

Joao Simplicio Rodrigues

Answer 7

Well, in the begin I use CurrentProject.Connection and didn't work.
So I looked into the Access Help and saw the CurrentProject.AccessConnection code and I tried to use it.
But the result was the same.

My test form is working, even as a subform, using Access 2010 under Vista.  Can you prepare a small database to demonstrate the problem, and either e-mail it to me, or post it where I can download it for debugging?

The most strange thing is that the control (textbox) event has no reaction, but if I change the recordset to DAO works as expected.

This doesn't seem at all strange to me.  If the the recordset is not updatable, all that will happen if you try to modify the data in a bound text box is that a message will be displayed in the status bar, saying "This recordset is not updatable".  It would be easy to overlook that, and you may not even be displaying the status bar.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 8

Hi Dirk,

Thank you for your willingness to help.
Obviously I adopted the DAO solution, the business does not stop.
As soon as possible I will do what I suggest.

João.

---

Joao Simplicio Rodrigues