This sample db demonstrates use of SELECT clause in preference to VALUES
clause, for appending values held by form / subform controls into the destination
table.
Some advantages of using SELECT instead of VALUES clause are mentioned
below:
(a) No need to worry about specific delimiters while concatenating values for
various data types (e.g. ' for text type and # for date type data). Moreover,
there is no problem if the value held by any of the controls happens to be Null
(provided destination table is in a position to accept Null value in the field
concerned).
(b) No need to fix any embedded quotes, whether single or double.
(c) No risk of dates getting mis-interpreted (in the process of concatenation into
SQL string) when local system settings for short date are not as per US
settings (mm/dd/yyyy).
Following three styles of SQL for append action are shown separately for values
held by controls on an independent form as well as those on a subform. Three
controls are involved in each case, holding text, date and number type data
respectively:
(a) Use form/subform values directly.
(b) Use form/subform values via recordset. Function Fn_rsApp() is used.
(c) Use form/subform values via array. Function Fn_arApp() is used.
A dummy table named T_Dummy with single field single record is used for
facilitating adoption of SELECT clause instead of VALUES clause. Contents of this
table are of no significance.
Form/subform controls and correspondingly appended record in destination table
get highlighted in special color. Simultaneously, the SQL actually used for the
selected append action is also displayed for ready reference.
5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format) Download Access 2K/XP/2K3/2K7: http://www.rogersaccesslibrary.com/forum/uploads/37/Query_AppendValuesViaSelectClause.zip - uploads/37/Query_AppendValuesViaSelectClause.zip
------------- --
Email: mailto:adtejpal@gmail.com - adtejpal@gmail.com
|