ACC95: Using FindFirst Method to Find a GUID Generates an Error

• Microsoft Access version 7.0

SYMPTOMS

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

When you use the FindFirst method of a recordset to find a globally unique identifier (GUID), you may receive one of the following error messages:

   Malformed GUID in expression

   -or-

   GUID not allowed in Find method criteria expression

CAUSE

A GUID is a 16-byte array. In table Datasheet view, a GUID appears as a string of hexadecimal numbers enclosed in braces. The value of an unbound combo box whose bound column is a GUID will be a string of hexadecimal numbers enclosed by braces. The FindFirst method will not be able to find a record with a GUID equal to that string.

RESOLUTION

Create an AfterUpdate event procedure that moves through the form's recordsetclone to find the record with the GUID that matches the value in the combo box. Set the bookmark of the form to the bookmark of the recordsetclone.

The following example shows you how to find a record on a form using a combo box whose bound column is a GUID:

1. Open the sample database Northwind.mdb.

2. Copy the Employees table and paste it as Employees1.

3. Open the Employees1 table in Design view.

4. Change the EmployeeID Field Size property to Replication ID and save the table.

5. Create a new form based on the Employees1 table using the AutoForm: Columnar wizard. Save the form as Employees1.

6. Open the Employees1 form in Design view.

7. Create an unbound combo box on the form with the following properties:

       Combo box:  GUIDFind
       ---------------------
       Name: GUIDFind
       RowSource: Employees1
       ColumnCount: 2
       ColumnWidths: 0";1"
   

8. Set the AfterUpdate property of the combo box to the following event procedure:

         Private Sub GUIDFind_AfterUpdate()
            Dim rs as Recordset
            Dim findguid as String
            findguid = "{guid " & Me!GUIDFind & "}"
            Set rs = Me.RecordsetClone
            rs.MoveFirst
            Do Until rs.EOF
               If rs!EmployeeID = findguid Then
                  Me.Bookmark = rs.Bookmark
                  Exit Sub
               End If
               rs.MoveNext
            Loop
         End Sub
   
   

9. Open the form in Form view. Select a value from the combo box and notice that the form displays the correct record.

MORE INFORMATION

Another way to represent a GUID is to convert it to a string using the StringFromGUID() function.

For example, if you print the value of a text box bound to a GUID in the Debug Window, you see a series of question marks because Microsoft Access 7.0 cannot display the 16-byte array. However, if you convert the value with the StringFromGUID() function, you see a string of hex numbers preceded by the word guid and enclosed by another set of curly braces. For example:

   debug.print StringFromGUID(Forms!formname!textboxname)

   {guid {3B9B63A3-863D-11CF-8CAE-00AA00C0016B}}

   GUID not allowed in Find method criteria expression

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Copy the Employees table and paste it as Employees1.

3. Open the Employees1 table in Design view.

4. Change the EmployeeID Field Size property to Replication ID and save the table.

5. Create a new form based on the Employees1 table using the AutoForm: Columnar wizard. Save the form as Employees1.

6. Open the Employees1 form in Design view.

7. Use the Combo Box Wizard to create a combo box on the form.

8. In the How do you want the combo box to get its values? dialog box, click Find a record on my form based on the value I selected in my combo box, then click Next.

9. In the Which fields contain the values you want included in your combo box? dialog box, add EmployeeID and LastName to the Selected Fields column, then click Next.

10. In the How wide would you like the columns in your combo box? dialog box, click Finish.

11. Switch to Form view and select a value from the combo box list. You receive the following error:

    Malformed GUID in expression

REFERENCES

For more information about globally unique identifiers, search for "GUIDs," using the Microsoft Access 7.0 Help Index.