Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Jul 2008
    Posts
    22

    Red face Unanswered: Synatx Error in update statement

    Hi All,

    I am a newby so please understand if I ask a stupid question.....

    I get an "Synatx Error in update statement" error.

    In my Global.bas Module I have the following:
    Public cn As New ADODB.Connection
    Public rs As New ADODB.Recordset
    Public dBase As String

    In my Main.form I have:
    Private Sub MDIForm_Load()
    dBase = App.Path & "\Inventory.mdb"
    cn.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" & dBase
    mnuLeads.Enabled = False
    mnuReps.Enabled = False
    mnuDbase.Enabled = False
    End Sub

    The statement that I have a problem with is:
    rs.Open "Update tblLead set Client Name='" & Sup_CName & "',Client Surname='" & Sup_Surname & "',Client Address='" & sAddr & "',Client Cell='" & sTele & "',App Date='" & sDate & "',Satus='" & sStatus & "',Rep='" & sRep & "'" & _
    "Where Lead_ID='" & iFind & "'", cn, 3, 3

    Now I admid that this code is not mine but was found on another site. I just cant seem to get it to work.

    Will someone please help???


    Thanks


    Swannie

    PS. I can make the project available for download if nescesarry.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You know what helps debugging these things... If you print out the resultant string...

    Code:
    msg = "hi there"
    msg = msg & "my name is george"
    
    MsgBox msg
    You'll then be able to see your mistake
    Quote Originally Posted by result
    hi theremy name is george
    See what I'm getting at?
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2008
    Posts
    22
    As I said, I am very new to this. Where should I add this code?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So you've got this bit of code
    Code:
    rs.Open "Update tblLead set Client Name='" & Sup_CName & "',Client Surname='" & Sup_Surname & "',Client Address='" & sAddr & "',Client Cell='" & sTele & "',App Date='" & sDate & "',Satus='" & sStatus & "',Rep='" & sRep & "'" & _
    "Where Lead_ID='" & iFind & "'", cn, 3, 3
    Chande your code to include the following
    Code:
    Dim sqlStr
    
    sqlStr = <bit highlighted in red above>
    
    rs.Open sqlStr, cn, 3, 3
    What you're doing here is assigning the highlighted string to a variable, which you call upon later in the rs.Open command.

    Now, so we can see the string in full, stick a
    Code:
    MsgBox sqlStr
    Before the line that causes the error.

    Look at the result carefully and see if you can spot any problems, such as missing spaces/apostrophes, etc...
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2008
    Posts
    22

    I got The message

    Hi There,

    thanks - I did what you told me and got the following:



    I cant see any errors, can you?


    Thanks


    Swannie

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ideally never have spaces in column names. If you must then you need to enclose them. Depends on your RDBMS but usually either [Column Name] or "Column Name".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's another problem once you fix that. A clue :
    Quote Originally Posted by georgev
    Look at the result carefully and see if you can spot any problems, such as missing spaces/apostrophes, etc...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2008
    Posts
    22

    Error2

    I added the [] but now have another error - You knew that would happen, didn't you?????????



    Stop keeping me in suspence, please??

    Thanks so far,


    Swannie

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well I needs to see the statement donts I?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2008
    Posts
    22
    Dim sqlStr

    sqlStr = "Update tblLead set [Client Name]='" & Sup_CName & "',[Client Surname]='" & Sup_Surname & "',[Client Address]='" & sAddr & "',[Client Cell]='" & sTele & "',[App Date]='" & sDate & "',[Satus]='" & sStatus & "',[Rep]='" & sRep & "'" & _
    "Where [Lead_ID]='" & iFind & "'"
    MsgBox sqlStr
    rs.Open sqlStr, cn, 3, 3

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cool.

    Missing space still.
    I expect Access will not like that date format either. "MM/DD/YYYY" is Access's preferred format.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2008
    Posts
    22
    The database accepts the date what adding new. This error is on update. Where is the missing space?

  13. #13
    Join Date
    Jul 2008
    Posts
    22
    The database accepts the date when adding new. This error is on update. Where is the missing space?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Swannie1971
    Where is the missing space?
    Exactly - you've got it!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hang on - date is incorrectly delimited. Access uses # not '. Are you sure your insert statement works?

    Also, no need for ' around Integers (though I don't think Access really cares).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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