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

  • 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

    Tuesday, May 17, 2016 7:32 PM

All replies

  • 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

    Tuesday, May 17, 2016 8:48 PM
  • 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

    Wednesday, May 18, 2016 1:07 PM
  • 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

    Wednesday, May 18, 2016 3:23 PM
  • 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

    Thursday, May 19, 2016 1:44 PM
  • 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

    Thursday, May 19, 2016 2:02 PM
  • 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

    Monday, May 23, 2016 6:51 PM
  • 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

    Tuesday, May 24, 2016 5:41 PM
  • 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

    Tuesday, May 31, 2016 9:43 AM