Most
applications that are distributed to users include at least some
Visual Basic for Applications (VBA) code. Because VBA 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.
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?
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.
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.
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 think you were trying to numerically divide 5 by 16, and then by 56.
Numeric values require no delimiters. If you were searching for the row in which the ID value was 8, you could use this expression:
[ID] = 8
and Access would know exactly what you meant.
Many situations in Access require that you create strings that supply search criteria. Because the Jet database engine has no knowledge of Access Basic or its variables, you must supply the actual values before you apply any search criteria or perform lookups. That is, you must create a string expression that contains the value of any variable involved, not the variable name.
Any of the three examples in this section could have been used as search criteria, and string values would need to have been surrounded by quotes. The next few paragraphs cover the steps you need to take in creating these search criteria strings.
To build expressions
that involve variables, you must supply any required delimiters. For
numeric expressions, there is no required delimiter. If the variable
named intID
contains the value 8, you
could use this expression to create the search string you need:
"[ID] = " & intID
As part of a SQL string, or as a parameter to DLookup, this string is unambiguous in its directions to Access.
To create a search criterion that includes
a date variable, you’ll need to include the
#
delimiters. For example, if you have a variant
variable named varDate
that contains the
date May 22, 1959, and you want to end up with this expression:
"[BirthDate] = #5/22/59#"
you have to insert the delimiters yourself. The solution might look like this:
"[BirthDate] = #" & varDate & "#"
The complex case occurs when you must include strings. For those cases, you’ll need to build a string expression that contains a string itself, surrounded by quotes, with the whole expression also surrounded by quotes. The rules for working with strings in Access are as follows:
An expression that’s delimited with quotes can’t itself contain quotes.
Two quotes (""
) inside a string are seen by Access
as a single quote.
An expression that’s delimited with apostrophes can’t itself contain apostrophes.
You can use the value of
Chr$(34)
(34 is the ANSI value for the quote
character) inside a string expression to represent the quote
character.
Given these rules, you can
create a number of solutions to the same problem. For example, if the
variable strLastName
contains
“Smith”, and you want to create a
WHERE
clause that will search for that name, you
will end up with this expression:
"[LastName] = "Smith""
However, that expression isn’t allowed because it includes internal quotes. An acceptable solution would be the following:
"[LastName] = ""Smith"""
The problem here is that the literal value “Smith” is
still in the expression. You’re trying to replace that value
with the name of the variable,
strLastName
. You might try this
expression:
"[LastName] = ""strLastName"""
but that will search for a row with the last name of “strLastName”. You probably won’t find a match.
One solution, then, is to break up that expression into three separate pieces—the portion before the variable, the variable, and the portion after the variable (the final quote):
"[LastName] = """ & strLastName & """"
Although that may look confusing, it’s correct. The first portion:
"[LastName] = """
is simply a string containing the name of the field, an equals sign,
and two quotes. The rule is that two quotes inside a string are
treated as one. The same logic works for the portion of the
expression after the variable (""""
). That’s
a string containing two quotes, which Access sees as one quote.
Although this solution works, it’s a bit confusing.
To make things simpler, you can just use apostrophes inside the string:
"[LastName] = '" & strLastName & "'"
This is somewhat less confusing, but there’s a serious drawback: if the name itself contains an apostrophe (“O’Connor”, for example), you’ll be in trouble. Access doesn’t allow you to nest apostrophes inside apostrophe delimiters, either. This solution works only when you’re assured that the data in the variable can never itself include an apostrophe.
The simplest solution is to use
Chr$(34)
to embed the quotes. An expression such
as the following would do the trick:
"[LastName] = " & Chr$(34) & strLastName & Chr$(34)
If you don’t believe this works, go to the debug window in Access and type this:
? Chr$(34)
Access will return to you by typing the value of
Chr$(34)
—a quote character.
To make this solution a little simpler, you could create a string
variable at the beginning of your procedure and assign to it the
value of Chr$(34)
:
Dim strQuote As String Dim strLookup As String strQuote = Chr$(34) strLookup = "[LastName] = " & strQuote & strLastName & strQuote
This actually makes the code almost readable!
Finally, if you grow weary of defining that variable in every procedure you write, you might consider using a constant instead. You might be tempted to try this:
Const QUOTE = Chr$(34)
Unfortunately, Access won’t allow you to create a constant whose value is an expression. If you want to use a constant, your answer is to rely on the “two-quote” rule:
Const QUOTE = """"
Although this expression’s use is not immediately clear, it works just fine. The constant is two quotes (which Access will see as a single quote) inside a quoted string. Using this constant, the previous expression becomes:
strLookup = "[LastName] = " & QUOTE & strLastName & QUOTE
To encapsulate all these rules, you might want to use the
acbFixUp function in the basFixUpValue module in
07-01.MDB
. This function takes as a parameter a
variant value and surrounds it with the appropriate delimiters. Its
source code is:
Function acbFixUp(ByVal varValue As Variant) As Variant ' Add the appropriate delimiters, depending on the data type. ' Put quotes around text, #s around dates, and nothing ' around numeric values. ' If you're using equality in your expression, you should ' use Basic's BuildCriteria function instead of calling ' this function. Const QUOTE = """" Select Case VarType(varValue) Case vbInteger, vbSingle, vbDouble, vbLong, vbCurrency acbFixUp = CStr(varValue) Case vbString acbFixUp = QUOTE & varValue & QUOTE Case vbDate acbFixUp = "#" & varValue & "#" Case Else acbFixUp = Null End Select End Function
Once you’ve included this function in your own application, you can call it, rather than formatting the data yourself. The sample code in frmQuoteTest uses this function. For example, here’s how to build the expression from the previous example:
"[LastName] = " & FixUp(strLastName)
abcFixUp will do the work of figuring out the data type and surrounding the data with the necessary delimiters.
Access also provides a useful function, BuildCriteria, that will accept a field name, a data type, and a field value and will create an expression of this sort:
FieldName = "FieldValue"
with the appropriate delimiters, depending on the data type. We’ve used this in our example in the case where you uncheck the Use Like checkbox. It won’t help if you want an expression that uses wildcards, but if you’re looking for an exact match, it does most of the work of inserting the correct delimiters for you. To study the example, look at the BuildWhere function in frmQuoteTest’s module.
18.118.210.133