Function or Variable Expected

[DS]

I have this SQL Statement that keeps giving me the Error...
Function or variable expect...any help appreciated.
Thanks
DS


Dim MORESQL As String
DoCmd.SetWarnings False
MORESQL = "UPDATE tblChecks SET tblChecks.ChkPaid = -1 " & _
"WHERE tblChecks.CheckID = Forms!frmCheckPayment!TxtSalesID " & _
DoCmd.RunSQL(MORESQL)
DoCmd.SetWarnings True

 

[DS]

I have this SQL Statement that keeps giving me the Error...
Function or variable expect...any help appreciated.
Thanks
DS


Dim MORESQL As String
DoCmd.SetWarnings False
MORESQL = "UPDATE tblChecks SET tblChecks.ChkPaid = -1 " & _
"WHERE tblChecks.CheckID = Forms!frmCheckPayment!TxtSalesID " & _
DoCmd.RunSQL(MORESQL)
DoCmd.SetWarnings True

Never Mind as usual, after I post I figure out my misdeeds!

Not This:
WHERE tblChecks.CheckID = Forms!frmCheckPayment!TxtSalesID " & _
This:
WHERE tblChecks.CheckID = Forms!frmCheckPayment!TxtSalesID;"
Thanks
DS

 

[Guest]

The first problem is this line:
"WHERE tblChecks.CheckID = Forms!frmCheckPayment!TxtSalesID " & _
It has a concatenation character (&) and a line continuation character (_)
at the end of it. It is expecting more. I thinks the next line
DoCmd.RunSQL(MORESQL)
is part of the statement.

The second problem is your reference to the form control is inside the
quotes. Anything inside quotes is passed as a literal, not the value of the
control.

Here is a rewrite of your code that will run much faster and you don't have
to use the SetWarings, because the Execute method bypasses the Access UI and
goes straight to Jet:

CurrentDb.Execute("UPDATE tblChecks SET tblChecks.ChkPaid = -1 " & _
"WHERE tblChecks.CheckID = '" &Forms!frmCheckPayment!TxtSalesID & "';"),
dbFailOnError

You can assign the SQL string to a variable and execute that, but why bother?
Also note the above code assumes ChkID is a text field. If it is a numeric
field, you need to remove the single quotes and use this version:

CurrentDb.Execute("UPDATE tblChecks SET tblChecks.ChkPaid = -1 " & _
"WHERE tblChecks.CheckID = " &Forms!frmCheckPayment!TxtSalesID & ";"),
dbFailOnError

 

[Dirk Goldgar]

The second problem is your reference to the form control is inside the
quotes. Anything inside quotes is passed as a literal, not the value
of the control.

But the RunSQL method can recognize and resolve form/control references
inside the quotes. So the statement will work, once the terminal
concatenation and continuation are removed.

 

[Guest]

Thanks, Dirk.
I was not aware of that since I don't use RunSQL.
I see no point in using 4 lines of code where 1 will do.

 

[Dirk Goldgar]

Thanks, Dirk.
I was not aware of that since I don't use RunSQL.
I see no point in using 4 lines of code where 1 will do.

I agree with you, but RunSQL does have its advantages. For one thing,
it works in both MDBs and ADPs, and for another, Access will fill in
parameters for you, as in this case. However, I prefer the level of
control that the .Execute method gives, not having to set warnings on
and off, and the fact that my code can find out how many records were
affected.

 

[Guest]

yes, you always have weigh the options whenever you are coding.

One thing I miss from the Foxpro days was the ability to read the current
setting for Warnings. The advantage there was when you entered a procedure,
you could read the current setting into a variable, do what you wanted in the
procedure, and set it back like it was when you found it.

Mostly, I turn warnings off in the Options.