infamous 3421 error, null values on input boxes

[benjammin]

Hi,



I'm working with a form and not all the values are required but when they're null i receive a run-time error 3421, data type conversion error. I have put an If/Then on the EquipmentName combo box and that works but i was wondering if there was a more efficient way than putting if/thens around all the combo boxes that can be null.

The majority of fields that have this error go to fields that are numeric, not text...

Also, this only happens after I've entered data in once before so I assume it has something to do with setting those fields to "" (to clear the form) after the data is first input.

Thank you!

Private Sub cmdSaveAdd_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tmpCalID As Long


Set db = CurrentDb
Set rst = db.OpenRecordset("tblCalLog", dbOpenDynaset)
    rst.AddNew
        rst!CalTime = txtDateTime
        
        If cboEquipmentName = "" Then
            rst!EquipmentID = Null
        Else
            rst!EquipmentID = cboEquipmentName
        End If
        
    rst.Update
    rst.Bookmark = rst.LastModified
    tmpCalID = rst!CalibrationID
    
    Me!txtDateTime = ""
    Me!cboEquipmentName = ""


rst.Close


Set rst = db.OpenRecordset("tblCalWL", dbOpenDynaset)


    rst.AddNew
        rst!SiteID = cboSiteName
        rst!LocationID = cboLocationName
        rst!WellID = cboWellName
        rst!CheckedBy = TxtCheckedBy
        rst!DTWMeasured = txtDTWM
        rst!DTWLogger = txtDTWDL
        rst!OneFoot1 = txtOneFoot1
        rst!OneFoot2 = txtOneFoot2
        rst!Comments = txtComments
        rst!CalibrationID = tmpCalID
    rst.Update


    Me!cboSiteName = ""
    Me!cboLocationName = ""
    Me!cboWellName = ""
    Me!TxtCheckedBy = ""
    Me!txtDTWM = ""
    Me!txtDTWDL = ""
    Me!txtOneFoot1 = ""
    Me!txtOneFoot2 = ""
    Me!txtComments = ""




rst.Close
db.Close


End Sub

[randman1]

It appears as if you are just adding a new record to tblCallLog with an unbound form. My method for doing so was developed from the need to store single and double quotes but I also found that it would support null values. You can see if it will work for you application. It's about the same amount of coding but I feel it's structure is much easier to read and understand.

You may also consider setting your controls to null rather than zero length strings to clear the controls.

Private Sub cmdSaveAdd_Click()
    Dim oQd As QueryDef
    Dim sSql As String
    
    sSql = "INSERT INTO tblCallLog (SiteID, LocationID, WellID, CheckedBy, DTWMeasured, DTWLogger, OneFoot1, OneFoot2, Comments, CalibrationID ) " & _
        "VALUES (xSiteID, xLocationID, xWellID, xCheckedBy, xDTWMeasured, xDTWLogger, xOneFoot1, xOneFoot2, xComments, xCalibrationID)"

    Set oQd = CurrentDb.CreateQueryDef("")
    oQd.SQL = sSql

    With oQd
        .Parameters("xSiteID") = Me.cboSiteName
        .Parameters("xLocationID") = Me.cboLocationName
        .Parameters("xWellID") = Me.cboWellName
        .Parameters("xCheckedBy") = Me.TxtCheckedBy
        .Parameters("xDTWMeasured") = Me.txtDTWM
        .Parameters("xDTWLogger") = Me.txtDTWDL
        .Parameters("xOneFoot1") = Me.txtOneFoot1
        .Parameters("xOneFoot2") = Me.txtOneFoot2
        .Parameters("xComments") = Me.txtComments
        .Parameters("xCalibrationID") = Me.tmpCalID
    End With
    
    oQd.Execute dbFailOnError
    
    Me!txtDateTime = ""
    Me!cboEquipmentName = ""
    Me!cboSiteName = ""
    Me!cboLocationName = ""
    Me!cboWellName = ""
    Me!TxtCheckedBy = ""
    Me!txtDTWM = ""
    Me!txtDTWDL = ""
    Me!txtOneFoot1 = ""
    Me!txtOneFoot2 = ""
    Me!txtComments = ""

End Sub

[randman1]

... or you could test for null with the NZ function.

rst!EquipmentID = NZ(cboEquipmentName, "", cboEquipmentName)

[benjammin]

I'm back, still having this error but just getting around to working on it...

Private Sub cmdSaveRec_Click()


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tmpCalID As Long


Set db = CurrentDb
Set rst = db.OpenRecordset("tblCalLog", dbOpenDynaset)
    rst.AddNew
        rst!CalTime = txtDateTime
        rst!EquipmentID = cboEquipmentName
    rst.Update
    rst.Bookmark = rst.LastModified
    tmpCalID = rst!CalibrationID
    
    Me!txtDateTime = ""
    Me!cboEquipmentName = ""


rst.Close


Set rst = db.OpenRecordset("tblCalCond", dbOpenDynaset)


    rst.AddNew
        rst!SiteID = cboSiteName
        rst!LocationID = cboLocationName
        rst!WellID = cboWellName
        rst!CheckedBy = txtCheckedBy
        
        If txtCondInst = "" Then
            rst!CondInst = Null
        Else
            rst!CondInst = txtCondInst
        End If
        
        
        If txtCondSoars = "" Then
            rst!CondSoars = Null
        Else
            rst!CondSoars = txtCondSoars
        End If
        
        
        If txtPostCondSoars = "" Then
            rst!PostCondSoars = Null
        Else
            rst!PostCondSoars = txtPostCondSoars
        End If
        
        
        If txtStanDI = "" Then
            rst!StanDI = Null
        Else
            rst!StanDI = txtStanDI
        End If
        
        
        If txtStan01 = "" Then
            rst!Stan01 = Null
        Else
            rst!Stan01 = txtStan01
        End If


        
        If txtStan1 = "" Then
            rst!Stan1 = Null
        Else
            rst!Stan1 = txtStan1
        End If
        
        
        If txtStan5 = "" Then
            rst!Stan5 = Null
        Else
            rst!Stan5 = txtStan5
        End If
        
        
        If txtStan10 = "" Then
            rst!Stan10 = Null
        Else
            rst!Stan10 = txtStan10
        End If
        
                
        rst!Comments = txtComments
        
        rst!CalibrationID = tmpCalID
        
    rst.Update


    Me!cboSiteName = ""
    Me!cboLocationName = ""
    Me!cboWellName = ""
    Me!txtCheckedBy = ""
    Me!txtCondInst = ""
    Me!txtCondSoars = ""
    Me!txtPostCondSoars = ""
    Me!txtStanDI = ""
    Me!txtStan01 = ""
    Me!txtStan1 = ""
    Me!txtStan5 = ""
    Me!txtStan10 = ""
    Me!txtComments = ""




rst.Close
db.Close


End Sub

This is really messy, is there a better way to allow for empty values and write the record?

[TG_W]

Why set the value to null? Why not just set it to the data source on the form? If it's null and the field it is writing to is not a Primary Key, then there shouldn't be a problem. Below is a quick blurb from one of my data entry forms. If the form source is null, the record is null, unless I have a precheck preventing null items, which will stop the code and a messge box will appear notifying the user to enter data in the field.

    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Select * from Equipment")
        
        rst.AddNew
        rst!ID = Forms!sfrNewItem!ID
        rst!PSG = Forms!sfrNewItem!PSG
        rst!SNo = Forms!sfrNewItem!S_No
        rst!Desc = Forms!sfrNewItem!Desc
        rst!PkgEng = Forms!sfrNewItem!PkgEng
        
        Set dbs = Nothing
        rst.Update
        rst.Close

[benjammin]

Because if i don't set it to null it gives me the 3421 error, i just tried this under rst.AddNew...

            rst!Stan01 = txtStan01
            rst!Stan1 = txtStan1

If I fill in txtStan01 and leave txtStan1 EMPTY it will tell me there's a 3421 error on txtStan1 since i didn't fill that out which is why i've been doing that setting to null thing if the field is empty

[TG_W]

Did you try referencing the Text Box on the form the way I have it in my code? I have never had a 3421 error with null fields calling the boxes in that manner.