Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    110

    Unanswered: Update SQL statement problem

    I have a list box which displays various records of a table. When a user double clicks on a record then another form pops up with details about the record. The user can then edit the record and save the record back to the table. I have written the code below (which gets called when the user clicks on the update button).. The problem is that I get a syntax error for the SQL statement.

    Any suggestions?

    Private Sub btnUpdate_Click()
    Dim strUpdate As String

    On Error GoTo Err_btnUpdate

    strUpdate = "Update EPISKEYH SET Kwdikos_texnikou = " & Me.Kwdikos_texnikou _
    & ", Kwdikos_mhxanhmatos = " & Me.Kwdikos_mhxanhmatos _
    & ", Hmeromhnia = " & Me.Hmeromhnia _
    & ", Wra_enarjhs = " & Me.Wra_enarjhs _
    & ", Wra_lhjhs =" & Me.Wra_lhjhs _
    & ", Aitia_blabhs =" & Me.Aitia_blabhs _
    & ", Katastash =" & Me.Katastash _
    & ", Ek8esh_texnikou =" & Me.Ek8esh_texnikou _
    & " WHERE Kwdikos_episkeyhs = " & Me.Kwdikos_episkeyhs

    CurrentDb.Execute (strUpdate)
    Exit Sub

    Err_btnUpdate:
    MsgBox Err.Description
    End Sub

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Where die you get the syntax error? in the WHERE part?

  3. #3
    Join Date
    Apr 2004
    Posts
    110
    No, the syntax error occurs with or without the 'WHERE' part. It occurs at runtime when I call CurrentdB.Execute statement.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by divined
    No, the syntax error occurs with or without the 'WHERE' part. It occurs at runtime when I call CurrentdB.Execute statement.
    Are all your fields of Text type? If yes then use the single quote after the = sigen

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    to complete Hambakkka's suggestion:

    if Wra_enarjhs
    is a text field, you need
    & ", Wra_enarjhs = '" & Me.Wra_enarjhs & "'" _

    if Wra_enarjhs
    is a date field, you need
    & ", Wra_enarjhs = #" & Me.Wra_enarjhs & "#" _



    in brief: date textboxes need to be enclosed in #and#, string textboxes in 'and'

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and another thought!

    you don't mention your environment!
    (add your OS/A-version/sqlserver-if-any to your signature so we know).

    currentdb is a DAO concept (...i believe), so it wont run "native" on A2000 or later unless you specifically reference the DAO 3.6 library.

    if you are a2k or later, either fix the reference, or try
    docmd.runsql
    in place of
    currentdb.execute

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Apr 2004
    Posts
    110
    I am using MS Access 2000. I do not get the single quotation marks. Why are they needed since I already am using double quotation marks.

  8. #8
    Join Date
    Apr 2004
    Posts
    110
    I also use DAO 3.6 I have a couple of more questions. Do I need to pretext integer fields just as for date & string fields. Finally, what about memo fields?

    thx, for the assiastance

    George

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Executing an SQL string

    Just a thought, but when I use the Execute method of the CurrentDB I don't use brackets !? ie.

    CurrentDb.Execute strUpdate

    and no you don't need single quotetion marks if the field is an integer.
    I am not sure if you would want to filter a memo field !!

    MTB

  10. #10
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Debugging tip:
    Whenever you're building a SQL statement in code, use debug.print or msgbox to display the statement before trying to execute it. In your case, you could temporarily replace

    CurrentDb.Execute (strUpdate)

    with

    MsgBox(strUpdate)

    Then compare the results displayed in the message box with the SQL view of an equivalent update query you've created (and proven to work) using the Query Design view.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •