ACC: "Expected Function or Variable" Error Message

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

When you run or compile a procedure in a Microsoft Access database that references a field with the same name as a data-access method on 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 with the same name as a data-access method on a Recordset object. For example, you may have a field called "Close" that is preceded with a dot:

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

RESOLUTION

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

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

STATUS

This behavior is by design.

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, then 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. Open a Microsoft Access database.

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

3. Create a module and type the following procedure:

         Function Test()
           Dim db As DATABASE, tb As 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, search the Help Index for "naming conventions," or ask the Microsoft Access 97 Office Assistant.