Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21

    Unanswered: SQL Select/Update (in VBA) troubles

    Hi all,
    I am trying to right an SQL statement in the Code of a form I have, and am having some trouble. The idea is to is to update a table with info from another table, using criteria from my form. Here is what I have so far:

    Code:
    Private Sub Command12_Click()
    Dim mysql As String
    Dim frmdim As Integer
    frmdim = [frmLen] & [frmDia]
    
    mysql = "UPDATE tblLogPurchase SET tblLogPurchase.DBFT " & _
            "SELECT tblDoyle.DBFT " & _
            "WHERE tblDoyle.Dimensions = '*" & frmdim & "*' "
    DoCmd.SetWarnings False
    DoCmd.RunSQL mysql
    DoCmd.SetWarnings True
    
    Me.Requery
    Me.Refresh
    End Sub
    But this doesn't seem to be working for me at all. I am still quite new at this and any help would be greatlly appreciated. Thanks alot
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Are the tblDoyle.Dimensions numeric or String? So as you enter your sql, it shows them as string...

  3. #3
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    They are numeric
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  4. #4
    Join Date
    Nov 2004
    Posts
    34
    Just one thing that imediately springs to mind, the end of the WHERE statement should have a semi-colon, like so:-

    "WHERE tblDoyle.Dimensions = '*" & frmdim & "*';"

  5. #5
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    Yep, I noticed that as well, thanks
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the value in frmdim is numeric then I dont think you can use the wildcard characters
    ie it shoul read
    ..."WHERE tblDoyle.Dimensions =" & frmdim & ";"

    I would suggest you do an explicit conversion to the required datatype (it shouldn't affect the operation but may
    ..."WHERE tblDoyle.Dimensions =" & clng(frmdim) & ";"
    HTH

  7. #7
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    Thanks for the tip I have changed my statement accordingly. I am however still stuck with the same problem...

    The error is as follows "Run-Time Error 3144. Syntax Error in UPDATE Statement"
    When I hit the Debugger it points to the "DoCmd.RunSQL mysql" line.
    Last edited by spiderweb; 12-07-04 at 10:49.
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  8. #8
    Join Date
    Nov 2004
    Posts
    34
    I've just tested it and it is allowable to use wildcard characters with numeric data types. Can you paste the SQL you have into a blank query in SQL view, run it and see what error message come up then?

  9. #9
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    Can you paste the SQL you have into a blank query in SQL view, run it and see what error message come up then
    Just tried that and I get the same Error as in the code editor. I get the feeling that I am totally off here. The thing is that I have been searching all morning and cannot find any info regarding using UPDATE and SELECT in the same query.
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the syntax error indicates that your sql statement is badly formed
    to investigate you need to see the precise sql statement that the docmd is trying to run

    I would suggest that you debug, switch to the immediate window in the vb code dialog
    and type ?mysql.

    Looking at your SQL the error is that the sentance itself is gibberesh.
    you have a mix of select and update clauses, no table defintion
    a select is usually
    select <tablename>.<columnname1>,<tablename>.<columnnamen >
    from <tablename>,<tablename2>
    where <tablename>.<columnname1> = myvalue and <tablename>.<columnname2>=myothervalue
    order by <tablename>.<columnname1>;

    or
    update
    update <tablename>
    set <columnname1>=myval
    where <tablename>.column2<>myval

    or something similar

  11. #11
    Join Date
    Nov 2004
    Posts
    34
    Yea, you got it Healdem. What I do whenever I'm having problems getting a query to run in Vba, I create it in Query builder using a dummy value for for where the vba variable will go, make sure it runs ok, switch to SQL view and then just copy the code.

  12. #12
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    Sounds like a plan guys I will go ahead and Try to make it work that way.
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

Posting Permissions

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