Query_AppendValuesViaSelectClause.mdb |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
Posted: 08 Jul 2008 at 10:19pm |
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)
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |