BUG: DAO Recordset Update Method Fails Silently (242459)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q242459

SYMPTOMS

When you attempt to add a new record to a recordset, the Data Access Objects (DAO) Recordset Update method may fail silently when the following conditions are met:
  • The Recordset is based on a query that joins two tables.
  • The table being updated has a Required field enforced by a Primary Key or Unique Index.
  • No values are supplied for any of the fields in the Recordset.

RESOLUTION

To work around this problem, use either of the following methods:
  1. If the joined table is unnecessary, remove the joined table from the query. Doing this causes the 3314 error to fire as expected. To remove the joined table:

    1. Open the database in Microsoft Access.
    2. Open the query in Design View.
    3. Delete the unnecessary table from the query.
    4. Save and close the query.
    Prior to calling the Update method, write code that checks to ensure that a value is supplied for all fields in the table where the Required Value property is set to Yes. For example, if you are using text boxes on a form for data entry and you have a text box named txtSSN and a required field in the table named SSN, check the Text property of the txtSSN text box prior to calling the Update method to ensure it contains a value as follows:
    If Trim(txtSSN.Text) = "" Then 
   MsgBox "The SSN field is Required. You must supply a valid SSN." 
   txtSSN.SetFocus 
   Exit Sub 
End If
  2. Prior to calling the Update method, write code that checks to ensure that a value is supplied for all fields in the table where the Required Value property is set to Yes. For example, if you are using text boxes on a form for data entry and you have a text box named txtSSN and a required field in the table named SSN, check the Text property of the txtSSN text box prior to calling the Update method to ensure it contains a value as follows:
    If Trim(txtSSN.Text) = "" Then 
   MsgBox "The SSN field is Required. You must supply a valid SSN." 
   txtSSN.SetFocus 
   Exit Sub 
End If

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When the above conditions are met, attempting to add a new record to the recordset can fail on the Update method with the following error:
Run-time error 3314
The field 'table.fieldname' can't contain a NULL value because the Required property for this field is set to True. Enter a value in this field.
Instead, the Update method fails silently. If you are using a DBGrid control to display the recordset, an empty row is added to the DBGrid control even though no row has been added to the actual database table.

Steps to Reproduce Behavior

  1. Open the Northwind.mdb database in Microsoft Access.
  2. Open the Orders table in design view.
  3. Change the DataType of the OrderID field from Autonumber to Number.
  4. Save the table and close it.
  5. Create a new query and add the Orders and Order Details tables.
  6. Join the tables on the OrderID field.
  7. Add several fields from the Orders table to the query grid.
  8. Save the query as qry_Orders and close it.
  9. Create a new Standard EXE project in Microsoft Visual Basic.
  10. From the Project menu, choose Components.
  11. In the Controls tab of the Components dialog box, select the Microsoft Data Bound Grid control.
  12. On the existing form, add the following controls:

    Data Control
    DB Grid Control
    Command Button

  13. Set the following properties for the controls.
    ControlPropertyValue
    Data ControlNamedcOrders
    DatabaseNameEnter the path to your Northwind.mdb database
    RecordSourceQry_Orders
    DB Grid ControlNameDBGrid1
    DataSourcedcOrders
    Command ButtonNamecmdNew
    CaptionAdd Record

  14. Add the following code to the form. You need to change the line in the Form_Load event that points to the Northwind.mdb database, to refer to the path where your database resides.
    Option Explicit
Dim db As DAO.Database
Dim rs As DAO.Recordset

Private Sub cmdNew_Click()

'Update fails silently when the Access query contains the two joined tables.
'Update errors with 3314 when the Order_Details table is removed from the query. 

dcOrders.Recordset.AddNew
'As a second workaround, check the Text property of any textboxes that supply data to required fields to ensure they contain values.
dcOrders.Recordset.Update

End Sub

Private Sub Form_Load()

Dim sDbName As String

Stop    'Change the path to point to where you saved the MDB File and then comment out this line.
sDbName = "C:\Database\Northwind.mdb"

Set db = DAO.DBEngine.Workspaces(0).OpenDatabase(sDbName, False, False)

'The problem occurs with both a DAO recordset AND the DataControl's Recordset.
'This scenario only illustrates the use of the DataControl's Recordset.
Set dcOrders.Recordset = db.OpenRecordset("Qry_Orders") 

End Sub
  15. Run the project and click the Add Record command button. You do not receive an error but an empty row is added to the DBGrid. Note that no record has been added to the Orders table.
Modification Type:MajorLast Reviewed:10/15/2002
Keywords:kbBug kbDatabase kbDSupport KB242459
©2002 Microsoft Corporation. All rights reserved.