ACC2000: "Expected Function or Variable" Error Message

• Microsoft Access 2000

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 need to reference the Microsoft DAO 3.6 Object Library.

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

1. Create a new Access database.



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



3. 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 

4. On the Run menu, click Compile All Modules. Note that you receive the following error message:




Expected Function or variable

REFERENCES

For more information about naming conventions, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "naming conventions" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: argument not optional type mismatch prb

Keywords : kberrmsg kbprg kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb