MS Access - UPDATE SQL Query Error 3061

Question

I try to update data in a table with text delivered from a InputBox.

Private Sub editname_Click()

 Dim GivenNameTB As String
 Dim EnterName As String
 Dim SQl As String
 Dim LocationID As Integer


 Me.txt_name.SetFocus
 GivenNameTB = Me.txt_name.Text
 EnterName = InputBox("Change name", "Change name", GivenNameTB)
 LocationID = Me.list_ma.Column(1)

 SQl = " Update tWorkers SET GivenName = forms!mainform!EnterName WHERE tWorkers.IDName = forms!mainform!LocationID "

 CurrentDb.Execute SQl




End Sub

However, I get error code 3061 "Too few parameters. Expected 2"

EDIT:

The table structure of tWorkers:

IDName - auto-increment (primary key)
LastName - text
GivenName - text

I'm targeting column GivenName by SET GivenName = ..., and the row by LocationID.

LocationID gets its value from the list field list_ma. The list field consists of five columns whereas IDName is column 2.

My whole point is to update a field in a table. A text box in my form shows a name which can be edited by clicking a button. Then a inputbox pops up. The entered string should be saved in the desired field.

Answer 1

I think you need DoCmd.RunSQL rather than CurrentDb.Execute.

Private Sub editname_Click()

 Dim GivenNameTB As String
 Dim EnterName As String
 Dim SQl As String
 Dim LocationID As Integer

 Me.txt_name.SetFocus
 GivenNameTB = Me.txt_name.Text
 EnterName = InputBox("Change name", "Change name", GivenNameTB)
 LocationID = Me.list_ma.Column(1)

 SQL = " Update tWorkers SET GName = " & chr(34) & EnterName & chr(34) & " WHERE tWorkers.IDName = " & LocationID

 Debug.Print SQL -- For checking what code we are running.

 DoCmd.RunSQL SQL 

End Sub

Answer 2

Re-reading your question, your data lives in VBA variables, not in form controls. So you can't read the parameters from the form (DoCmd.RunSQL won't help).

You must construct the SQL string dynamically, best using CSql() by Gustav :

SQL = "Update tWorkers SET GivenName = " & CSql(EnterName) & _
      " WHERE tWorkers.IDName = " & CSql(LocationID)
Debug.Print SQL
CurrentDb.Execute SQL