Article ID: 172101 - Last Review: June 28, 2004 - Revision: 2.0

PRB: "Data Type Conversion Error" When Using a Data Control

View products that this article applies to.

This article was previously published under Q172101

SYMPTOMS

Deleting the data from a text box bound to certain data types, such as an integer or date, causes Error 3421:

"Data Type Conversion Error."

NOTE: If you are using a dbGrid, it's Error Method will produce Error 16389.

Back to the top

This is designed behavior of DAO. The data control is sending a NULL string to DAO and DAO attempts to coerce it to a number and fails. This is not a problem with the Data Control: The same behavior can be observed without the use of a Data Control, using only code. For example:

   Set db = OpenDatabase("nwind.mdb", False, False)
   Set rs = db.OpenRecordset("select shipvia from orders", dbOpenDynaset)
   rs.Edit
   rs(0) = ""

Back to the top

RESOLUTION

Use the following code in the Data Controls Validate method to allow the edit to occur as expected by user:

   Private Sub Data1_Validate(Action As Integer, Save As Integer)
      If Text1.DataChanged Then
         Text1.DataChanged = False 'So this data is not saved
         Data1.UpdateRecord  ' This saves the data that
                             ' may have changed in the other controls

         ' Now clear the numeric field
         Data1.Recordset.Edit
         Data1.Recordset![Year Born] = Null
         Data1.Recordset.Update
     End If
   End Sub

Back to the top

STATUS

This behavior is by design.

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

Place a data control and a textbox on a form.
Bind the textbox to an integer column of a table.
Delete the data from the text box.
Move off the record. Note that you get the "data type conversion error in the error event of the data control. This happens with Jet 3.5, 3.0, or 2.5, but does not happen in 16-bit Visual Basic.

Back to the top