I.2. Find Records

Access provides a way to search for records, but it leaves a lot to be desired. The binoculars button pops up a dialog box with several search options, but most users don't know how to use it properly. The problem is that it is has too many options, when your user probably just wants to find a record containing a certain text value.

Instead of the dreaded binoculars, you can include a quick and easy way for your user to find records. Figure I-4 shows a form with a built-in technique to find records.

This record finding technique allows any phrase to be entered in the text box, then finds the first (or next) record that contains that phrase anywhere in the displayed fields. Alternatively, the user may use the radio buttons to switch to an exact match instead of a contains search, where the entire field must match the phrase. This exact mode is not used very often, but it can be handy in searching for exact codes or numbers (such as membership or account numbers).

I.2.1. Call ng the Record Finder Code

The On Click event of the Find button (shown in Figure I-4) includes this code:

Private Sub cmdFirst_Click()
On Error GoTo Error_Handler

    FindRecordLike "first"

Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell " _
    & "them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
    & Err.Description, _
    Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume

End Sub

This code really has only one operative line:

FindRecordLike "first"

The code behind cmdNext_Click() is almost identical. Instead of using "first" as the parameter for FindRecordLike, it sends in "next":

FindRecordLike "next"

Now, let's take a look at the code for the subroutine FindRecordLike. This code also resides in the same index form that contains the record finder controls (shown in Figure I-3). It looks like this:

Private Sub FindRecordLike(strFindMode As String)
On Error GoTo Error_Handler

Call ww_FindRecord(frmCallingForm:=Me, _
        ctlFindFirst:=Me!cmdFirst, _
        ctlFindNext:=Me!cmdNext, _
        ctlSearchText:=Me!txtFind, _
        ctlSearchOption:=Me!optFind, _
        strFindMode:=strFindMode, _
        strField1:="BusinessName", _
        strField2:="LastName", _
        strField3:="FirstName", _
        strField4:="City")

Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell them this
information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " &
Err.Description, _
    Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume
End Sub

This Sub accepts a parameter strFindMode of "first" or "next", which it passes directly on to the ww_FindRecord procedure. In fact, pretty much all this procedure does is call ww_FindRecord. The interesting part is the set of parameters that is passed to ww_FindRecord, many of which are explained the next section. However, we'll first take a look at the parameters strField1, strField2, strField3, and strField4.

In order for the record finder routine to know which fields to search for our user's phrase, we need to make it known. We can do this by sending in up to 10 field names. These must be names of fields that appear in the Recordsource for the form. Although they don't technically have to appear on the form itself, it will seem strange to the user to find records containing a phrase that they can't see.

In this example, we are only sending in four field names to be searched, but we could have specified up to 10. Fields 2 through 10 are optional parameters, and are explained later in this chapter.

I.2.2. Record Finder Code

In order to make this code easy to implement, we want to reuse as much code as possible. The key to this technique is to pass references to the controls on this form (the text box, buttons, even the form itself) to a reusable Record Finder function. Note that this code has a "ww" prefix (for Wiley-Wrox) to reduce conflicts with any other public procedures.

Option Compare Database
Option Explicit

'Record Finder

'Accepts references from a continuous form with Record Finder
'controls, finds the first/next record containing the search
'text in one of the passed-in field names, and repositions the
'form to that record.

Public Function ww_FindRecord(frmCallingForm As Form, _
        ctlFindFirst As Control, _
        ctlFindNext As Control, _
        ctlSearchText As Control, _
        ctlSearchOption As Control, _
        strFindMode As String, _
        strField1 As String, _
        Optional strField2 As String, _
        Optional strField3 As String, _
        Optional strField4 As String, _
        Optional strField5 As String, _
        Optional strField6 As String, _

Optional strField7 As String, _
        Optional strField8 As String, _
        Optional strField9 As String, _
        Optional strField10 As String)

On Error GoTo Error_Handler

Dim recClone As Recordset
Dim intBookmark As String
Dim strAllFields As String
Dim strSelection As String

' Field delimiter is used to separate concatenated
' field values below. This prevents text from being
' matched across adjacent fields. It may be changed
' to any text value that is unlikely to appear in the fields.
Const FIELDDELIMITER = "@%%@"

' If there is no string to search for set the focus back to
' the text box.
If ctlSearchText & "" = "" Or strField1 & "" = "" Then
    ctlSearchText.SetFocus
    Exit Function
End If

DoCmd.Hourglass True

ww_FindRecord = False

This next section of code handles the search if the user has specified the Contains mode. This is the default search and the most flexible, as it will find the phrase anywhere in any of the specified fields.

'Test search option
If ctlSearchOption = 1 Then 'Contains search

    ' build string to concatenate all fields together
    strAllFields = "[" & strField1 & "]"
    If strField2 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER & _
        """ & [" & strField2 & "]"
    End If
    If strField3 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER & _
        """ & [" & strField3 & "]"
    End If
    If strField4 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER & _
        """ & [" & strField4 & "]"
    End If
    If strField5 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER & _
        """ & [" & strField5 & "]"
    End If

If strField6 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER &
        """ & [" & strField6 & "]"
    End If
    If strField7 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER &
        """ & [" & strField7 & "]"
    End If
    If strField8 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER &
        """ & [" & strField8 & "]"
    End If
    If strField9 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER &
        """ & [" & strField9 & "]"
    End If
    If strField10 <> "" Then
        strAllFields = strAllFields & " & """ & FIELDDELIMITER &
        """ & [" & strField10 & "]"
    End If

    Set recClone = frmCallingForm.RecordsetClone

    ' if find First button was used
    If strFindMode = "first" Then
        recClone.FindFirst strAllFields & " Like ""*" & _
        Replace(ctlSearchText, """", """""") & "*"""
        If recClone.NoMatch Then
            MsgBox "No matches found.", vbOKOnly, "Record Finder"
            ctlSearchText.SetFocus
        Else
            frmCallingForm.Bookmark = recClone.Bookmark
            ctlFindNext.SetFocus
            ww_FindRecord = True
        End If
    Else
        ' if find Next button was used
        If strFindMode = "next" Then
            recClone.Bookmark = frmCallingForm.Bookmark
            recClone.FindNext strAllFields & " Like ""*" & _
            Replace(ctlSearchText, """", """""") & "*"""
            If recClone.NoMatch Then
                MsgBox "No more matches found.", vbOKOnly, _
                "Record Finder"
                ctlFindFirst.SetFocus
            Else
                frmCallingForm.Bookmark = recClone.Bookmark
                ctlFindNext.SetFocus
                ww_FindRecord = True
            End If

        End If
    End If
Else

Now, we move on to the search if the user specified Exact mode. This mode checks the exact contents of each of the specified fields to see if they equal the user's search phrase.

'ctlSearchOption = 2 'Exact Search

    strSelection = "CStr(" & strField1 & " & """") = """ &
    Replace(ctlSearchText, """", """""") & """"
    If strField2 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField2 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField3 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField3 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField4 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField4 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField5 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField5 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField6 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField6 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField7 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField7 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField8 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField8 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField9 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField9 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If
    If strField10 <> "" Then
        strSelection = strSelection & _
        " OR CStr(" & strField10 & " & """") = """ & _
        Replace(ctlSearchText, """", """""") & """"
    End If

    Set recClone = frmCallingForm.RecordsetClone

If strFindMode = "first" Then
        recClone.FindFirst strSelection
        If recClone.NoMatch Then
            MsgBox "No matches found.", vbOKOnly, "Record Finder"
            ctlSearchText.SetFocus
        Else
            frmCallingForm.Bookmark = recClone.Bookmark
            ctlFindNext.SetFocus
            ww_FindRecord = True
        End If
    Else
        ' if find Next button was used
        If strFindMode = "next" Then
            recClone.Bookmark = frmCallingForm.Bookmark
            recClone.FindNext strSelection
            If recClone.NoMatch Then
                MsgBox "No more matches found.", vbOKOnly, _
                "Record Finder"
                ctlFindFirst.SetFocus
            Else
                frmCallingForm.Bookmark = recClone.Bookmark
                ctlFindNext.SetFocus
                ww_FindRecord = True
            End If

        End If
    End If
End If

DoCmd.Hourglass False

Exit_Procedure:
    Exit Function

Error_Handler:
    DoCmd.Hourglass False
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell " _
    & "them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
    & Err.Description, Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume
End Function

There are several techniques in this code that are worth a closer look. They are explained in the next few sections.

I.2.2.1. Passing Control and Form References to a Function

This function does not reside behind any form, but rather in a separate module. In order for it to be usable from any index form, it needs to be able to interact with that form. To do so, we pass object references from our form in addition to actual parameter values. These reference parameters are:

• frmCallingForm (the actual form itself, to build a RecordsetClone and to set the Bookmark)

• ctlFindFirst (the First button, to set focus)

• ctlFindNext (to Next button, to set focus)

• ctlSearchText (the text box with the search phrase, to use for searching and to set focus)

• ctlSearchOption (the radio button group of Exact/Contains, to determine which kind of search to perform)

The key thing to remember here is that these are not values themselves; they are pointers to the controls on the original form, so they give us direct access to those controls as if this code were in that form itself.

The rest of the parameters are values, such as the Find mode (first or next matching record) and the names of up to 10 fields to search.

I.2.2.2. Optional Parameters

This function uses optional parameters, meaning that when you call this function, you can choose whether or not to specify them. In this case, the optional parameters are strField2 through strField10. Optional parameters are very useful in this case, because they give you the flexibility of specifying any number of fields to search, from 1 to 10. If we didn't use optional parameters in this procedure, we would have to specify all 10 field names every time we called it, sending in empty strings for the ones we didn't need.

I.2.2.3. Using the RecordsetClone

A RecordsetClone is a recordset based on a form's recordset, but with full search capabilities and a different record cursor. We use a RecordsetClone of the form to find a matching record, and then use its Bookmark property to position the form to the matching record. We do all this with the passed in Form reference:

Set recClone = frmCallingForm.RecordsetClone
frmCallingForm.Bookmark = recClone.Bookmark

Remember, the form reference was passed in as a parameter to this procedure, so the RecordsetClone being searched is the same as the recordset currently displayed on the index form that called our record finder procedure.

I.2.2.4. Searching Multiple Fields Using Concatenation

In order to search multiple fields, this function takes a different approach than you might be familiar with. Instead of building a complex SQL string that searches each specified field in the recordset with OR statements, it concatenates all the desired fields from the recordset into one large string (strAllFields). Then it searches this large text field for the search phrase.

There's one problem with this approach, however. The search phrase may be discovered using the end of one field and the beginning of another. For example, if the fields for City and State are concatenated together, they may look like this:

SeattleWA

If your user searches for the phrase "lew", this record will be found, even though "lew" doesn't appear in any one field.

To avoid this problem, we use the FIELDDELIMETER constant. It is set to "@%%@", a value that's unlikely to occur in any search phrase. When we concatenate the desired fields together into the one big field, they are separated by this delimiter value, like this:

Seattle@%%@WA

By doing this, we separate the two words and prevent a search for "lew" from finding this record.

I.2.2.5. Handling Quotes in the Search Phrase

There's a potential problem when the user types a quote (") in the search phrase, as shown in Figure I-5.

Since we build strings using quotes in the code, these extra quotes supplied by the user can cause errors. To guard against this, we need to replace each quote in the user's search phrase with two quotes (there'sa full discussion of string handling techniques in Chapter 14, "SQL and VBA").

To perform the quote replacement, we use the VBA Replace function in this rather strange-looking code:

Replace(ctlSearchText, """", """""")

This takes every instance of a quote (") in ctlSearchText and replaces it with two quotes (""). Then, when we build search strings with it, those doubled-up quotes will "collapse" back into solo quotes. It's weird, but it works.

I.2.2.6. Setting Focus from Afar

To help the user use the Find routine efficiently, we control the focus so that keystrokes make sense. For example, if the user clicks the First button and no match is found, we know that they are probably going to want to change the search phrase to something else. To help them in this regard, we set the focus to the Search Text control.

If recClone.NoMatch Then
    MsgBox "No matches found.", vbOKOnly, "Record Finder"
    ctlSearchText.SetFocus

Remember, this code is not in the form the user is viewing—we are controlling focus from this procedure using a reference to the control on the index form that called ww_FindRecord.

Similarly, in order to facilitate cycling through all the matching records, we set focus to the Next button when a record is found, and to the First button after no more records are found. This allows the user to continue to press the Enter key on the keyboard to repeatedly loop through all the matching records. This sort of convenience feature adds a lot of polish to your applications.