Answered by:
sql does not add records

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.FSNOstrSQL = "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 FilexFriday, 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