Microsoft Access: Filter a form based on a GUID key value

Platform:Microsoft Access
Task:Filter a form based on a GUID key value
Discussion:
If you use GUIDs as a primary key in a table you may run into difficulties when searching and filtering forms. The Access listbox wizard will ask you if you want to find a record in the form based on the listbox value and then develop some code that doesn't work. The problem is that the str() function does not work with GUIDs.
Example:
'the non-functional code that the wizard produces. List36.Value is a GUID
Private Sub List36_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.Find "[PONDID] = " & Str(Nz(Me![List36], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

'remove the Str() and voila, it works
Private Sub List36_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.Find "[PONDID] = " & Nz(Me![List36], 0)
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Back to Index