[AccessD] Insert Into with Null Fields

Dan Waters dwaters at usinternet.com
Mon Nov 12 12:34:50 CST 2007


Hello A.D.

This is absolutely clever!

Thanks!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Monday, November 12, 2007 12:06 PM
To: Access Developers discussion and problem solving
Cc: A.D.TEJPAL
Subject: Re: [AccessD] Insert Into with Null Fields

Dan,

    As an interesting alternative with significant advantages, VALUES clause
can be replaced by SELECT clause. Sample code in form's module, as given at
(A) below, demonstrates this approach. If it happens to be a subform, the
code given at (B) below would be applicable.

    With this technique, all your problems should stand resolved. Some of
the advantages inherent in this method are:

    1 - While building the SQL string, you don't have to worry about data
type.
    2 - If the source text box is blank, Null value gets inserted in the
destination field (Required property set to No).
    3 - If there are any embedded quotes (whether single, double or various
combinations of these) in the contents of source text box, the same get
faithfully transferred to destination field without needing any special
treatment. If such a task were to be implemented via VALUES clause, tedious
prior fixing of such embedded quotes would have been necessary.

    Note - As form based parameters are involved, DoCmd.RunSQL method has
been used instead of CurrentDb.Execute.

Best wishes,
A.D.Tejpal
------------

A - Code in independent form's module
F_Main is the name of this form. TxtA, TxtB and 
TxtC are text boxes whose values are to be appended 
to fields A, B & C respectively in table T_Main. 
(T_Dummy is a single field single record table).
'=====================================
Private Sub CmdAppend_Click()
    Dim Qst As String
    
    Qst = "INSERT INTO T_Main (A, B, C) " & _
            "SELECT Forms!F_Main!TxtA, " & _
            "Forms!F_Main!TxtB, " & _
            "Forms!F_Main!TxtC FROM T_Dummy;"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL Qst
    DoCmd.SetWarnings True
End Sub
'=====================================

B - Code in subform's module
F_Main is the name of parent form. SF_Sub is the
name of control serving as container for the subform.
Txt1, Txt2 and Txt3 are text boxes on the subform
whose values are to be appended to fields A, B & C
respectively in table T_Main. (T_Dummy is a single 
field single record table).
'=====================================
Private Sub CmdAppend_Click()
    Dim Qst As String
    
    Qst = "INSERT INTO T_Main (A, B, C) " & _
            "SELECT Forms!F_Main!SF_Sub!Txt1, " & _
            "Forms!F_Main!SF_Sub!Txt2, " & _
            "Forms!F_Main!SF_Sub!Txt3 FROM T_Dummy;"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL Qst
    DoCmd.SetWarnings True
End Sub
'=====================================

  ----- Original Message ----- 
  From: Dan Waters 
  To: 'Access Developers discussion and problem solving' 
  Sent: Monday, November 12, 2007 01:40
  Subject: Re: [AccessD] Insert Into with Null Fields


  Hi Susan,

  If IsNull(txtA) then
  Stg = "INSERT INTO tblMain ( B, C )" _
  & " VALUES ('" & txtB & "', '" & txtC & "')"
  Else
  Stg = "INSERT INTO tblMain ( A, B, C )" _
  & " VALUES ('" & txtA & "', '" & txtB & "', '" & txtC & "')"
  End If

  Trying to insert a Null value creates an error.

  Dan

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
  Sent: Sunday, November 11, 2007 1:49 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Insert Into with Null Fields

  > Sometimes when I write an INSERT INTO string, I have to take into
account
  > that a text box or memo field might be null.  If there is one
possibility 
  > of a Null, I have to write and choose from 2 different strings, if there
are
  > two possibilities of Null fields, then I have to write and choose from 4
  > different strings, and so on.
  >
  > Is there a way out of this?

  =========I don't understand the need for mutliple statements. Can you 
  provide a simple example?

  Susan H.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list