"Object variable or With block variable not set" error message when you use Delete or UpdateBatch methods (287485)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q287485
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you use code that performs Delete and UpdateBatch methods based on the Forms collection, you receive the following error message:
Microsoft Visual Basic

Run-time error '91':
Object variable or With block variable not set

RESOLUTION

Make sure the Recordset object is not based on the form's recordset if you plan to use the Delete or UpdateBatch methods. For an example, see the following code: 
Sub Test()

   Dim rs As New ADODB.Recordset
   rs.Open "SELECT * FROM TableX", CurrentProject.Connection, _
     adOpenKeyset, adLockOptimistic
   rs.Delete
   rs.UpdateBatch

End Sub

STATUS

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

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample project NorthwindCS.adp connected to SQL Server 2000.
  2. Create the following new table:
       Table: TableX
   ------------------------
   Column Name: ID
   Data Type: int
   Allow Nulls: not checked
   Identity: Yes

   Column Name: Field1
   Data Type: char 

   Table Properties: TableX
   ------------------------
   PrimaryKey: ID
  3. Save the table as TableX.
  4. Open the TableX table in Datasheet view.
  5. Enter five rows of data in the Field1 field.
  6. Create a new, unbound form.
  7. Switch to Form view.
  8. Press ALT+F11 to open the Visual Basic Editor.
  9. Insert a new module, and then type or paste the following code:
    Sub Test()

   Dim rs As New ADODB.Recordset
   rs.Open "SELECT * FROM TableX", CurrentProject.Connection, _
     adOpenKeyset, adLockOptimistic
   Set Forms(0).Recordset = rs
   Forms(0).Recordset.Delete
   Forms(0).Recordset.UpdateBatch

End Sub
  10. Place the mouse pointer anywhere in the procedure, and then press F5. Note that you receive the error message that is mentioned in the "Symptoms" section of this article.
Modification Type:MinorLast Reviewed:7/28/2006
Keywords:kbfunctions KbVBA kbProgramming kbbug kberrmsg kbpending KB287485
©2004 Microsoft Corporation. All rights reserved.