syntax error

C

cporter

I'm getting a compile/syntax error message on the string below. I don't
see it. What do I need to change?

StrSQL = UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID =
tblFPMTemp.MACHID SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));
 
G

Graham R Seach

The purpose in populating a string variable with an SQL statement is to pass
that statement to something that will execute it. In this case you'll
probably be using the db.Execute strSQL construct.

In order to do so, you must populate strSQL with a string value, which your
code doesn't.

The following example does that, but it assumes vValue is a numeric value.

StrSQL = "UPDATE FPM INNER JOIN tblFPMTemp " & _
"ON FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & vValue
& _
" WHERE
(((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));"

If vValue is also a string value, then you need to enclose it in single or
double quotes.
"SET tblFPMTemp.EFLH = '" & vValue & "', ...
or
"SET tblFPMTemp.EFLH = """ & vValue & """, ...

The first example, uses a single quote ( ' ). The second uses double quotes
( " ). Because double-quotes are a special character, you must use it twice,
so Access recognises the fact that you want to include a quote, rather than
closing a sting value; hence three quotes together.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
D

Duane Hookom

When you set the value of a string variable, you need to place quotes around
the string.

StrSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]

This assumes MACHID is numeric. If it is text, try:

StrSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue " & _
"WHERE tblFPMTemp.MACHID= """ & _
[Forms]![formFPMByNum]![MACHID] & """"
 
C

cporter

OK changed the code and Iam getting a new error message, "to few
parameters" and the code phrase, CurrentDb().Execute strSQL,
dbFailOnError, is highlighted.

The whole code is below:


Private Sub TimeUp_AfterUpdate()
Dim vValue As Variant
Dim strSQL As String


If Forms!formFPMByNum!MACHID = "chi-002" Then
'Get the value from a control on a form or ask the user for the value
vValue = InputBox("What EFLH", "EFLH", 10)
ElseIf Forms!formFPMByNum!MACHID = "car-001" Then
vValue = InputBox("What EFLH", "EFLH", 10)
ElseIf Forms!formFPMByNum!MACHID = "car-002" Then
vValue = InputBox("What EFLH", "EFLH", 10)

'Or you can use
'vValue = Forms!formFPMByNum!EFLH

Else
vValue = 0
End If

If IsNumeric(vValue) = True Then
strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]

CurrentDb().Execute strSQL, dbFailOnError
Else
MsgBox "Invalid EFLH value"
End If
End Sub
 
D

Duane Hookom

The query engine won't know what vValue is.
Try:
strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
 
D

Duane Hookom

Add code after the strSQL=..

Debug.Print strSQL

Then open the code after running and copy the strSQL output from the debug
window and paste it into a query window. See what happens. If you can't
figure it out, come back with your code and the value of strSQL.

--
Duane Hookom
MS Access MVP

This one just gives me a syntax error


Duane said:
The query engine won't know what vValue is.
Try:
strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
Click to expand...
Click to expand...
 
C

cporter

It doesn't run. The thing pops up a msb box saying compile error:
syntax error and stops with the code highlighted. If the debug.print
is supposed to do something I don't see it anywhere.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
 
D

Duane Hookom

You missed my mistake ;-) There was an ampersand missing following the
second vValue.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue & " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]


--
Duane Hookom
MS Access MVP


It doesn't run. The thing pops up a msb box saying compile error:
syntax error and stops with the code highlighted. If the debug.print
is supposed to do something I don't see it anywhere.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
Click to expand...
 
C

cporter

Duane,

Thanks for hanging with me.

The new error is run time error '3061':
to few parameters. Expected 1

The print StrSQL reads:
"UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID = tblFPMTemp.MACHID SET
tblFPMTemp.EFLH = 19500, FPM.EFLH = 19500 WHERE tblFPMTemp.MACHID=
CHI-002"

Inserting this into the SQL window of a query and running it generated
a dialog box asking for the value of CHI. Could the hyphen in the
MachID be causing a problem? I enclosed CHI-002 in double quotes and
the update query ran ok.


It looks like I am going to be writing a lot more code in VBA. To do
this well I'm going to need some references and something to walk me
through the basics. What would you suggest?
 
D

Duane Hookom

Note my first reply had two different suggestions based onif MACHID was
numeric or text. You chose the numeric solution while your MACHID is
apparently text. Try:

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue & " " & _
"WHERE tblFPMTemp.MACHID= """ & _
[Forms]![formFPMByNum]![MACHID] & """"

--
Duane Hookom
MS Access MVP


Duane,

Thanks for hanging with me.

The new error is run time error '3061':
to few parameters. Expected 1

The print StrSQL reads:
"UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID = tblFPMTemp.MACHID SET
tblFPMTemp.EFLH = 19500, FPM.EFLH = 19500 WHERE tblFPMTemp.MACHID=
CHI-002"

Inserting this into the SQL window of a query and running it generated
a dialog box asking for the value of CHI. Could the hyphen in the
MachID be causing a problem? I enclosed CHI-002 in double quotes and
the update query ran ok.


It looks like I am going to be writing a lot more code in VBA. To do
this well I'm going to need some references and something to walk me
through the basics. What would you suggest?



Duane said:
You missed my mistake ;-) There was an ampersand missing following the
second vValue.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue & " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
Click to expand...
Click to expand...
 
C

cporter

Duane,

It's working now, thank you. I had thought you meant the value of
vValue was numeric or text.

Carl
 
Top