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.

Additional query words: argument not optional type mismatch

Keywords : kberrmsg kbprg
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbprb