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.

score 1 · Answer 1 · edited May 23 '17 at 12:10

1

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

edited May 23 '17 at 12:10

Community

1
1

answered May 02 '17 at 14:27

Andre

26,751
7
36
80

Steve Lovell · Accepted Answer · 2017-05-02T14:30:08.407

0

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

edited May 02 '17 at 14:30

answered May 02 '17 at 12:23

Steve Lovell

2,564
2
13
16

Some explanation would be useful. e.g. https://social.msdn.microsoft.com/Forums/office/en-US/71392d23-867f-444f-a333-874b3f7eaea8/docmdrunsql-vs-dbexecute?forum=accessdev or http://www.utteraccess.com/forum/CurrentDbExecute-Docmd-t1654908.html&p=1654938#entry1654938 – Andre May 02 '17 at 12:49
With DoCmd.RunSQL it works, but Access asks for allowance to update "0" records. Somehow the identification via the ID IDName doesn't seem to work. Any suggestions? :/ – edorius May 02 '17 at 12:59
The code does look a little odd to me. Can you show us your table structures, some sample data (anonymised if necessary) and let us know what the code we are correcting is supposed to do. As written it looks like it targets all the rows where the IDName field matches the LocationID on the form. That sounds odd, but not knowing your data makes it impossible to tell without further information. – Steve Lovell May 02 '17 at 13:05
I've made some edits which may help (not too sure). If you continue to have problems, please let us know the text which this code puts into the Immediate Window (can't see it? press CTRL+G). – Steve Lovell May 02 '17 at 13:55
1

I had to change char(34) to Chr(34) - None the less, it Works - Thank you!! – edorius May 02 '17 at 14:29
Ah yes, I tend to forget that VBA uses `chr` not `char`. – Steve Lovell May 02 '17 at 14:31

MS Access - UPDATE SQL Query Error 3061

2 Answers2