locked
Can you add comments to an append-only field in a Sharepoint list using Access2010? RRS feed

  • Question

  • I can use the following connection string to write to all fields but not the field [Actions Taken] field that is Append Only with Versioning turned on:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = Application.CurrentProject.Connection
    Set rs = New ADODB.Recordset
    Dim strRS As String
    strRS = "SELECT qryJD_SRs.* FROM qryJD_SRs WHERE qryJD_SRs.ID=" & Me.ID
    
    rs.Open strRS, cn, adOpenKeyset, adLockOptimistic
    This line of code works to write data to a field:

    rs.Fields("Details").Value = "Test Details Field" 

    This line of code does not work:

    rs.Fields("Actions Taken").Value = "Test Actions Taken Field"

    Both the [Details] and the [Actions Taken] fields are Memo and Rich Text. The [Actions Taken] field is Append Only = Yes

    If I use DAO the following will write data to a field:

    Private Sub addNewAction_Click()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM [Service Requests] WHERE [Service Requests].ID = " & Me.ID
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
    
    With rs
    .Edit
    ![Details] = "Monday test"
    .Update
    End With
    
    rs.Close
    Set rs = Nothing
    End Sub

    Unfortunately, this DAO below will NOT write data to the Append Only field:

    Private Sub addNewAction_Click()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM [Service Requests] WHERE [Service Requests].ID = " & Me.ID
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
    
    With rs
    .Edit
    ![Actions Taken] = "Monday test"
    .Update
    End With
    
    rs.Close
    Set rs = Nothing
    End Sub

    Any suggestions?...JD


    JohnDurbin

    Monday, August 13, 2012 6:53 PM

All replies

  • You would need to use Recordset2 object, not Recordset object. Recordset2 object has been enhanced to work with newer features, including Append-only fields. Likewise, you would use Field2 instead of Field.

    I'm not aware of any support within ADO to do the equivalent operation.

    Monday, August 13, 2012 9:25 PM
  • Hello and thank you for the reply.  Could you give me a bit more info?

    If I modified the code to use Recordset2, how would I modify the code to use Field2?

    Private Sub addNewAction_Click()
    Dim rs As DAO.Recordset2
    Dim strSQL As String
    strSQL = "SELECT * FROM [Service Requests] WHERE [Service Requests].ID = " & Me.ID
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
    
    With rs
    .Edit
    ![Actions Taken] = "Monday test"
    .Update
    End With
    
    rs.Close
    Set rs = Nothing
    End Sub

    I've tried several different alterations but none have achieved success.  Can you help me?...JD


    JohnDurbin

    Tuesday, August 21, 2012 2:02 PM