Access Cookbook - Ken Getz [124]
Discussion
The NewRecord property is simple: its value is True when adding a new record and False otherwise. This property is True from the moment the pending new record becomes current until the moment the record is saved. NewRecord is reset to False right after the BeforeUpdate event; it is False during both the AfterUpdate and AfterInsert events.
The image control used to display the add/edit icon uses a trick to change its picture quickly. Rather than loading a bitmap image from a disk file, which would be slow, it copies the picture from one of two hidden "source" image controls on the form.
To do this, set the image control's PictureData property to the value of the PictureData property of another image control. Chapter 9 discusses the PictureData property in more detail.
6.3. Find All Records with Names That Sound Alike
Problem
You enter people's names into a table in which misspellings are a common occurrence. You would like a way to search for a person's record disregarding slight differences in spelling. You've tried using the Like operator with the first letter of the person's last name, but that produces too many names. Is there any way to search for records that sound alike?
Solution
Access has no built-in sound-alike function, but you can create one that employs a standard algorithm called the Russell Soundex algorithm. Using this algorithm, it's fairly easy to search for a last name phonetically.
Run the qrySoundex query found in 06-03.MDB. Enter a last name in the query parameter dialog, and qrySoundex will return all records from tblStaff that sound like the name you entered. For example, if you enter the name "Jahnsin" at the parameter prompt, qrySoundex will return the records shown in Figure 6-5.
Figure 6-5. The records returned by searching for "Jahnsin"
To perform Soundex searches in your own applications, follow these steps:
Import the basSoundex module from 06-03.MDB into your database.
Create a query based on a table that contains a field that holds people's last names. Include the LastName field and any additional fields you wish to see in the output of the query.
Create a calculated field that calculates the Soundex code for the LastName field using the acbSoundex function. In qrySoundex, we used the following calculation to create a new field called Soundex:
Soundex: acbSoundex([LastName])
Enter criteria for the calculated field that compare that field against the Soundex code of a user-entered parameter. Use the acbSoundex function to obtain the Soundex code of the parameter. We used the following criteria in qrySoundex:
acbSoundex([Enter Last Name])
This qrySoundex query is shown in Figure 6-6.
Figure 6-6. The qrySoundex query in design view
Declare the parameter to be of type Text using the Query → Parameters dialog.
Save and run the query.
Discussion
You can find the acbSoundex function in basSoundex in 06-03.MDB. This function takes a last name and returns a four-digit Soundex code for the name. If you look at the fourth column in Figure 6-5, you can see that the Soundex code for all rows is the same. In this case—for names sounding like "Jahnsin"—the code is "J525". Soundex codes always begin with the first letter of the name followed by three digits ranging between 0 and 6 that represent the remaining significant consonants in the name.
The acbSoundex function is shown here:
Public Function acbSoundex( _
ByVal varSurName As Variant) As Variant
' Purpose:
' Takes a surname string and returns a 4-digit
' code representing the Russell Soundex code.
' In:
' varSurName: A surname (last name) as a variant
' Out:
' Return value: A 4-digit Soundex code as a variant
Const acbcSoundexLength = 4
On Error GoTo HandleErr
Dim intLength As Integer
Dim intCharCount As Integer
Dim intSdxCount As Integer
Dim intSeparator As Integer
Dim intSdxCode As Integer
Dim intPrvCode As Integer
Dim strChar As String * 1
Dim strSdx As String * acbcSoundexLength
Dim strName As String
' We add vbNullString to take