asked on

sql VBA UPDATE too few parameters

Hello,

I have the below statement that I tried 2 different ways. don't know what I have wrong?

the first one gives too few parameters and the second says It can't find the table

strSQL = "UPDATE tblUser " _
                & "SET Checked = -1" _
                & " WHERE FName = " & Me.FName
                Debug.Print strSQL
                    CurrentDb.Execute strSQL, dbFailOnError
strSQL = "UPDATE tblUser " & _
          "SET [Checked] = -1 " & " " & _
          "WHERE [FName] = " & Me.FName
          Debug.Print strSQL
        CurrentDb.Execute strSQL, dbFailOnError

Open in new window

This is the debug result for each
UPDATE tblUser SET Checked = -1 WHERE FName = 34
UPDATE tblUser SET [Checked] = -1 WHERE [FName] = 34

even for the 3rd way I get a syntax error:

strSQL = "UPDATE tblUser " _
                & "SET Checked = -1" _
                & " UserName = " & Me.APIUserName1 _
                & " WHERE FName = " & Me.FName
                Debug.Print strSQL
                    CurrentDb.Execute strSQL, dbFailOnError

Open in new window

crystal (strive4peace) - Microsoft MVP, Access

the 3rd example needs a comma before Username

perhaps tblUser isn't the right name of the table?

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crystal (strive4peace) - Microsoft MVP, Access

good catch, gustav

and the 3rd example needs delimiters around the values to compare if they are strings too (in addition to needing a comma)

military donut

ASKER

Thanks!

Gives me

too few parameters....expected 1

crystal (strive4peace) - Microsoft MVP, Access

what is the data type of FName in the table?

what is the data type of Checked?

military donut

ASKER

text

military donut

ASKER

Updates is

Yes/No (true/false)

sorry forgot that one..

SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

too few parameters

typically means that a field is missing or misspelled.

/gustav

military donut

ASKER

Ahhh....crystal

Gotcha...mixed up the fields from the form and the table....didnt' change the statement

plus I needed the single quotes as Gustav Brock said....

strSQL2 = "UPDATE tblUser " _
& "SET Updated = -1" _
& ", UserName = '" & Me.APIUserName1 & "'" _
& " WHERE FName = '" & Me.FName & "'"
Debug.Print strSQL2
CurrentDb.Execute strSQL2, dbFailOnError

Thanks once again!

crystal (strive4peace) - Microsoft MVP, Access

you're welcome, Ernest ~ happy to help