Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Query_AppendValuesViaSelectClause.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

Query_AppendValuesViaSelectClause.mdb

 Post Reply Post Reply
Author
Message
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Query_AppendValuesViaSelectClause.mdb
    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)

Download Access 2K/XP/2K3/2K7: uploads/37/Query_AppendValuesViaSelectClause.zip

--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links


Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down