Updating a recordset in Access 2010 with an Excel 2010 userform

[cjohnson78]

I'm new to this forum and a rookie so please bare with me.

Current Errors:
1.)Syntax error(Missing operator)In query expression '[Technician_Name]ID-'38'.
2.)Operation is not allowed when the object is closed.
3.)Object variable or with block variable not set.

I'm using Access 2010 and a separate workbook in Excel 2010.
I have a userform in Excel called "frmQueServiceCall" with a
couple of text boxes, "txtDateReceived" & "txtTimeReceived". This userform is intended
to update recordsets in my Access database in a Table Called "Service Board". The column names are "Technician_Name", "Date_Received" & "Time_Received". What I'm trying to do is
an update record and I can't seem to figure out the proper code structure
to do the update. I've searched and searched through the internet for answers but haven't been able to figure out how use what others have suggested. I've tried many different things and here is what I stopped trying with that obviously doesn't work.


Dim Cn As ADODB.Connection, _
Rs As New ADODB.Recordset, _
oCm As ADODB.Command, _
iRecAffected As Integer

On Error GoTo ADO_ERROR

Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Chris Johnson\My Documents\SCID Base.accdb;Persist Security Info=False"

Set Rs = New ADODB.Recordset

Rs.CursorType = adOpenKeyset
Rs.LockType = adLockOptimistic
Rs.Open "SELECT [Technican_Name], [Date_Received], [Time_Received]FROM [Service Board] WHERE [Technician_Name]ID='38'", Cn, , , adCmdText

With Rs
Rs.Fields(1).Value = frmQueServiceCall.txtDateReceived.Value
Rs.Fields(2).Value = frmQueServiceCall.txtTimeReceived.Value
.Update

End With

oCm.Execute iRecAffected

If iRecAffected = 0 Then
MsgBox "No records inserted"

End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing
If Not Rs Is Nothing Then Set Rs = Nothing

ADO_ERROR:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
Resume Next

End If

End Sub

Thank you in advance to any that reply.

 

[Rkay]

Hi CJohnson,
I think it would be easier if you put a form in the access db and do updates from there instead of excel


As for the top 3 problems:
1. What do you mean when you write [Technician_name]Id = '38'...Is it rowId?
2. Try opening the db before you update (Connection.open)
3. replace

With Rs
Rs.Fields(1).Value = frmQueServiceCall.txtDateReceived.Value
Rs.Fields(2).Value = frmQueServiceCall.txtTimeReceived.Value
.Update

End With

With:

With rs
.Edit
![Field1] = new value to add
![Field2] = new value to add
.Update
End with