none
sql does not add records RRS feed

  • Question

  • i have a sql statement , it gives no errors , but it is not adding a record to my file ...

    LUser = CurrentUser()
    fnr = Me.Parent.FSNO

    strSQL = "INSERT INTO Filex  ( FFFSNO, FFWuser, FFWoms ) SELECT " & fnr & " "
    strSQL = strSQL & "AS FFFSNO, " & Chr(34) & LUser & Chr(34) & " AS FFWuser, " & Chr(34) & Tekst12 & Chr(34)
    strSQL = strSQL & " AS FFWoms FROM Filex

    Friday, May 20, 2016 11:57 AM

Answers

  • The only thing your code is doing is assigning the sql statement to  the variable strSql

    To executing this statement you must use

    CurrentDb.Execute strSql, dbFailOnError

    after strSql hold the statment

    • Marked as answer by tekoko10 Friday, May 20, 2016 1:06 PM
    Friday, May 20, 2016 12:30 PM
  • Try using a debug.print statement following the last line of code:

    LUser = CurrentUser()
     fnr = Me.Parent.FSNO
    
    strSQL = "INSERT INTO Filex  ( FFFSNO, FFWuser, FFWoms ) SELECT " & fnr & " "
     strSQL = strSQL & "AS FFFSNO, " & Chr(34) & LUser & Chr(34) & " AS FFWuser, " & Chr(34) & Tekst12 & Chr(34)
     strSQL = strSQL & " AS FFWoms FROM Filex
    
    ' Print the string to the immemmediate window:
    debug.print strSQL
    

    That will write a copy of the resulting SQL string in the immediate window in the VBA editor, which you can copy/paste into the SQL view in the Query Builder, to test the query that your code is trying to execute.

    Also, as a sanity check... you have not listed any code that actually RUNS your query.  You need a statement to this effect:

    CurrentDB.Execute strSQL,dbfailonerror

    Docmd.RunSQL strSQL is another method, but the currentdb.execute method will actually fail with an error message if something goes wrong, which makes it more useful when debugging than the RunSQL statement, which can fail 'quietly'.


    Miriam Bizup Access MVP

    • Marked as answer by tekoko10 Friday, May 20, 2016 1:06 PM
    Friday, May 20, 2016 12:14 PM

All replies

  • Try using a debug.print statement following the last line of code:

    LUser = CurrentUser()
     fnr = Me.Parent.FSNO
    
    strSQL = "INSERT INTO Filex  ( FFFSNO, FFWuser, FFWoms ) SELECT " & fnr & " "
     strSQL = strSQL & "AS FFFSNO, " & Chr(34) & LUser & Chr(34) & " AS FFWuser, " & Chr(34) & Tekst12 & Chr(34)
     strSQL = strSQL & " AS FFWoms FROM Filex
    
    ' Print the string to the immemmediate window:
    debug.print strSQL
    

    That will write a copy of the resulting SQL string in the immediate window in the VBA editor, which you can copy/paste into the SQL view in the Query Builder, to test the query that your code is trying to execute.

    Also, as a sanity check... you have not listed any code that actually RUNS your query.  You need a statement to this effect:

    CurrentDB.Execute strSQL,dbfailonerror

    Docmd.RunSQL strSQL is another method, but the currentdb.execute method will actually fail with an error message if something goes wrong, which makes it more useful when debugging than the RunSQL statement, which can fail 'quietly'.


    Miriam Bizup Access MVP

    • Marked as answer by tekoko10 Friday, May 20, 2016 1:06 PM
    Friday, May 20, 2016 12:14 PM
  • The only thing your code is doing is assigning the sql statement to  the variable strSql

    To executing this statement you must use

    CurrentDb.Execute strSql, dbFailOnError

    after strSql hold the statment

    • Marked as answer by tekoko10 Friday, May 20, 2016 1:06 PM
    Friday, May 20, 2016 12:30 PM
  • Sorry folks , i had a problem with integrity , sorry sorrry !

    Friday, May 20, 2016 1:06 PM