[AccessD] Insert Into with Null Fields

    You are most welcome Dan!

A.D.Tejpal
------------

  ----- Original Message ----- 
  From: Dan Waters 
  To: 'Access Developers discussion and problem solving' 
  Sent: Tuesday, November 13, 2007 00:04
  Subject: Re: [AccessD] Insert Into with Null Fields


  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.