• Choose a category
• All
• Classic-Fiction

By Root 1822 0

name or ID. You'll want to make sure the fields you allow the user to search are indexed. The code determines which field the user wishes to search by using the IsNumeric function to test if the entered value is a number. If so, the code constructs criteria using the ID field of tblPeople. If the entered value is non-numeric, then the code assumes the user wishes to search on LastName. Again, we add a bit of flexibility by allowing the user to enter partial matches—the criteria string is constructed using the Like operator. Because this is a Text field, we must surround the value with quotes, so we use the acbcQuote constant that we defined earlier in the procedure. Finally, we have added "*" (an asterisk) before the closing quote to perform a pattern match search.

If you wish, you can simplify this code on your own form to use a single field. Either way, you'll need to change the references to ID and LastName to match the names of the fields (not the control names) in your form's record source. If you decide to allow a search on a date/time field, make sure you surround the date/time value with # (pound signs) instead of quotes.

With the criteria built, the SQL statement is easily created:

' Change the form's recordset based on criteria.

Me.RecordSource = "SELECT * FROM tblPeople WHERE " & varCriteria

Of course, you'll need to replace tblPeople with the name of the table or query on which your form is based.

The remaining code determines if any records were found:

' Now check the form's recordset to see if

' any records were found.

With Me.Recordset

If .EOF And .BOF Then

MsgBox "No matching record found.", _

vbOKOnly + vbCritical, "Goto Procedure"

End If

End With

This portion of code is not absolutely required, because Access will pull up the "new" record if no matching records are found. However, you might prefer to notify the user when no records were found. You can do this by using the form's Recordset property to return a recordset object that you can inspect. If the recordset is empty, Access sets both the end of file (EOF) and beginning of file (BOF) flags to True, so you can use this fact to test for the absence of records in the form's recordset.

A simple error handler is included in this procedure. It's important to include error-handling code in all multiuser procedures to handle the cases where records are locked. See Chapter 10 for more information on developing multiuser applications.

The one negative side to using this technique is that users may find it restrictive if they are used to navigating freely among records using the navigation controls at the bottom of the form. The sample form allows users to grab a subset of records from tblPeople by entering a partial match on LastName. If you also need to return groups of records when using numeric primary key field searches, you can use two text boxes to allow users to search for a range of primary key values, perhaps including code that limits the range to some arbitrary number.

The techniques presented in this solution apply equally to client/server applications.

See Also

Additional optimization strategies for client/server applications are discussed in the Solution in Recipe 8.8 and in Chapter 14.

8.8. Accelerate Client/Server Applications

Problem

You are using Access as a front end to linked tables stored in a client/server database. You're not satisfied with the response time of your client/server application. What can you do to make it run faster?

Solution

You can apply a variety of optimization techniques when developing client/server applications. If you are attaching remote tables in databases such as SQL Server or Oracle, you are accessing data through open database connectivity (ODBC) drivers. Typically, client/server applications using ODBC require more horsepower on the part of workstations and the network. By knowing how data is retrieved from the server, you can make your application run faster.

Another option is to create an Access Data Project (ADP). This is possible only if your data is stored