RUN TIME ERROR 3144, syntax error in update statement

[mjancic]

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

 

[vbaInet]

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:

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?

 

[mjancic]

Hi,

ideal_px field is a number data type

 

[mjancic]

It won't work.

When I go to DEBUG, marked is DoCmd.RunSQL strSQL

 

[vbaInet]

Go back to yoru table, look in the Field Size property for ideal_px and ensure that it is DOUBLE.

 

[mjancic]

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

 

[DCrake]

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?

 

[mjancic]

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

 

[DCrake]

insert it just before the DoCmd.Setwarnings = False line

 

[mjancic]

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

 

[vbaInet]

Glad you got that sorted.