• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 974
  • Last Modified:

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
0
IMD1
Asked:
IMD1
  • 2
  • 2
  • 2
  • +1
1 Solution
 
netcoolCommented:
Hi try this

CurrentDb.Execute "Update ProdCode Set Prodcode.Stat ="S" where (ProdCode.Pcode)= '" & Me![Pcode] & "'
0
 
RyanDeMouginCommented:
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] & "'"
0
 
netcoolCommented:
CurrentDb.Execute "Update ProdCode Set Prodcode.Stat ="S" where (ProdCode.Pcode)= '" & Me![Pcode] & "'"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Arthur_WoodCommented:
try it this way:

CurrentDb.Execute "Update ProdCode Set Stat ='S' where Pcode = '" & Me![Pcode] & "'

AW
0
 
IMD1Author Commented:
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
0
 
Arthur_WoodCommented:
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

0
 
IMD1Author Commented:
Cheers Arthur.

Lee
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now