Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Unanswered: Updating field (with Requery or similar)

    Help needed, as I can't get a field to update on a form. The structure of the form is:

    • Form F1 (populated by On load event that reads a SQL server database)
    • The form includes 3 tabs: T1 | T2 | T3
    • T1 includes the field to be updated, let's call it "State" (it's a combo).


    I'd like either:
    • Whole tab T1 to update (requery?) when a button is clicked on T3 (this button changes the value of the field State in a SQL server table).
    • Or just the "State" combo box in T1 to requery (to 'update') when a button is clicked on T3.


    I've tried this through code (Refresh, Requery) on the click event of the button, but something is missing because it doesn't update.

    I'm looking for the general command/code (Refresh/Requery/ Other) that updates either a form, a tab in a form, or a control in a tab. Thanks in advance, a.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Form F1 (populated by On load event that reads a SQL server database)
    Exactly how is the data fetched from the SQL Server (linked table, pass-through query, other...)?
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Posts
    8
    Through code, that links to SQL server through a recordset (attached below).

    Code:
    Set rs1 = New ADODB.Recordset
    With rs1
        Set .ActiveConnection = oConn
         .Source = SQL
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
        .ActiveConnection = Nothing
    End With
    
    Set Me.Recordset = rs1

    Quote Originally Posted by Sinndho View Post
    Exactly how is the data fetched from the SQL Server (linked table, pass-through query, other...)?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you change anything at all in the form? I don't usually work with objects from the ADODB library but, as far as I know, the technique you use yields a read-only form. How is oConn defined and initialized and what's in the variable SQL?
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Posts
    8
    Quote Originally Posted by Sinndho View Post
    Can you change anything at all in the form? I don't usually work with objects from the ADODB library but, as far as I know, the technique you use yields a read-only form. How is oConn defined and initialized and what's in the variable SQL?
    I honestly don't know. I'd say you must use ADODB because the source DB is SQL Server, not Access. No way to work around that.

    Can it be updated? By using code, definitely. I think for me the main questions now are 1) whether Requery works on a code populated form, and 2) in case it does, how to use it on a given control.

    thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by a_ud View Post
    I'd say you must use ADODB because the source DB is SQL Server, not Access. No way to work around that.
    You're wrong about that: there are several other methods (DAO, RDO, linked tables via ODBC, ...) to interface an Access form with a SQL Server, that do no use ADODB.

    As far as your problem is concerned and according to MS Knowledge Base (How to bind Microsoft Access forms to ADO recordsets) :
    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:
    The underlying ADO recordset must be updateable.
    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.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •