Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. "Object variable or With block variable not set" error message when you use Delete or UpdateBatch methods

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

Moderate: Requires basic macro, coding, and interoperability skills.

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

↑ Back to the top

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

↑ Back to the top

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

↑ Back to the top

Status

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

↑ Back to the top

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.

↑ Back to the top

Keywords: kb, kboffice12no, kbfreshness2006, kbpending, kberrmsg, kbbug, kbbillprodsweep, kbswept, kbarchive, kbsweptsoltax, kbprogramming, kbvba, kbfunctions, kboffice2003yes

↑ Back to the top

Article Info
Article ID : 287485
Revision : 3
Created on : 4/17/2018
Published on : 4/19/2018
Exists online : False
Views : 294