Consulting

Results 1 to 4 of 4

Thread: Updating A Recordset

  1. #1

    Updating A Recordset

    Morning All,

    I'm using the below code to add a new record to the 'Contact History' table. The code doesn't error in any way but no new record is added to the table? Can anyone advise if there is something wrong with the code?

    [vba]Private Sub Command21_Click()
    On Error Resume Next
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source = S:\Customer_Sat_DB\Original\Survey_v0.1.mdb"
    objRecordSet.Open "SELECT Contact_History.* " & _
    "FROM Contact_History " & _
    "WHERE CustomerID = " & Me.CustomerID, objConnection, adOpenStatic, adLockOptimistic
    objRecordSet.AddNew
    objRecordSet("Contact_Date") = Me.Contact_Date1
    objRecordSet("Contact_History") = Me.Contact_History1
    objRecordSet("UserID") = Me.UserID1
    objRecordSet.Update
    objRecordSet.Close
    objConnection.Close
    End Sub
    [/vba]

    Regards,

    Matt

  2. #2
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    I haven't done a lot with ADO recordsets, but usually when I'm updating recordsets by code, I use

    [VBA]
    .Fields("{Fieldname}") = value
    [/VBA]

    Have you tried using the fields argument?
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  3. #3
    Matt,

    It may be a RI issue. You are not setting the CustomerID field in the new record.

    [VBA]

    objRecordSet.AddNew
    objRecordSet("CustomerID") = Me.CustomerID

    [/VBA]

    Note: It would be a whole lot easier to use an Append query
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Are you basing your assumption that it's not working based on what you see on the form, or did you open the table to have a look?

Posting Permissions

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