Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Unhappy Unanswered: docmd.RunSQL doesent work...

    Hi,

    I am trying to write a filtering utility which lets a user without SQL knowlege write a simple union sql statement with a simple 'Where' clause.

    That part of it is working and im outputting the SQL statement in 2 parts.

    1: SQL: This is basically SelectString & " WHERE " & me.ResultBox & ";" depending on the otions selected.

    2: ResultBox: This is "([field] "& operator &" ) "& AND/OR & ... default is AND.

    The problem starts when i try to insert those strings into a table for later use...

    Note that I put the strings together in a textbox called "SQL" which gives me a working SQL string (complete with a ";" at the end)


    Now hers the code I use to add to the table, I'm new at VBA so its not amazing:

    Code:
    Private Sub SaveF_Click()
    
    Dim saveFilter As String
    Dim Name As String
    Dim Filter As String
    
    Name = Me.fname
    Filter = Me.ResultBox
    
    saveFilter = "INSERT INTO FilterTable (fName, SQL, Filter) VALUES (" & Name & ", [" & _
                 SQL & "], [" & Filter & "])"
                 
                 'MsgBox saveFilter
    
    
    DoCmd.RunSQL saveFilter
    
    End Sub

    The Error message I get is "Missing semicolon ( ; ) at the end if SQL statement"

    I think that it doesent like the fact that the strings i'm saving are parts of an sql statement...

    Is there any way to overcome this?

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    put your strings in single quotes.
    e.g.

    VALUES ('" & Name & "',
    etc


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    Ok im confused...

    hers the insert statement im generating... is it at all possible to make that work? Note that those are the exact strings i want to go into the table.

    INSERT INTO FilterTable (fName, SQL, Filter) VALUES ([Test2], [SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00') ;], [ ([ProjectID] = 'KE00') ]);


    The fields for Filter table are as follows:

    FilterID Autonumber
    fName Text
    SQL Text
    Filter Text

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are getting a bit lost there.
    try:

    WHERE [ProjectID] = 'KE00';
    (assuming you want the literal string "KE00")

    if the WHERE is supposed to be two different criteria, you want:
    WHERE (([ProjectID] = 'KE00') AND ([somethingElse] = 'XYZ'));

    and in both cases, safer would be:
    [KE00_drawings].[ProjectID]

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and are you sure that * is going to fit in the two fields still available?
    you are probably better of using two named fields instead of *

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    You mist the mark there a little bit. The filter works.....

    I just need those strings in a table as records.. As literal strings.

    But the problem is that the Insert statement is getting confused... or the Docmd is getting confused.

    To clarify:

    I need

    SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00') ;

    saved as a separate string in a field named SQL in a table named FilterTable

    AND as a bonus, but not entirely nessesary I need the last part (yes i need the brackets because i dont know how many of them i will have, could be 1 could be 10)

    ([ProjectID] = 'KE00')

    Also stored as a literal string in a table, thesame table.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Aaaaah!

    OK - but you still havea problem
    INSERT INTO FilterTable (fName, SQL, Filter) VALUES ([Test2], [SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00') ;], [ ([ProjectID] = 'KE00') ]);

    try this:
    INSERT INTO FilterTable (fName, SQL, Filter) VALUES ([Test2], 'SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00') ;', '([ProjectID] = 'KE00') ');

    NOTE: that i didn't test this... there might be probs with the third field.
    are you able to consider stuffing this into vars?

    i.e.
    dim strF2 as string
    dim strF3 as string
    dim strSQL as string
    strF2 = "SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00');"
    strF3 = "([ProjectID] = 'KE00')"

    then
    strSQL = "INSERT INTO FilterTable (fName, SQL, Filter) VALUES ([Test2], '"
    strSQL = strSQL & strF2 & "', '" & strF3 & "';"

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2004
    Posts
    118
    Quote Originally Posted by izyrider
    Aaaaah!

    OK - but you still havea problem
    INSERT INTO FilterTable (fName, SQL, Filter) VALUES ([Test2], [SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00') ;], [ ([ProjectID] = 'KE00') ]);

    try this:
    INSERT INTO FilterTable (fName, SQL, Filter) VALUES ([Test2], 'SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00') ;', '([ProjectID] = 'KE00') ');

    NOTE: that i didn't test this... there might be probs with the third field.
    are you able to consider stuffing this into vars?

    i.e.
    dim strF2 as string
    dim strF3 as string
    dim strSQL as string
    strF2 = "SELECT * FROM KE0B_drawings UNION SELECT * FROM KE00_drawings WHERE ([ProjectID] = 'KE00');"
    strF3 = "([ProjectID] = 'KE00')"

    then
    strSQL = "INSERT INTO FilterTable (fName, SQL, Filter) VALUES ([Test2], '"
    strSQL = strSQL & strF2 & "', '" & strF3 & "';"

    izy
    this is exactly what I am doing. If you look at the original code in the first message, if i do MSGBox saveFilter the result I get is with the strings in there.

    This means i cant use "" because that would write exactly what im putting into the string. i.e. ".."" & Filter & "".."


    I tried [] AND '' , both return thesame problem...

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i've got a missing ) in the last line. should be:
    strSQL = strSQL & strF2 & "', '" & strF3 & "');"

    ...and apart from that it should work!

    mine is not quite the same as your first post cos i've got the 'quotedString' bits.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Mar 2004
    Posts
    118
    Thanks izy, but basically I figured out that its not possible to use an insert statement in code like that....

    It doesent like the fact that I use brackets inside the strings im inserting...

    Im using a recordset now. That works.

    Code:
    Dim db As Database
    Dim rs As Recordset
    Dim strsql As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("filtertable", dbOpenDynaset)
    
    rs.AddNew
    rs!fName = Me.fName
    rs!SQL = Me.FilterBox
    rs!Filter = Me.ResultBox
    rs.Update

Posting Permissions

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