[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.