SQL INSERT and a Null Value

[thegameoflife]

I’m trying to do a SQL insert statement. The statement work good only if all the fields are filled in on the form. What can I do to allow for null fields in the form? Currently the SQL statement will error out if one of the fields is null.

Private Sub cmdsave_Click()

Dim rsmytable As Recordset
Dim Sqll As String
Dim prvname
Dim membr
Dim membrnum
Dim claimnum
Dim reqdate
Dim amt
Dim reasn
Dim prdct
Dim rq
Dim cmpdate
Dim opper

    Me.txtprovider.SetFocus
    prvname = Me.txtprovider.Text
    Me.txtmember.SetFocus
    membr = Me.txtmember.Text
    Me.txtmembernum.SetFocus
    membrnum = Me.txtmembernum.Text
    Me.txtclaimnum.SetFocus
    claimnum = Me.txtclaimnum.Text
    Me.txtreqdate.SetFocus
    reqdate = Me.txtreqdate.Text
    Me.txtamount.SetFocus
    amt = Me.txtamount.Text
    Me.cmbreason.SetFocus
    reasn = Me.cmbreason.Text
    Me.txtproduct.SetFocus
    prdct = Me.txtproduct.Text
    Me.txtreq.SetFocus
    rq = Me.txtreq.Text
    Me.txtcompdate.SetFocus
    cmpdate = Me.txtcompdate.Text
    Me.txtopperror.SetFocus
    opper = Me.txtopperror.Text
       
        
    Set rsmytable = New ADODB.Recordset
    
    rsmytable.ActiveConnection = CurrentProject.Connection
    rsmytable.Open "TBL_Master", , adOpenKeyset, adLockOptimistic, adCmdTable
    
    Sqll = "Insert INTO TBL_Master(Provider,Member,Member_Num,Claim_Num,Req_date,Amount,Reason,Product,Req,Comp_Date,Opp_Error)" & _
    "VALUES('" & prvname & "','" & membr & "'," & membrnum & ",'" & claimnum & "','" & reqdate & "','" & amt & "','" & reasn & "','" & prdct & "','" & rq & "','" & cmpdate & "','" & opper & "');"
    DoCmd.RunSQL Sqll
    
    rsmytable.Close
    


End Sub

 

[RobertT687]

For each assignment statement you must test for NULL and provide a non-NULL value where appropriate. For example:

Me.txtmembernum.SetFocus
If IsNull(Me.txtmembernum) Then
membrnum = " "
Else
membrnum = Me.txtmembernum.Text
End If

Your only other alternative is to alter the SQL table definition to permit the insertion of NULL values; probably a poor idea.

Also, it isn't necessary to SetFocus to a field before referencing it.

 

[thegameoflife]

Your first idea will work thank you

 

[SBendBuckeye]

There's a neat little function called Nz (NullZero) which will return an initialized state consistent with the data type of the variable (eg string returns null string, numeric returns 0, etc).

I use it to initialize data without having to check the type. Following are a few examples:

intNumber = Nz(intNumber + Null)
strText = Nz(strText & Null)

Good Luck!