Update query from form "invalid use of null"

[Lady_Jane]

Hi everyone, my form looks like the attached image.

The checkmark is associated with the following code and is supposed to add two records to tbl_SampleActivity.

It keeps giving me an "invalid use of null" error. I suspect this might have something to do with the yes/no field (Sampled). I am not sure...

Private Sub Save_Click()
On Error GoTo Err_Save_Click
    
    Dim strUID As String
    strUID = txtUID.Value
    
    Dim intYear As Integer
    intYear = txtYear.Value
    
    Dim strSampler As String
    strSampler = cmbSampler.Value
    
    Dim strPermission As String
    strPermission = cmbPermission.Value
    
    Dim booBenthos As Boolean
    booBenthos = chkBenthos.Value
    Dim benthos As Boolean
    If booBenthos = True Then
        benthos = -1
    ElseIf booBenthos = False Then
        benthos = 0
    End If
    
    Dim booChem As Boolean
    booChem = chkChem.Value
    Dim chem As Boolean
    If booChem = True Then
        chem = -1
    ElseIf booChem = False Then
        chem = 0
    End If
    
    Dim strBenReason As String
    strBenReason = txtBenReason.Value
    
    Dim strChemReason As String
    strChemReason = txtChemReason.Value
    
    Dim strComments As String
    strComments = txtComments.Value

    Dim strSQL As String

    ' Add the benthos row
    strSQL = "INSERT INTO tbl_SampleActivity (UID, Year, Sampler, SampleType, Sampled, Reason, PermissionType, Comments) VALUES (" _
    & strUID & "," & intYear & "," & strSampler & ", 'Benthos'" & benthos & "," & strChemReason & "," & strPermission & "," & strComments & ")"
    db.Execute strSQL

    ' Add the chem row
    strSQL = "INSERT INTO tbl_SampleActivity (UID, Year, Sampler, SampleType, Sampled, Reason, PermissionType, Comments) VALUES (" _
    & strUID & "," & intYear & "," & strSampler & ", 'Chemistry'" & chem & "," & strChemReason & "," & strPermission & "," & strComments & ")"
    db.Execute strSQL

    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
        
End Sub

Thanks!

[pbaldy]

A way to debug is to temporarily comment out the "On Error.." line and run the code, which will take you to the offending line. I'd point out that a String variable can not accept a Null, so if any of your textboxes were left empty you'd get that error. You can use the Nz() function to get around that, or test first if they're required. I also suspect that SQL isn't going to come out like you think it will. The way to debug that is to add

Debug.Print strSQL

right before the execute line, which will print out the finished SQL to the VBA Immediate window. Further, values for text data types need to be surrounded by quotes, which it doesn't look like you're doing. A decent tutorial here:

http://www.baldyweb.com/BuildSQL.htm

[Lady_Jane]

thanks,
getting rid of the on error helped a lot.
The error was caused by the boolean field like i thought.
I changed the line to:

Dim booChem As Boolean
booChem = chkChem.InSelection

but apparently you can't use the InSelection property outside of design view. So how am I supposed to pass the information to SQL saying whether or not the box is selected?

[pbaldy]

That wasn't in the original code. I think you want the Value property, which is the default. Try:

booChem = Me.chkChem

[Lady_Jane]

Alright,
figured it out.
1. change the form to unbound. This got rid of the error saying that the table was open and uneditable.
2. If the check box has never been checked, it is null. If it is checked and then unchecked, it is false.
3. Single quotes can be used inside double quotes to but necessary quotes around a string.