Finding a Record

We would like a general-purpose procedure for locating a record. In particular we want a simple way to key in a search string and find the record. Perhaps the telephone rings and we are caught off guard. "It's Joe Bloggs", we hear. Who is Joe Bloggs and why is he calling me? Do I know him? We want to be able to key in "Bloggs" and click a button so that Access can pull up the record while we are stalling for time: "Hi Joe, it's good to hear from you!" (grin)

Build a Command Button cmdFind with a caption "Find". We will use the Wizard to gain familiarity with the business of VBA code to find records.

In the category Record Navigation" select the action "Find Record", but note in passing that there is an action "Find Next". Nominate the text "Find" as a caption for the Command Button. We will call our Command Button cmd_Find.

If you examine the event code generated by the Wizard you will see this:

Private Sub cmd_Find_Click()
On Error GoTo Err_cmd_Find_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_cmd_Find_Click:
Exit Sub
Err_cmd_Find_Click:
MsgBox Err.Description
Resume Exit_cmd_Find_Click
End Sub

If you run the command, you will find that you are in the regular search dialogue box from Access, with a requirement that you confirm various options (Match case, Use current field etc.). We would like not to have to do this - we want a fast no-think search, so we will modify the VBA code appropriately.

In particular we will not be concerned with any particular field, so there is no need to set the focus on a particular field. That line ("Screen.PreviousControl.SetFocus") comes out.

Now we are going to use the Access/VBA help system. In VBE choose Help, and then "Contents And Index". In the text box under the Index tab, key in a search string "Find Record" as two words, but without the double-quotes. You will find yourself taken to the "Find Methods" section. Scroll down until you reach and can select the "FindRecord Action".

The text under that section will seem complicated, but read through it just once, then inspect the code line below:

DoCmd.FindRecord strFind, acAnywhere, False, acSearchAll, True, acAll,

This line asks Access to "Do" a command. Which command? The FindRecord command. Now let's go back and re-read the help screens. It is not so difficult after all.

The first parameter, which we have written as a string variable "strFind" will be a string of characters, the characters for which we are searching. In our example above, we would want the string to be "Bloggs", because we are searching for "Joe Bloggs"

The second parameter (I'm reading from the Help screens here) tells Access how to match this string with the fields of our record. "acAnywhere" means "anywhere in the field; we will be happy if Access find "Bloggs and Co." or "Mister Bloggs". We just want Access to find it!

The third parameter tells Access not to be fussy about the case. This is no time to be particular about "Bloggs" or "bloggs"; he is on the phone - just FIND his record!

The fourth parameter tells Access to search ALL records. We might be partway through examination of our Oldest or Current records, but when the telephone rings, we want to search All records.

The fifth parameter tells Access to search for data in the same manner in which it is formatted on the screen. For our text data this will be irrelevant, but for numeric or date data it could be significant. Suppose we have previously captured data and printed it, and now want to search for it; we will want to search in the same format as it was displayed at the time of capture.

Finally we specify that Access is to search all fields in each record. Again, please note that here we are implementing a "panic search" device; we want Access to find the data anywhere that is possible. It is not a very refined search at all.

At this point we have removed on line and changed one line; our incomplete procedure looks like this:

Private Sub cmd_Find_Click()
On Error GoTo Err_cmd_Find_Click
DoCmd.FindRecord strFind, acAnywhere, False, acSearchAll, True, acAll, True
Exit_cmd_Find_Click:
Exit Sub
Err_cmd_Find_Click:
MsgBox Err.Description
Resume Exit_cmd_Find_Click
End Sub

We need a device to obtain the characters for our Find string, and the little InputBox function satisfies our need:

	Dim strFind As String
strFind = Trim$(InputBox$("enter a Find string"))

We declare a string variable with the name "strFind". The prefix "str" does nothing for VBA, but it does alert human readers to the fact that we have declared the variable to be a string of characters.

We elect to Trim any superfluous spaces from the front and back of our string - we will drop leading and trailing spaces.

What string? The string returned by the InputBox function. This function takes a string of characters to be displayed as a prompt, pops up a small box inviting the user to key in a string, and when the user clicks on the OK button, the InputBox function delivers the keyed-in string to our program. We Trim the result, and assign it to our string variable.

At this point our Find procedure code is complete:

Private Sub cmd_Find_Click()
On Error GoTo Err_cmd_Find_Click
Dim strFind As String
strFind = Trim$(InputBox$("enter a Find string"))
DoCmd.FindRecord strFind, acAnywhere, False, acSearchAll, True, acAll, True
Exit_cmd_Find_Click:
Exit Sub
Err_cmd_Find_Click:
MsgBox Err.Description
Resume Exit_cmd_Find_Click
End Sub

Experiment with the Find Command Button. It will locate any record that contains your volunteered string anywhere within one field. It will, however, stop at the first record that it finds.

In the next lesson we will create a Command Button to advance to the Next found record (in case we have more than one record containing "Bloggs").

Search