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

SYMPTOMS

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

If you use the Combo Box Wizard on a form to create a combo box that finds a record based on a replication ID value, you may also receive this error. This is because the wizard generates Visual Basic for Applications code that uses the FindFirst method.

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:

Open the sample database Northwind.mdb.
Copy the Employees table and paste it as Employees1.
Open the Employees1 table in Design view.
Change the EmployeeID Field Size property to Replication ID and save the table.
Create a new form based on the Employees1 table using the AutoForm: Columnar wizard. Save the form as Employees1.
Open the Employees1 form in Design view.

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

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

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

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)

displays something like this:

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

The FindFirst method also does not work with strings like this. You cannot use the StringFromGUID() function with an unbound combo box value because the value is already a string expression. If you concatenate "{guid " to the beginning of the combo box value and you concatenate "}" to the end of the value, then you receive the following error message when you use the FindFirst method:

   GUID not allowed in Find method criteria expression

You must use an AfterUpdate event procedure as described earlier.

Steps to Reproduce Behavior

Open the sample database Northwind.mdb.
Copy the Employees table and paste it as Employees1.
Open the Employees1 table in Design view.
Change the EmployeeID Field Size property to Replication ID and save the table.
Create a new form based on the Employees1 table using the AutoForm: Columnar wizard. Save the form as Employees1.
Open the Employees1 form in Design view.
Use the Combo Box Wizard to create a combo box on the form.
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.
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.
In the How wide would you like the columns in your combo box? dialog box, click Finish.
Switch to Form view and select a value from the combo box list. You receive the following error:
Malformed GUID in expression