Insert Code Not Working

[KKash]

The code listed below is the "click" event on an "update" button." When I
click on the button in my form, it doesn't update my table. However, it
doesn't give me an error message!! HELP!!! Why won't it update?

CurrentDb.Execute "INSERT INTO tblSavedMTNs(MTN, AccountNo, GroupID,
AccountName)" & _
"VALUES (" & Chr$(34) & txtMTN & Chr$(34) & ", " & _
Chr$(34) & AccountNo & Chr$(34) & ", " & _
Chr$(34) & GroupID & Chr$(34) & ", " & _
Chr$(34) & AccountName & Chr$(34) & ") "

 

[Graham Mandeno]

Hi KKash

If you add the option dbFailOnError to your Execute method then it will
raise an error for you.:

CurrentDb.Execute "INSERT INTO tblSavedMTNs(MTN, " & _
"AccountNo, GroupID, AccountName)" & _
"VALUES (" & Chr$(34) & txtMTN & Chr$(34) & ", " & _
Chr$(34) & AccountNo & Chr$(34) & ", " & _
Chr$(34) & GroupID & Chr$(34) & ", " & _
Chr$(34) & AccountName & Chr$(34) & ") ", _
dbFailOnError

Are all these four fields text fields? For a numeric field you should not
use the enclosing Chr$(34) quotes.

If it's still not clear what is wrong then assign your SQL string to a
string variable and examine it in the Immediate window, then copy and paste
it into the SQL view of an empty query and try to run it. You will often
get a more helpful message this way.

 

[Klatuu]

There are some syntax issues and as Graham said, the reason you don't see an
error is because you did not use the dbFailOnError option. The Execute
method does not go through the Access User Interface. It goes directly to
Jet. That is why it is faster and doesn't trigger warning messages, but that
also means is doesn't trhow an error unless you use the option. Here is how
your code should be, assuming all four fields are text fields:

Dim strSQL As String

strSql = "INSERT INTO tblSavedMTNs(MTN, AccountNo, GroupID, " & _
"AccountName)" & "VALUES (""" & txtMTN & """, """ & _
AccountNo & """, """ & GroupID & """, """ & tName & """);"
CurrentdbExecute strSQL, deFailOnError

You can execute it straight from the Execute without first putting it in a
string, but first putting it in a string ensures you have the syntax correct
as far as formatting is concerned. That does not mean the syntax is valid
for Jet, it just means it is a complete string that can be sent to Jet.