ACC2002: No Records Are Displayed in a Subform That Is Based on a SQL Server Table (295219)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q295219
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you open a form that contains a subform that is based on a linked Microsoft SQL Server table, the subform does not display any records.

CAUSE

The fields that are used to link the subform to the main form are using the SQL Server UniqueIdentifier data type.

RESOLUTION

You can work around this problem by using a custom Visual Basic for Applications procedure that uses the StringFromGUID method on the Current event of the form. The following example demonstrates how to use this procedure.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
  1. First, follow the steps in the "Steps to Reproduce the Behavior" section later in this article to reproduce the problem.
  2. Then, open the frmMyCategories form in Design view.
  3. Click the subform object, and then on the View menu, click Properties.
  4. On the Data tab of the property sheet, clear the LinkChildFields and LinkMasterFields properties.
  5. On the View menu, click Code to view the module of the form.
  6. Add the following code to the Current event procedure of the form: 
    Private Sub Form_Current()
    Dim strSQL As String
    If Not Me.NewRecord Then
        strSQL = "SELECT * FROM dbo_MyProducts WHERE " & _
                 "dbo_MyProducts.fCategoryID = " & _
                  StringFromGUID(Me.Controls("CategoryID").Value)
    Else
        strSQL = "SELECT * FROM dbo_MyProducts WHERE False"
            
    End If
    Me.Controls("dbo_MyProducts").Form.RecordSource = strSQL
End Sub
  7. On the File menu, click Close and Return to Microsoft Access.
  8. Save the frmMyCategories form, and then close it.
  9. Open the frmMyProducts form in Design view.
  10. On the View menu, click Code to view the module of the form.
  11. Add the following code to the BeforeInsert event procedure of the form: 
    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.Controls("fCategoryID").Value = _
         Me.Parent.Controls("CategoryID").Value
End Sub
  12. On the File menu, click Close and Return to Microsoft Access.
  13. Save the frmMyProducts form, and then close it.
  14. Open the frmMyCategories form in Form view.
  15. Enter a new category name in the main form, and then add several new products in the subform.
  16. Close and then reopen the form.
Note that Microsoft Access correctly displays the related records in the subform based on the current record in the main form.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The UniqueIdentifier data type is a GUID data type, and it is stored internally as a 16-byte array, rather than as a string. The LinkChildFields and LinkMasterFields properties are unable to use this data type to link the main form to the subform.

The StringFromGUID method converts a GUID data type into a string. By converting the GUID data type into a string, you can use it in the WHERE clause of an SQL SELECT statement. Then you can assign the SQL SELECT statement to the RecordSource property of the subform, which causes the subform to display only the records that match the current record on the main form.

Steps to Reproduce the Behavior

  1. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  2. Create the following table in a Microsoft SQL Server database: 
       Table: MyCategories
   ----------------------------
   Field Name: CategoryID
   Data Type: UniqueIdentifier
   Allow Nulls: No (unchecked)
   IsRowGUID: Yes
   Default Value: (newid())

   Field Name: CategoryName
   Data Type: varchar
   Length: 50

   Table Properties: MyCategories
   ------------------------------
   PrimaryKey: CategoryID
    Save the table as MyCategories.Create the following table in a Microsoft SQL Server database:
       Table: MyProducts
   ---------------------------
   Field Name: ProductID
   Data Type: int
   Allow Nulls: No (unchecked)
   Identity: Yes

   Field Name: ProductName
   Data Type: varchar
   Length: 50

   Field Name: fCategoryID
   Data Type: UniqueIdentifier
   Allow Nulls: Yes (Checked)
   IsRowGUID: No
   Default Value: <blank>

   Table Properties: MyProduct
   ---------------------------
   PrimaryKey: ProductID
  3. Save the table as MyProducts.
  4. Open the sample database Northwind.mdb.
  5. Link the two tables that you just created by using an ODBC data source for your SQL Server.
  6. Create a new, blank form that is based on the dbo_MyProducts table.
  7. Add all the fields to the form.
  8. Set the DefaultView property of the form to Datasheet.
  9. Save the form as frmMyProducts, and then close it.
  10. Create a new, blank form that is based on the dbo_MyCategories table.
  11. Add all fields to the form.
  12. In the Database window, drag the frmMyProducts form onto the open form to create a subform.
  13. Click the subform object, and then on the View menu, click Properties.
  14. On the Data tab of the property sheet, set the following properties: 
       Subform: frmMyProducts
   -------------------------
   Link Child Fields: fCategoryID
   Link Master Fields: CategoryID
  15. Save the form as frmMyCategories.
  16. On the View menu, click Form View.
  17. Enter a new category record in the main form, and then add several new products in the subform.
  18. Close and then reopen the form.
Note that the main form displays the category record that you added; however, the subform does not display the related product records that you entered.

REFERENCES

For more information about the StringFromGUID method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type StringFromGUID method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbbug kbdta kbnofix KB295219
©2004 Microsoft Corporation. All rights reserved.