Problem with Update query

[Gepetto]

Hi,
i have code with error, but don't see why.

Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Computers set [Network] = (select [Network] from Computers where (((Computers.[Serial]) = '" & Me.txtSerial & "'))) where ((([Serial]) = '" & Me.txtSerialNew & "'))"

Set db = CurrentDb
db.Execute strSql

Error is:

Run-time error '3061':
Too few parameters. Expected 1.

Thanks for advance!

 

[JANR]

Try this:

strSql = "UPDATE Computers set [Network] = (select [Network] from Computers where (((Computers.[Serial]) = '" & Me.txtSerial & "')) [COLOR=red]AND[/COLOR] ((([Serial]) = '" & Me.txtSerialNew & "'))"

JR

 

[Gepetto]

No, i've got the same error again

 

[namliam]

@JNR if we re-write the sql

strSql = ""
strSql = strSql & " UPDATE Computers set [Network] = ( "
strSql = strSql & "      select [Network]  "
strSql = strSql & "      from   Computers "
strSql = strSql & "      where Computers.[Serial] = '" & Me.txtSerial & "' "
strSql = strSql & " ) "
strSql = strSql & " where [Serial] = '" & Me.txtSerialNew & "' "

Msgbox strSQL

we see that the 2nd where belongs to the Update query, not the subselect

Try above peace of code and tell us the SQL, make sure your "Serial" is actually "Serial" and there is not a simple type anywhere

 

[JANR]

my bad :-(

 

[Gepetto]

Msgbox note

UPDATE Computers set [Network]=( select[Network] from Computers where Computers.[Serial]='79223TP' ) where [Serial]='701964K'

 

[namliam]

Looks right to me?!

 

[Gepetto]

I'm create a update query where filed Computers.[Network] is updated to [forms]![frmExistingForm]![txtNetwork]. Criteria is: Computers.[Serial] is [forms]![frmExistingForm]![SerialNew].
This works perfect. Maybe is now articulated how to write VBA code.
Thanks for assistance!

 

[Gepetto]

Or:

UPDATE Computers SET Computers.[Network] = [forms]![frmExistingForm]![Network]
WHERE (((Computers.[Serial])=[forms]![frmExistingForm]![SerialNew]));