Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2014
    Posts
    29

    Unanswered: VBA SQL not working

    When i run this routine i get the message
    Run time error 3075:
    syntax (error missing operator) in query expression 'Manager Menu'

    I have tried various solutions already posted on the forum to no avail,

    any help greatly appreciated.

    Cheers


    Private Sub Command26_Click()
    Dim myform As String
    Dim SQL As String
    Dim i As Long
    Dim MyTbl As String
    MyTbl = "OpenForm"
    i = 1
    myform = "Manager Menu"
    SQL = "UPDATE MyTbl " & "SET [FormOpen] = " & myform & " " & "WHERE [ID] = " & i
    DoCmd.RunSQL SQL
    Call PasswordCheck("Manager Menu", "MainMenu", "", acNormal)
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what datatype is formopen?

    my expectation is that it is text/string so the value need to be delimted
    eg:-
    Code:
    SQL = "UPDATE MyTbl SET FormOpen = '" & myform & "' WHERE ID = " & i
    ..that also assume ID is numeric
    a suggestion:- you will find your code easier to read if you give variable 'meaningful' names ie replace i with something that makes it clear what that variable holds.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    SQL = "UPDATE MyTbl " & "SET [FormOpen] = " & myform & " " & "WHERE [ID] = " & i
    Should be
    Code:
    SQL = "UPDATE " & MyTbl & " SET [FormOpen] = '" & myform & "' WHERE [ID] = " & i
    Sam

    ps Also, you shouldn't be using reserved words (e.g. SQL, which is the name of a property) for variable names. Change SQL variable name, such as MySQL.
    Last edited by Sam Landy; 02-13-14 at 00:18.

  4. #4
    Join Date
    Jan 2014
    Posts
    29
    Thanks

    this still doesn't work though. I am getting the message syntax error in update.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok
    so can we see the actual value of SQL?
    put a breakpoint on the code, examine the value then copy and paste that code here

    can we see the table design for 'openform'

    can you confrim what datatype i is, both Sam & myself are assuming its numeric?

    'doesn't work' is possibly the most pointless thing to type on a forum such as this
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2014
    Posts
    5
    Quote Originally Posted by F1ynn View Post
    SQL = "UPDATE MyTbl " & "SET [FormOpen] = " & myform & " " & "WHERE [ID] = " & i
    Try this:
    Code:
    SQL = "UPDATE " & MyTbl & " SET [FormOpen] = '" & myform & "' WHERE [ID] = " & i

  7. #7
    Join Date
    Jan 2014
    Posts
    29
    I have attached an image of the code and value of SQL which i have changed to MySQL

    The variable I is used fo the numeric value to the ID on table [OpenForm]


    Cheers
    Attached Thumbnails Attached Thumbnails SQLcode.jpg  

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so looking at your variable SQL, what looks wrong?

    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you examine the contents of the variable MySQL, you'll notice that the name of the table is missing:
    Code:
    UPDATE SET ...
    while this would be expected:
    Code:
    UPDATE TableName SET ...
    Have a nice day!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sinndho View Post
    If you examine the contents of the variable MySQL, you'll notice that the name of the table is missing:
    Code:
    UPDATE SET ...
    while this would be expected:
    Code:
    UPDATE TableName SET ...
    my thoughts exactly.......
    but I was trying to push the OP into looking at what the problem was, analysing it and come up with a solution

    the fact is you are not setting the value of mytbl = "openform", you are setting it to the value of a variable called openform,. which Im guessing has no value.

    several lessons form this

    always, always use option explicit at the start of any form or report code module.. it forces you to declare a varaible before first use.. so it would have picked up the openform was not declared when you tried to assign it to mytbl.

    always, always assign your sql to a variable, and when reporting faults here include the value of that variable in the original request for help

    vba can easily create code that runs, however its very easy to look at your code at think 'that' bit is working because in your mind its written as you expect. however always check each segment is doing what you expect (ie variables initialised, variables contain sane values and so on. using the debugger is vital (in my opinion) when developing Access applications that use code. stepping through code allows you to check that the code as written does what you think it does, and not just assume it works as you expect
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2014
    Posts
    29
    OpenForm is the name of the form to be opened, not a variable. Should have been in "". I have changed that, attached is a screen shot of the variable MySQL.

    Still getting the error 'Data type mismatch in expression'
    Attached Thumbnails Attached Thumbnails SQL2.jpg  

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the usual reason for that error message is that you are supplying the wrong value for columnint he table


    so either
    ..formopen isn't of tyre test/string
    OR
    ..ID isn't numeric

    you don't need the square brackets, I doubt its going to make a huge amount of difference though (y'd only need square brackets if you used say spaces in your table or column names

    aside from that the sql looks valid in the absence of any information about data types and table design
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sam Landy View Post
    Also, you shouldn't be using reserved words (e.g. SQL, which is the name of a property) for variable names. Change SQL variable name, such as MySQL.
    curiously though examples for 2007 Access Developer Reference
    RunSQL Method*[Access 2007 Developer Reference] MSDN use a variabel called SQL. whic I woudl take to mean yuou cant use SQL inside a JET table, but it doenst' matter as a VBA variable
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jan 2014
    Posts
    29
    Resolved thanks, yes the data type in the table was set to 'short text' rather than 'number'

Posting Permissions

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