ACC2000: Parameter Query Returns No Records with Replication ID Field (210612)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210612
This article applies only to a Microsoft Access database (.mdb).

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

For a Microsoft Access 2002 version of this article, see 291958.

SYMPTOMS

When you supply a GUID value as a criteria in a query field that is based on an AutoNumber field that is set to a Replication ID, no fields are returned. You might see this if you reference a form field that contains the Replication ID in the Criteria row of the query.

CAUSE

The Datasheet view of a table/query that contains a field of data type AutoNumber (with its FieldSize property set to ReplicationID) is returning/displaying the ReplicationID as type String, instead of a GUID (Byte array).

RESOLUTION

You can return the correct records by converting the GUID being supplied as a criteria to a string. The following demonstrates how to use the Parameters dialog box to force a form field reference to be converted to a string in the query criteria.
  1. Complete the steps in the "Steps to Reproduce Problem" section in the "More Information" section.
  2. Open the Query1 query in Design view.
  3. On the Query menu, click Parameters, and in the Parameter box, type the following:

    [Forms]![Form1]![Field1]

  4. In the Data Type box, enter Text, and then click OK.
  5. With the Form1 form open and displaying the first record, run the Query1 query.

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 reference to a parameter (for example, a form's control) is returning the actual Byte array (GUID) value of the Replication ID instead of the structured text format of the GUID.

Steps to Reproduce Problem

  1. Open the sample database Northwind.mdb.
  2. Create the following new table:

    Field NameData TypeField SizeIndexed
    Field1AutoNumberReplication IDNo
    Field2Text50No
  3. Close and save the table as Table1. Click No when prompted to create a primary key.
  4. Use the Autoform: Columnar Wizard to create a new form based on the Table1 table.
  5. Type some text in Field2 so that a new Replication ID is generated for this first record.
  6. On the Records menu, click Save Record (stay on this new record; do not move to a new, blank record).
  7. Save the form as Form1, but do not close the form.
  8. Create a new query based on the Table1 table, setting it up as follows:

    Field:Field1Field2
    Table:Table1Table1
    Criteria:[Forms]![Form1]![Field1]
  9. Save the query as Query1, and then run the query. Note that no records are returned, even though the form's current record contains a Replication ID that exists in the table.

REFERENCES

For more information about Replication ID AutoNumbers, click Microsoft Access Help on the Help menu, type Which type of AutoNumber field should I create? in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/21/2002
Keywords:kbbug KB210612
©2002 Microsoft Corporation. All rights reserved.