Updating A Recordset

[mattster1010]

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?

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


Regards,

Matt

[Imdabaum]

I haven't done a lot with ADO recordsets, but usually when I'm updating recordsets by code, I use


.Fields("{Fieldname}") = value


Have you tried using the fields argument?

[HiTechCoach]

Matt,

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



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



Note: It would be a whole lot easier to use an Append query

[geekgirlau]

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?