Microsoft KB Archive/295219

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

Article ID: 295219

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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.  First, follow the steps in the &quot;Steps to Reproduce the Behavior&quot; section later in this article to reproduce the problem. Then, open the frmMyCategories form in Design view. Click the subform object, and then on the View menu, click Properties. On the Data tab of the property sheet, clear the LinkChildFields and LinkMasterFields properties. On the View menu, click Code to view the module of the form.  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 = &quot;SELECT * FROM dbo_MyProducts WHERE &quot; & _ &quot;dbo_MyProducts.fCategoryID = &quot; & _ StringFromGUID(Me.Controls(&quot;CategoryID&quot;).Value) Else strSQL = &quot;SELECT * FROM dbo_MyProducts WHERE False&quot; End If   Me.Controls(&quot;dbo_MyProducts&quot;).Form.RecordSource = strSQL End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> Save the frmMyCategories form, and then close it.</li> Open the frmMyProducts form in Design view.</li> On the View menu, click Code to view the module of the form.</li>  Add the following code to the BeforeInsert event procedure of the form: Private Sub Form_BeforeInsert(Cancel As Integer) Me.Controls(&quot;fCategoryID&quot;).Value = _ Me.Parent.Controls(&quot;CategoryID&quot;).Value End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> Save the frmMyProducts form, and then close it.</li> Open the frmMyCategories form in Form view.</li> Enter a new category name in the main form, and then add several new products in the subform.</li> Close and then reopen the form.</li></ol>

Note that Microsoft Access correctly displays the related records in the subform based on the current record in the main form.

<div class="status_section">

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

<div class="moreinformation_section">

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

</li>  Create the following table in a Microsoft SQL Server database: <pre class="fixed_text">  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: <pre class="fixed_text">  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:

Table Properties: MyProduct ---  PrimaryKey: ProductID </li> Save the table as MyProducts .</li> Open the sample database Northwind.mdb.</li> Link the two tables that you just created by using an ODBC data source for your SQL Server.</li> <li>Create a new, blank form that is based on the dbo_MyProducts table.</li> <li>Add all the fields to the form.</li> <li>Set the DefaultView property of the form to Datasheet.</li> <li>Save the form as frmMyProducts, and then close it.</li> <li>Create a new, blank form that is based on the dbo_MyCategories table.</li> <li>Add all fields to the form.</li> <li>In the Database window, drag the frmMyProducts form onto the open form to create a subform.</li> <li>Click the subform object, and then on the View menu, click Properties.</li> <li> On the Data tab of the property sheet, set the following properties: <pre class="fixed_text">  Subform: frmMyProducts -  Link Child Fields: fCategoryID Link Master Fields: CategoryID </li> <li>Save the form as frmMyCategories .</li> <li>On the View menu, click Form View.</li> <li>Enter a new category record in the main form, and then add several new products in the subform.</li> <li>Close and then reopen the form.</li></ol>

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.

<div class="references_section">