Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 13 votes, 5.00 average. Display Modes
Old 03-29-2010, 04:56 AM   #1
mjancic
Registered User
 
Join Date: Mar 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
mjancic is on a distinguished road
Question RUN TIME ERROR 3144, syntax error in update statement

Hi,

I have a problem with one script. This scrip is working in access 2003, but it wont work in access 2007. When I run it in 2007 I get run time error 3144, syntax error in update statement. Please take a look at this script below.

Private Sub cmd_update_Enter()

Dim ideal_px As Double
Dim strSQL As String

ideal_px = tbox_ideal_px.Value / 100
strSQL = "Update input_ideal_px set input_ideal_px.ideal_px = "
strSQL = strSQL & ideal_px & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


What should change in this scrip so I can run it in Access 2007?

Thanks in advance,

Milan

mjancic is offline   Reply With Quote
Old 03-29-2010, 05:36 AM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: RUN TIME ERROR 3144, syntax error in update statement

Things to consider:

1. Try running the code in a different event
2. Do not use variable names that are the same as field names

Here's the amended code, see if this works:

Code:
Dim ideal_px_Var As Double
Dim strSQL As String

ideal_px_Var = tbox_ideal_px.Value / 100
strSQL = "UPDATE input_ideal_px SET [ideal_px] = " & ideal_px_Var & ";"
Question: Is the ideal_px field a Text or Number data type?
vbaInet is offline   Reply With Quote
Old 03-29-2010, 05:48 AM   #3
mjancic
Registered User
 
Join Date: Mar 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
mjancic is on a distinguished road
Re: RUN TIME ERROR 3144, syntax error in update statement

Hi,

ideal_px field is a number data type

mjancic is offline   Reply With Quote
Old 03-29-2010, 05:51 AM   #4
mjancic
Registered User
 
Join Date: Mar 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
mjancic is on a distinguished road
Re: RUN TIME ERROR 3144, syntax error in update statement

It won't work.

When I go to DEBUG, marked is DoCmd.RunSQL strSQL
mjancic is offline   Reply With Quote
Old 03-29-2010, 05:56 AM   #5
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: RUN TIME ERROR 3144, syntax error in update statement

Go back to yoru table, look in the Field Size property for ideal_px and ensure that it is DOUBLE.
vbaInet is offline   Reply With Quote
Old 03-30-2010, 02:24 AM   #6
mjancic
Registered User
 
Join Date: Mar 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
mjancic is on a distinguished road
Re: RUN TIME ERROR 3144, syntax error in update statement

Hi,

Field size property is set up as DOUBLE and it won't work.

Can I change anything else to get this thing working?

FYI, I'm not skilled in writting scripts, I just recieved this file from my colleague, but we cannot figure out why it is not working in access 2007, but it is working in access 2003.

Thanks for yor support.

Milan
mjancic is offline   Reply With Quote
Old 03-30-2010, 02:38 AM   #7
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: RUN TIME ERROR 3144, syntax error in update statement

Send the results of your strSQL to the immediate window by using

Debug.Print strSQL

Open the immediate window (Ctrl+G)
Next copy the SQL into memory
Open a new query but do not select and tables or queries.
Change the view pane to SQL mode
Paste in the SQL
Change view to Design mode
Does Access generate an error?
If so it will attempt to show you where by the description and possibly the location of the cursor.
If not try and run the update query. Does this throw up an error?
If so what is it?

__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 03-30-2010, 04:38 AM   #8
mjancic
Registered User
 
Join Date: Mar 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
mjancic is on a distinguished road
Re: RUN TIME ERROR 3144, syntax error in update statement

Hi,

Thanks for your advice, but sorry I'm do not know how to run deboug.print strSQL. Where do i need to put it, into now module into my user from or???

Below you can find complete code of my user from, maybe it can help you to define where the problem is.


Thanks
Milan



Option Compare Database
Private Sub cboCycle_Change()
Dim bill_type As String
Dim strSQL As String

bill_type = cboCycle.Value
strSQL = "UPDATE input_cycle SET input_cycle.cycle = '"
strSQL = strSQL & bill_type & "';"


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
Private Sub cmd_exit_Click()
DoCmd.Quit
End Sub
Private Sub cmd_export_full_Click()
Dim varRetVal As Variant

varRetVal = trfResults()
End Sub
Private Sub cmd_export_matched_Click()
Dim strSQL As String

strSQL = "Export matched list"

DoCmd.RunMacro strSQL
End Sub
Private Sub cmd_fs_Click()
DoCmd.OpenTable "001a fs_index"
End Sub
Private Sub cmd_missCtry_Click()
Me.cmd_runProg.Enabled = True
DoCmd.OpenQuery "002c check_missing country in zonings"
End Sub
Private Sub cmd_missStn_Click()
Me.cmd_missCtry.Enabled = True
DoCmd.OpenQuery "002b check_missing stations"
End Sub


Private Sub cmd_update_Enter()
Dim ideal_px_Var As Double
Dim strSQL As String

ideal_px_Var = tbox_ideal_px.Value / 100
strSQL = "UPDATE input_ideal_px SET [ideal_px] = " & ideal_px_Var & ";"


DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End Sub
Private Sub cmd_runProg_Click()
On Error GoTo Err_cmd_runProg_Click
Dim stDocName As String
Dim cycle As String
cycle = DLookup("[cycle]", "input_cycle")

stDocName = IIf(cycle = "T", "002 Run data - 3rd party", "002 run data")

DoCmd.RunMacro stDocName
MsgBox "Completed!"
Exit_cmd_runProg_Click:
Exit Sub
Err_cmd_runProg_Click:
MsgBox Err.Description
Resume Exit_cmd_runProg_Click

End Sub
mjancic is offline   Reply With Quote
Old 03-30-2010, 04:45 AM   #9
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: RUN TIME ERROR 3144, syntax error in update statement

insert it just before the DoCmd.Setwarnings = False line
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 03-30-2010, 05:09 AM   #10
mjancic
Registered User
 
Join Date: Mar 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
mjancic is on a distinguished road
Thumbs up Re: RUN TIME ERROR 3144, syntax error in update statement

Finnaly, I found out what was the cause ))

I use "," as decimal separator. When I change "," into "." it works.

Thanks for your help.

Cheers
Milan
mjancic is offline   Reply With Quote
Old 03-30-2010, 05:13 AM   #11
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: RUN TIME ERROR 3144, syntax error in update statement

Glad you got that sorted.

vbaInet is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run TIme Error 3131 - Syntax error in From Clause manix Forms 8 02-09-2007 02:18 AM
Unsuccessful execution of SQL statement within VBA codes cindyzhou Queries 4 01-02-2006 09:25 PM
SQL UPDATE Statement in VBA (Troubles) Josh Hill Modules & VBA 8 11-04-2005 12:14 PM
Run time versions and missing references Dan_T General 8 07-19-2004 03:50 AM
[SOLVED] WHERE clause statement redducati748 Queries 1 06-01-2002 01:56 PM




All times are GMT -8. The time now is 01:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World