Query_AppendValuesViaSelectClause.mdb

    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: uploads/37/Query_AppendValuesViaSelectClause.zip