• Choose a category
• All
• Classic-Fiction

By Root 2054 0

provides the only mechanism for performing certain tasks (for example, using variables, building SQL strings on the fly, handling errors, and using the Windows API), most developers eventually must delve into its intricacies. The sections in this chapter cover some of the details of VBA that you might not find in the Access manuals. First you'll find a complete explanation of embedding strings inside other strings, allowing you to build SQL strings and other expressions that require embedded values. Two solutions are devoted to creating a procedure stack, which allows you to keep track of the current procedure at all times. The second of the two also creates a profiling log file, which helps you document where and for how long your code wandered. Next you'll learn about the DoEvents statement, which gives Windows time to handle its own chores while your code is running. A group of four solutions covers the details of creating list-filling functions, passing arrays as parameters, sorting arrays, and filling a list box with the results of a directory search. The final two solutions cover some details of working with Data Access Objects (DAO): how to set and retrieve object properties, whether the properties are built-in, and how to tell whether an object exists in your application.

7.1. Build Up String References with Embedded Quotes

Problem

You want to create criteria for text and data fields, but no matter what syntax you try you seem to get errors or incorrect results. What are you doing wrong?

Solution

You'll face this problem in any place in Access where you're required to provide a string expression that contains other strings—for example, in using the domain functions (DLookup, DMax, DMin, etc.), in building a SQL expression on the fly, or in using the Find methods (FindFirst, FindNext, FindPrevious, and FindLast) on a recordset. Because all strings must be surrounded with quotes, and you can't embed quotes inside a quoted string, you can quickly find yourself in trouble. Many programmers agonize over these constructs, but the situation needn't be that difficult. This section explains the problem and shows you a generic solution.

To see an example of building expressions on the fly, load and run frmQuoteTest from 07-01.MDB. This form, shown in Figure 7-1, allows you to specify criteria. Once you press the Search button, the code attached to the button will build the SQL expression shown in the text box and will set the RowSource property for the list box at the bottom of the form accordingly.

Figure 7-1. The test form, frmQuoteTest, with a subset of the data selected

To try all the features of the form, follow these three steps:

In the First Name text box, enter A. When you press Return, the form builds the appropriate SQL string and filters the list box. Note in the SQL string that the value you entered is surrounded by quotes. (This is the state in which Figure 7-1 was captured.)

In the Birth Date text box, enter 3/13/60. Again, the form should filter the data (down to a single row). Note that the SQL expression must have "#" signs around the date value you entered.

Press the Reset button to delete all the data from the four text boxes. That will again fill the list box with all the rows. Enter the value 8 in the ID text box, and then press Return. Note that the SQL string this time has no delimiter around the value that you entered.

Discussion

The point of that exercise was to alert you to the fact that different data types require specific delimiters when they become part of an expression. For example, to use DLookup to find the row in which the [LastName] field was Smith, you'd need an expression like this:

[LastName] = "Smith"

Leaving off those quotes would confuse Access, because it would be looking for some variable named "Smith".

Date values don't require quotes. Instead, they require # delimiters. To find the row in which the [BirthDate] field is May 16, 1956, you'd need an expression like this:

[BirthDate] = #5/16/56#

If you left off the delimiters, Access would