Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 18th, 2008, 12:15 PM
Registered User
 
Join Date: Sep 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Syntax error on UPDATE

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.


 
Old September 18th, 2008, 02:47 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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:
Code:
    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)
        cnn.Execute strSql
    Else
        Ans = MsgBox("Current data will be lost!", vbOKCancel)
    End If
But why do you
   DIM Ans As String
??? Clearly it is not a string. vbYes, et al, are integer values.
 
Old September 19th, 2008, 12:07 PM
Registered User
 
Join Date: Sep 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old September 19th, 2008, 12:14 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

That would be:

DatePart("q", Date)

dartcoach
 
Old November 22nd, 2008, 01:37 AM
Registered User
 
Join Date: Nov 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Quote:
quote:Originally posted by Old Pedant
 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:
Code:
    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)
        cnn.Execute strSql
    Else
        Ans = MsgBox("Current data will be lost!", vbOKCancel)
    End If
But why do you
DIM Ans As String
??? Clearly it is not a string. vbYes, et al, are integer values.

 
Old November 22nd, 2008, 08:51 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old November 24th, 2008, 12:45 PM
Registered User
 
Join Date: Sep 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the tips!:)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax Error in the update statement annie_t Access ASP 1 February 1st, 2005 12:48 PM
Syntax Error in Update Louisa VB.NET 2002/2003 Basics 2 March 23rd, 2004 05:27 AM
UPDATE syntax Mitch SQL Server 2000 2 February 6th, 2004 05:08 PM
UPDATE statement returns syntax error AviatorTim Classic ASP Databases 2 January 8th, 2004 05:22 PM
update syntax error Adam H-W Classic ASP Databases 2 July 24th, 2003 07:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.