EDIT Error Formula (VBA)

[Jerome_RA]

Hello Excel Masters,

I need help in regards to my work. I am trying to create a EDIT Querry to be connected to MS Access but it seems my code won't push thru. The Save and Delete button of my work is already done. I just need the EDIT button to work.

Here is my code. (it will be getting a syntax error (-2147217900 (80040e14) on the highlighted part below "red")

====



Private Sub cmdEdit_Click()




If txtEmployeeID.Text = "" Then
MsgBox "Nothing to Edit", vbInformation, "Tracker"
Else




Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;" & _
"Data Source=D:\EXCEL\MasterlistDB.mdb"

Set rst = New ADODB.Recordset


rst.Open "SELECT * FROM TMasterlist;", _
cnn, adOpenStatic

strsql = "update TMasterlist set CompleteName = '" & txtName.Text _
& "', LOB = '" & txtLOB.Text _
& "', Position = '" & txtPosition.Text _
& "', Supervisor = '" & txtSupervisor.Text _
& "', Manager = '" & txtManager.Text _
& "' where EmployeeID = '" & txtEmployeeID.Text & "'"




Set cmd = New ADODB.Command
cmd.CommandText = strsql
cmd.ActiveConnection = cnn
cmd.Execute


rst.Close
cnn.Close




End If






End Sub

 

[Jerome_RA]

Forgot to add Functions from the Code. Here it is below. Really having a hard time figuring this out why I cannot update the records from my database.

------------------------------------

Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public cmd As ADODB.Command
Public strsql As String

Private Sub cmdEdit_Click()

If txtEmployeeID.Text = "" Then
MsgBox "Nothing to Edit", vbInformation, "Tracker"
Else

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;" & _
"Data Source=D:\EXCEL\MasterlistDB.mdb"

Set rst = New ADODB.Recordset


rst.Open "SELECT * FROM TMasterlist;", _
cnn, adOpenStatic

strsql = "update TMasterlist set CompleteName = '" & txtName.Text _
& "', LOB = '" & txtLOB.Text _
& "', Position = '" & txtPosition.Text _
& "', Supervisor = '" & txtSupervisor.Text _
& "', Manager = '" & txtManager.Text _
& "' where EmployeeID = '" & txtEmployeeID.Text & "'"

Set cmd = New ADODB.Command
cmd.CommandText = strsql
cmd.ActiveConnection = cnn
cmd.Execute


rst.Close
cnn.Close


End If

End Sub

 

[Jerome_RA]

Hi All,

Want to update that I already figured it out.

Thank you.