Error on executing update query

[jkmb]

I keep getting the following errors while executing an UPDATE query inside the VBA code :banghead:

Too few parameters. Expected <number>. (Error 3061)
<Message> in query expression <expression>. (Error 3075) => syntax error (missing operator) query expression “test test”

The UPDATE query is
dbs.Execute ("UPDATE TableEmployee SET TableEmployee.Emp_Name = " & [searchEmpName] & " WHERE TableEmployee.Emp_Id = " & finalNumber & " ")

The variable “searchEmpName” contains the value from the text box on the form. This is a string (name of employee) and can be with space or with put space. If this field has NO space, the error is
Too few parameters. Expected <number>. (Error 3061)
If this field has space then error is
<Message> in query expression <expression>. (Error 3075) => syntax error (missing operator) query expression “test test”

Any help is highly appreciated.

 

[spikepl]

Do not do too much in one shot.

For SQL in VBA do like this:

Dim strSQL as String

strSQL= "UPDATE TableEmployee SET TableEmployee.Emp_Name = " & [searchEmpName] & " WHERE TableEmployee.Emp_Id = " & finalNumber & " "

debug.print strSQL 

dbs.Execute strSQL

debug.print prints the SQL into the immediate window. Copy that text and paste it into the SQLview of a new query in the query designer. Play with it in the designer until it works.

In the SQLView, you now have a working SQL statement. Now change your SQL string in the VBA code so the SQL matches the working statement.

 

[jkmb]

i used ' ' like below:

('" & searchEmpName & "') and it worked !!!