JJ said:
From the statement below, var is not recognized when DoCmd RunSQL is
executed. What's wrong with it ?
Dim SQL, var As String
var = Form!Test!testvar1 & Form!Test!testvar2 & Form!Test!Testvar3
SQL = "INSERT INTO TABLENAME VALUES(Forms!Test!name, var)"
In DoCmd.RunSQL
That should be SQL = "INSERT INTO TABLENAME VALUES('" &
Forms!Test!name & "', '" & var "')". Note the single vs double
quotes which will be hard to pick out on your newsgroup reader.
Single quotes are used as text field delimiters. # for date fields.
Numeric fields don't have a delimiter.
SQL statements require that the dates be either completely unambiguous
such as in yyyy-mm-dd. mm/dd/yy, or mm/dd/yyyy format. Otherwise
Access/Jet will do it's best to interpret the date with unknown
results depending on the specific date it is working with. You can't
assume that the system you are working on is using those date formats.
Thus you should use the logic at the following web page.
Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm
Also note that you should also be using the field names on your insert
just in case someone reorders the fields on the table. Presumably
though you simplified things in your sample code.
Finally I would not use docmd.sql.
I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText
If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.
Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -
http://msmvps.com/blogs/access/