DOCUMENT:Q182945  11-JAN-2001  [vbwin]
TITLE   :PRB: Database Field in Collection Treated Differently in VB
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:
KEYWORDS:kbGrpDSVB

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0 
 - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

You receive the error "No Current Record" when referencing a database field that
has been added to a collection. The code may have functioned without error in
Visual Basic 4.0.

CAUSE
=====

The Collection Object in Visual Basic 5.0 and later provides more flexibility
when adding objects to a collection. To provide that additional level of
functionality, Visual Basic 5.0 and later no longer make any assumptions about
the item you are adding to the collection and will therefore add exactly what
you specify. This differs from previous versions of Visual Basic where adding a
reference to a field actually added the contents of that field's default
property. For example, the following line of Visual Basic 4.0 code would add the
contents of the "LastName" field (such as the text string "Smith") to the
collection:

      MyCollection.Add MyRecordset![LastName]

This same line of code in Visual Basic 5.0 or later adds a reference to the field
as a whole to the collection rather than the contents of the field. The Visual
Basic 5.0 or later equivalent to the above line of code would be:

      MyCollection.Add MyRecordset![LastName].Value

Therefore the programmer should be explicit in determining what is added to a
collection.

STATUS
======

This behavior is by design.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

1. Create a new Standard EXE project in Visual Basic. Form1 is created by
   default.

2. Add a reference to the "Microsoft DAO 3.5 Object Library."

3. Paste the following code into the Form1 code window:

         Private Sub Form_Load()
            Dim MyDatabase As Database
            Dim MyRecordset As Recordset
            Dim CollectionItem As Variant
            Dim MyCollection As New Collection

            'Open the database and recordset
            Set MyDatabase = OpenDatabase("C:\VB\NWIND.MDB")
            Set MyRecordset =  _
               MyDatabase.OpenRecordset("SELECT * FROM EMPLOYEES")
            'Add last name of each employee to the collection
            While Not MyRecordset.EOF
               MyCollection.Add MyRecordset![LastName]
               MyRecordset.MoveNext
            Wend

            'Display each item in collection
            For Each CollectionItem In MyCollection
               Debug.Print CollectionItem   '<- Generates an Error
            Next

            'Close the recordset and database
            MyRecordset.Close
            MyDatabase.Close
         End Sub

4. If necessary, modify the path to "NWIND.MDB" in the call to the
   "OpenDatabase" method.

5. Execute the sample project. Note that you will receive the "No Current
   Record" error message.

Additional query words: kbDAO kbVBp500 kbVBp600 kbVBp kbdsd kbDSupport

======================================================================
Keywords          : kbGrpDSVB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVBA500 kbVBA600 kbVB500 kbVB600
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.