IMD1
asked on
CurrentDb.Execute syntax
After reviewing many solutions here I can't seem to make this work. Am executing this from a form button but continually wind up with syntax errors in the update statement.
Private Sub Status_Click()
Dim t_stat As String
t_stat = DLookup("Stat", "ProdCode", "[ProdCode]![Pcode] = [Forms]![RA Log]![Pcode]")
If t_stat = "A" Then
CurrentDb.Execute _
"Update ProdCode" & _
"Set Prodcode.Stat =" & "S" & _
"where (ProdCode.Pcode)=" & Me![Pcode]
Else
(Repeat with new result)
End Sub
Private Sub Status_Click()
Dim t_stat As String
t_stat = DLookup("Stat", "ProdCode", "[ProdCode]![Pcode] = [Forms]![RA Log]![Pcode]")
If t_stat = "A" Then
CurrentDb.Execute _
"Update ProdCode" & _
"Set Prodcode.Stat =" & "S" & _
"where (ProdCode.Pcode)=" & Me![Pcode]
Else
(Repeat with new result)
End Sub
Prodcode.Stat is a string correct? If so, then you need to enclose the parameters/values in single or double quotes
CurrentDb.Execute _
"Update ProdCode" & _
"Set Prodcode.Stat ='" & "S'" & _
"where (ProdCode.Pcode)='" & Me![Pcode] & "'"
CurrentDb.Execute _
"Update ProdCode" & _
"Set Prodcode.Stat ='" & "S'" & _
"where (ProdCode.Pcode)='" & Me![Pcode] & "'"
CurrentDb.Execute "Update ProdCode Set Prodcode.Stat ="S" where (ProdCode.Pcode)= '" & Me![Pcode] & "'"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome. Thanks Arthur. Any good recommendations on "help" for this? Although I'm pretty green to this stuff, this area is where I seem to struggle the most and cannot find good help on.
Lee
Lee
the problem was the embedded " in the original expression and all of the attempts to correct it:
CurrentDb.Execute "Update ProdCode Set Prodcode.Stat ="S" where (ProdCode.Pcode)= '" & Me![Pcode] & "'"
the " before the S terminated the intial ", and the rest of the line was interpretted as garbage.
when a charater is to be treated as text, within a string to be passed to the execute method, it is enclosed in '...' not " ". this was being properly handled in the where clause, but not in the Set clause.
in general, within SQL strings, enclose text value ins '...', and you should be good to go.
you can also use chr(34) 34 is the ASCII code for the " character, and the Chr function returns the Character give its ASCII code, so the command could also have beeb coded as :
CurrentDb.Execute "Update ProdCode Set Stat =" & Chr(34) & "S" & Chr(34) & " where Pcode = '" & Me![Pcode] & "'
but personally I find that extremely hard to read, and prefer:
CurrentDb.Execute "Update ProdCode Set Stat ='S' where Pcode = '" & Me![Pcode] & "'
AW
CurrentDb.Execute "Update ProdCode Set Prodcode.Stat ="S" where (ProdCode.Pcode)= '" & Me![Pcode] & "'"
the " before the S terminated the intial ", and the rest of the line was interpretted as garbage.
when a charater is to be treated as text, within a string to be passed to the execute method, it is enclosed in '...' not " ". this was being properly handled in the where clause, but not in the Set clause.
in general, within SQL strings, enclose text value ins '...', and you should be good to go.
you can also use chr(34) 34 is the ASCII code for the " character, and the Chr function returns the Character give its ASCII code, so the command could also have beeb coded as :
CurrentDb.Execute "Update ProdCode Set Stat =" & Chr(34) & "S" & Chr(34) & " where Pcode = '" & Me![Pcode] & "'
but personally I find that extremely hard to read, and prefer:
CurrentDb.Execute "Update ProdCode Set Stat ='S' where Pcode = '" & Me![Pcode] & "'
AW
ASKER
Cheers Arthur.
Lee
Lee
CurrentDb.Execute "Update ProdCode Set Prodcode.Stat ="S" where (ProdCode.Pcode)= '" & Me![Pcode] & "'