ACC2000: "Expected Function or Variable" Error Message (210134)

SYMPTOMS

When you run or compile a procedure in a Microsoft Access database that references a field that has the same name as a data access method of a Recordset object, you may receive one of the following error messages:

Expected Function or variable

-or-

Argument not optional

-or-

Type Mismatch

CAUSE

You used the dot (.) syntax when you referenced a field that has the same name as a data access method of a Recordset object. For example, you may have a field called "Close" that is preceded by a dot, as follows:

Function Test()
  Dim db As DAO.DATABASE, tb As DAO.Recordset, x As Variant
  Set db = CurrentDb
  Set tb = db.OpenRecordset("Table1")
  x = x + tb.[Close]
End Function

RESOLUTION

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Modify the procedure to use the exclamation point (!) syntax in the reference, for example:

Function Test()
   Dim db As DAO.DATABASE, tb As DAO.Recordset, x As Variant
   Set db = CurrentDb
   Set tb = db.OpenRecordset("Table1")
    x = x + tb![Close]
End Function

MORE INFORMATION

The particular error message that you receive when you run or compile a procedure depends on the assigned name of the referenced field as follows:

If you receive the "Expected Function or variable" error message, then your field has the same name as one of the following data access methods for Recordset objects:

   AddNew
   CancelUpdate
   Close
   Delete
   Edit
   FillCache
   MoveFirst
   MoveLast
   MoveNext
   MovePrevious
   Requery
   Update

If you receive the "Argument not optional" error message, your field has the same name as one of the following data access methods for Recordset objects:

   FindFirst
   FindLast
   FindNext
   FindPrevious
   Move
   Seek

If you receive the "Type Mismatch" error message, then your field has the same name as one of the following data access methods for Recordset objects:

   Clone
   CopyQueryDef
   OpenRecordset

Steps to Reproduce Behavior

Create a new Access database.
Create a new table named Table1, and add one field named Close.

Create a module and type the following procedure:

Function Test()
   Dim db As DAO.DATABASE, tb As DAO.Recordset, x As Variant
   Set db = CurrentDb
   Set tb = db.OpenRecordset("Table1")
   x = x + tb.[Close]
End Function

On the Run menu, click Compile All Modules. Note that you receive the following error message:
Expected Function or variable