Need help to figure out why I keep getting syntax error on the following UPDATE statement:

    Dim Ans, strSql As String
    Ans = MsgBox("Save current data?", vbYesNo, "Payroll")
    strSql = "UPDATE Current SET W1RegHrs=32 WHERE EID=21"
    If Ans = vbYes Then
    MsgBox (strSql)
        rst.Open strSql, cnn, adOpenKeyset, adLockOptimistic
        rst.Close
    Else
        Ans = MsgBox("Current data will be lost!", vbOKCancel)
    End If

    DoCmd.GoToRecord , , acNext

where Current is a table, W1RegHrs & EID are fields of Current.

Your help is greatly appreciated.

Just as a guess, likely "Current" is a keyword in Access SQL.

But your code is also way overkill. You don't want or need a recordset object. An UPDATE query never needs or returns one.

Try this:
But why do you
   DIM Ans As String
??? Clearly it is not a string. vbYes, et al, are integer values.

Thanks for the help. As to the variable ANS, I used it as temp debug variable and basically lazy and used it to do debugging.

Do you have a quick way to determine the quarter from current date?

Thanks again.

That would be:

DatePart("q", Date)

dartcoach

Just a note. You wrote the following:

--------------------------------------------------------------------
But why do you
   DIM Ans As String
??? Clearly it is not a string. vbYes, et al, are integer values.
--------------------------------------------------------------------

In the declaration "Dim Ans, strSql As String", Ans is declared as a Variant. In VBA/VB6 you can not declare your variables as a single type en mass. The above statement is equivalent to:

Dim Ans AS Variant
Dim strSql AS String

If you want both variables to be type String, you must declare their types individually, howbeit on the same row:

Dim Ans AS String, strSql As String

This is not true with certain other languages. To prove this, assign a number to each of these variables.

Ans = 1
strSql = 2

Then:

Debug.Print "Ans is type " & TypeName(Ans)
Debug.Print "strSql is type " & TypeName(strSql)

You will see that Ans is type Integer, while strSql is type String.



Allan

Nice point of detail!

This is just another reason that I always put only one variable declaration per line. Even in languages where you don't have oddball foibles like this one.

Thanks for the tips!:)