Database Entry Form: Error when setting Null values

Database Entry Form: Error when setting Null values

Post by Peter Stan » Mon, 12 Jan 1998 04:00:00



Hello,
I have an entry form for a database table with a DbCombo control for a long
field.
When I enter an empty string, I want it to be replaced by Null. In the
validation
event of the data control I call the folling procedure ReplaceEmptyByNull.
Now when I set the value of this field for the first record to empty,
move to the next record, set the value for the second record to empty
and move again, the following error occurs:
 Data type conversion error. DAO.Field
 The action was cancelled by an associated object. DAO.Generic Object

The error is raised in the Update call.
The strange thing is that this error doesn't occur for the first record.
The same error occurs, when I set the value to 0 instead of Null.
I am using VB5/SP3. Is this a VB bug or do I something wrong?
Here is the procedure:
' Replace an empty string by a Null value during validation
' The control can be any control with text and datachanged properties
Private Sub ReplaceEmptyByNull(ctrl As Control, _
                               data As data, _
                               ByVal FieldName As String, _
                               ByVal save As Integer)
    On Error GoTo ReplaceEmptyByNullErr
    If save Then
        With ctrl ' Enter Null for value when the text is empty
            If .DataChanged Then
                If .text = "" Then
                    With data.Recordset
                        .Edit
                        .Fields(FieldName).value = Null       ' replace
empty string by Null value
                        .Update
                    End With
                    .DataChanged = False
                End If
            End If
        End With
    End If
    Exit Sub

ReplaceEmptyByNullErr:
    Dim e As Error
    Dim s As String
    s = ""
    For Each e In DBEngine.Errors
        s = s & e.Description & " " & e.Source & vbCrLf
    Next

    MsgBox s, vbCritical, ErrTitle
End Sub

 
 
 

1. Data Entry Form: Error when setting Null values

Hello,
I have an entry form for a database table with a DbCombo control for a long
field.
When I enter an empty string, I want it to be replaced by Null. In the
validation
event of the data control I call the folling procedure ReplaceEmptyByNull.
Now when I set the value of this field for the first record to empty,
move to the next record, set the value for the second record to empty
and move again, the following error occurs:

     Data type conversion error. DAO.Field
     The action was cancelled by an associated object. DAO.Generic Object

 The error is raised in the Update call.
The strange thing is that this error doesn't occur for the first record.
The same error occurs, when I set the value to 0 instead of Null.
I am using VB5/SP3. Is this a VB bug or do I something wrong?

Here is the procedure:

' Replace an empty string by a Null value during validation
' The control can be any control with text and datachanged properties
Private Sub ReplaceEmptyByNull(ctrl As Control, _
                               data As data, _
                               ByVal FieldName As String, _
                               ByVal save As Integer)
    On Error GoTo ReplaceEmptyByNullErr
    If save Then
        With ctrl ' Enter Null for value when the text is empty
            If .DataChanged Then
                If .text = "" Then
                    With data.Recordset
                        .Edit
                        .Fields(FieldName).value = Null       ' replace
empty string by Null value
                        .Update
                    End With
                    .DataChanged = False
                End If
            End If
        End With
    End If
    Exit Sub

ReplaceEmptyByNullErr:
    Dim e As Error
    Dim s As String
    s = ""
    For Each e In DBEngine.Errors
        s = s & e.Description & " " & e.Source & vbCrLf
    Next

    MsgBox s, vbCritical, ErrTitle
End Sub

2. Send stand-alone ADO recordset to SQL procedure

3. How to allow Null Value entry in VB Forms

4. Database and Table Sizes

5. Use ISNULL to set the value to the default value when NULL

6. Question

7. cannot delete entries in bound text control (no null values)

8. DTS Error: it tries to insert null into column when no not null is set

9. Null values: Classic null value problem (?)

10. SET NULL / SET NOT NULL