Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Unanswered: Upadte sql syntax

    I have the following

    Update Battery
    Set Battery.[ibTotal Cycles] = 20
    WHERE (((Battery.ID) = 1));

    I get a "Sub or Function not defined" when I try running it.
    From the iinternet

    SQL: UPDATE Statement

    The UPDATE statement allows you to update a single record or multiple records in a table.

    The syntax for the UPDATE statement is:

    UPDATE table
    SET column = expression
    WHERE predicates;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the code you are using to run the SQL?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That is valid SQL, but I suspect you have it in VBA directly. If so, try:

    CurrentDb.Execute "Update Battery Set [ibTotal Cycles] = 20 WHERE ID = 1"
    Paul

  4. #4
    Join Date
    Jul 2009
    Posts
    185
    Quote Originally Posted by pbaldy
    That is valid SQL, but I suspect you have it in VBA directly. If so, try:

    CurrentDb.Execute "Update Battery Set [ibTotal Cycles] = 20 WHERE ID = 1"
    That worked perfectly now if I could find that in my books. I go to the internet and search. I don't have vb help files yet. Maybe this weekend.

  5. #5
    Join Date
    Jul 2009
    Posts
    185
    Quote Originally Posted by healdem
    and the code you are using to run the SQL?
    I have it in
    Private Sub Form_Delete(Cancel As Integer)

    Sub

  6. #6
    Join Date
    Jul 2009
    Posts
    185
    How come
    CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID =1"
    works
    but
    CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID =flights.[ibattery id]"
    does not? It gives me a "To few parameters" error
    also tried
    Dim ilocbatteryid as integer
    ilocbatteryid = [Flights.ibattery id]
    CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID =ilocbatteryid"
    also give me the same error?
    So close!! but no cigar yet!

  7. #7
    Join Date
    Jul 2009
    Posts
    185
    I just tried (I really don't know what I am doing)
    DoCmd****nSQL "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID =ilocbatteryid"
    but no errors but it asks me for ilocbatteryid
    dah just take what's in ilocbatteryid and do it.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    youwant to use a variable as part of the SQL so yuou need to form the SQL using a variable
    for this sort of problem you are generally best off assigning the value to a variable before EXECUTing the variable
    Code:
    dim strSQL as string
    strsql = CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID =flights.[ibattery id]"
    currentdb.execute strsql
    ..the reason?
    ... then you can examine what you are actually sending to the SQL engine

    ..in this case your where clause is missing or corrupted
    it should read
    Code:
    dim strSQL as string
    strsql = CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID = " & flights.[ibattery id]
    currentdb.execute strsql
    had flights.[ibattery id] been a string value you would need to encapsulate it with a " or ' character, for ease of reading I use chr$(34)


    Code:
    dim strSQL as string
    strsql = CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID = " & chr$(34) & flights.[ibattery id] & chr$(34)
    currentdb.execute strsql
    you can examine trhe SQL using either a breakpoint (click on the grey rectangle adjacent to the line you want to examine or put in a msg box
    eg
    Code:
    dim strSQL as string
    strsql = CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID = " & flights.[ibattery id]
    msgbox ("My SQL is:" & vbcrlf & strSQL)
    currentdb.execute strsql
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2009
    Posts
    185
    growing old is mandatory, growing up is discretionary.

    So true. My x doesn't think I grew up since I moved from computers as a hobby to a hobby and job.

  10. #10
    Join Date
    Jul 2009
    Posts
    185
    dim strSQL as string
    strsql = CurrentDb.Execute "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID = " & flights.[ibattery id]
    currentdb.execute strsql

    gives me a error
    dim strSQL as string
    dim ilocbatteryid as integer
    ilocbattery=flights.[ibattery id]
    strsql = "Update Battery Set [ibTotal Cycles] =[battery.ibtotal cycles]-1 WHERE ID = " & ilocbattery
    currentdb.execute strsql

    Worked ya ya ya ya ay
    Thanks so much for your help None of this in my books and I don't have vb help files yet.
    Now I just need to be able to check if the user pressed yes or no to the delete confirm prompt box

  11. #11
    Join Date
    Jul 2009
    Posts
    185

    Thumbs up

    By the way thanks so much for your help.

Posting Permissions

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