VBA Details

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.

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?

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:

1. 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.)
   
2. 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.
   
3. 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, youd 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 VBA 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 intI D  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:

1. An expression that's delimited with quotes can't itself contain quotes.
   
2. Two quotes ("") inside a string are seen by Access as a single quote.
   
3. You can use apostrophes (') as string delimiters.
   
4. An expression that's delimited with apostrophes can't itself contain apostrophes.
   
5. 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 Immediate window in VBA 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, heres 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 frmQuoteTests module.

7.2 Create a Global Procedure Stack

Problem

When you're writing an application, you often need to know the name of the current procedure from within your code. For example, if an error occurs, you'd like to be able to have a generic function handle the error and display the name of the procedure in which the error occurred (and all the procedures that have been called on the way to get there). VBA doesn't include a way to retrieve this information. How can you accomplish this?

Solution

By maintaining a list of active procedures, adding the current name to the list on the way into the procedure and removing it on the way out, you can always keep track of the current procedure and the procedure calls that got you there. There are many other uses for this functionality (see the next solution, for example), but one simple use is to retrieve the name of the current procedure in a global error-handling procedure.

The kind of data structure you'll need for maintaining your list is called a stack. As you enter a new procedure, you "push" its name onto the top of the stack. When you leave the procedure, you "pop" the name off the stack. Figure 7-2 shows a graphical representation of a procedure stack in action. The arrows indicate the direction in which the stack grows and shrinks as you add and remove items.

Figure 7-2.  The call stack and the sample routines to fill it

To see the procedure stack in action, load 07-02.MDB. Open the module basTestStack in design mode. Open the Immediate window (choose View Immediate Window). In the Immediate window, type:

  ? A( )

to execute the function named A. Figure 7-2 shows A and the procedures it calls. At each step, the current procedure pushes its name onto the procedure stack and then calls some other procedure. Once the calling procedure regains control, it pops its name off of the stack. In addition, each procedure prints the name of the current procedure (using the acbCurrentProc function, discussed later in this solution) to the Immediate window. Once all execution has finished, you should see in the Immediate window output like that shown in Figure 7-3.

Figure 7-3.  The output from running the sample procedure

Follow these steps to incorporate this functionality into your own applications:

1. Import the module basStack into your application. This includes the procedures that initialize and maintain the procedure stack.
   
2. Insert a call to the acbInitStack subroutine into code that's executed when your application starts up. Consider adding this procedure call to the code in your main form's Load event procedure. You'll want to call acbInitStack any time you restart your program during development, so you probably don't want to call it from the Autoexec macro, which is executed only when you first load the database. To call acbInitStack, either place its name alone on a line of code, like this:
   
   acbInitStack
   
   or use the Call construct, as follows:
   
   Call acbInitStack
   

3. For each procedure in your application, place a call to acbPushStack as the first statement. This procedure will place the value it's passed on the top of the stack. As the single argument for each call, pass the name of the current procedure.
   
   Our example places a pair of parentheses after function names and nothing after subroutine names, as a matter of style. As the last line in each procedure add a call to acbPopStack, which will remove the current name from the top of the stack.

4. You can retrieve the name of the currently executing procedure at any time by calling the acbCurrentProc function. This function looks at the top of the stack and returns the string it finds there. You can use this as part of an error handler or, as in the next solution, to help track procedure performance.

Discussion

The module you imported from 07-02.MDB, basStack, includes code for maintaining the procedure stack and a module-local variable that is the stack itself. There are just six entry points (nonprivate procedures) in the module. Table 7-1 lists those procedures. Since all the code for the stack is encapsulated in that one module, you never really have to know how it all works. However, it's quite simple.

basStack includes two module-level variables: mastrStack , the array of strings that is the stack itself; and mintStackTop , an integer that holds the array slot into which the next stack item will be placed. When you begin your work with the stack, mintStackTop  must be 0, so the first item will go in the slot numbered 0. The acbInitStack procedure does nothing other than initialize mintStackTop :

  Public Sub acbInitStack( )
      ' Resets the stack top to 0.
      mintStackTop = 0
  End Sub

You can add an item to the stack at any time by calling acbPushStack. Pass to this subroutine the item you want pushed. To push the item, the code places the item in the array at the location stored in mintStackTop  and then increments the value of mintStackTop . Its code is:

  Public Sub acbPushStack(strToPush As String)

       ' Push a string onto the call stack.
       ' If the stack is full, display an error.
       ' Otherwise, add the new item to the call stack.

       ' Handle the error case first.
       If mintStackTop > acbcMaxStack Then
           MsgBox acbcMsgStackOverflow
       Else
          ' Store away the string.
          mastrStack(mintStackTop) = strToPush

          ' Set mintStackTop to point to the NEXT
          ' item to be filled.
          mintStackTop = mintStackTop + 1
      End If
  End Sub

The only problem that might occur is that the stack might be full. The constant acbcMaxStack is originally set to 20, which should be enough levels. (Remember that mintStackTop  goes up one only when a procedure calls another procedure. If you have 20 levels of procedure calling, you might consider rethinking your application, instead of worrying about procedure stacks!) If the stack is full, acbPushStack will pop up an alert and will not add the item to the stack.

When leaving a procedure, you'll want to remove an item from the stack. To do so, call the acbPopStack procedure:

  Public Sub acbPopStack( )

      ' Pop a string from the call stack.
      ' If the stack is empty, display an error.
      ' Otherwise, set the current item to be the
      ' next one to be filled in. If you're logging,
      ' send the information out to the log file.

      ' Handle the error case first.
      If mintStackTop = 0 Then
          MsgBox acbcMsgStackUnderflow
      Else
         ' Because you're removing an item, not adding one,
         ' set the stack top back to the previous row. Next time
         ' you add an item, it'll go right here.
         mintStackTop = mintStackTop - 1
     End If
  End Sub

Just as in acbPushStack, this code first checks to make sure that the stack integrity hasn't been violated; you can't remove an item from the stack if there's nothing to remove! If you try, acbPopStack will pop up an alert and exit. If the stack is intact, the procedure will decrement the value of mintStackTop . Decrementing that value sets up the next call to acbPushStack so that it will place the new value where the old one used to be.

To retrieve the value at the top of the stack without pushing or popping anything, call the acbCurrentProc function:

  Public Function acbCurrentProc( ) As String
      ' Since mintStackTop always points to the next item to
      ' be filled in, retrieve the item from mintStackTop - 1.
      If mintStackTop > 0 Then
         acbCurrentProc = mastrStack(mintStackTop - 1)
      Else
          acbCurrentProc = ""
      End If
  End Function

This function retrieves the value most recently placed on the stack (at the location one less than mintStackTop , because mintStackTop always points to the next location to be filled). You can't look at mastrStack yourself, because it's local to basStack--and that's the way it ought to be. Since the details of how the stack works are kept private, you can replace basStack, using a different architecture for the stack data structure, and the rest of your code won't have to change at all.

To retrieve more information about what's in the stack, you can call acbGetStackItems, to find out how many items there are in the stack, and acbGetStack, which retrieves a specific item from the stack. For example, write code like this to dump out the entire stack (see subroutine D, which does just this, in the basTestStack module):

  Debug.Print "Stack items currently:"
  For intI = 0 To acbGetStackItems( ) - 1
     Debug.Print , acbGetStack(intI)
  Next intI

The acbGetStackItems function is simple: it returns the value of mintStackTop , because that value always contains the number of items in the stack:

  Public Function acbGetStackItems( ) As Integer
      ' Retrieve the number of items in the stack.
      acbGetStackItems = mintStackTop
  End Function

The acbGetStack function is a little more complex. It accepts an item number (requesting item 0 returns the item at the top of the stack) and calculates the
posi tion of the item to retrieve. Its source code is:

  Public Function acbGetStack(mintItem As Integer) As Strin g
      ' Retrieve the item that's mintItems from the top of the
      ' stack. That is,
      ' ? acbGetStack(0)
      ' would return the same value as acbCurrentProc.
      ' ? acbGetStack(3) would return the third value from the top.
      If mintStackTop >= mintItem Then
          acbGetStack = mastrStack(mintStackTop - mintItem - 1)
      Else
          acbGetStack = ""
      End If
  End Function

For the procedure stack to work, you have to place calls to acbPushStack and acbPopStack on entry and exit from every procedure call. Good coding practice
supports the concept of only one exit point from each procedure, but even the best programmer sometimes breaks this rule. To use the call stack, however, you must catch every exit point with a call to acbPopStack. Keep this in mind as you retrofit old code to use this mechanism and when you devise new code to use it. You can always code for a single exit point, and you will find code maintenance much easier if you do.

7.3 Create an Execution Time Pro?ler

Problem

You'd like to optimize your VBA code, but it's almost impossible to tell how long Access is spending inside any one routine and it's difficult to track which procedures are called by your code most often. You'd like some way to track which routines are called, in what order, and how much time each takes to run. Can you do this?

As outlined in the Solution in Recipe 7.2, you can create a code profiler using a stack data structure to keep track of the execution order and timing of the procedures in your application. Though the code involved is a bit more advanced than that in the Solution in Recipe 7.2, it's not terribly difficult to create the profiler. Using it is simple, as all the work is wrapped up in a single module.

Open the database 07-03.MDB and load the module basTestProfiler in design mode. In the Immediate window, type:

  ? A( )

to run the test procedures. Figure 7-4 shows the profile stack and the code in A. As you can see, A calls B, which calls C, which calls D, which waits 100 ms and then returns to C. C waits 100 ms and then calls D again. Once D returns, C returns to B, which waits 100 ms and then calls C again. This pattern repeats until the code gets back to A, where it finally quits. The timings in the profile stack in Figure 7-4 are actual timings from one run of the sample.

As the code is set up now, the profiler writes to a text file named C:\LOGFILE.TXT. You can read this file in any text editor. For a sample run of function A, the file contained this text:

  ******************************* *
  Procedure Profiling
  8/13/2003 3:29:11 PM

Figure 7-4.  The profile stack and the sample rountines used to fill it

To incorporate this sort of profiling into your own applications, follow these steps:

1. Import the module basProfiler into your application. This module includes all the procedures needed to initialize and use the profile stack.
   
2. Insert a call to acbProInitCallStack into code that's executed when your application starts up. In the Solution in Recipe 7.2, you might have gotten by without calling the initialization routine. In this situation, however, you must call acbProInitCallStack each time you want to profile your code, or the profile stack will not work correctly. To call acbProInitCallStack, you must pass it three parameters, all of which are logical values ( True or False ). Table 7-2 lists the question answered by each of the parameters.

Table 7-2. Parameters for acbProInitCallStack

The procedure initializes some global variables and, if you're writing to a log file, writes a log header to the file. A typical call to acbProInitCallStack might look like this:

acbProInitCallStack False, True, True

3. For each procedure in your application, place a call to acbProPushStack as the first statement. This procedure places the value it's passed on the top of the stack, along with the current time. As the single argument for each call, pass the name of the current procedure. Our example places a pair of parentheses after function names and nothing after subroutine names, as a matter of style. As the last line in each procedure, add a call to acbProPopStack, which will remove the current name from the top of the stack and record the current time.
   
4. You can retrieve the name of the currently executing procedure at any time by calling the acbProCurrentProc function. This function looks at the top of the stack and returns the string it finds there.
   
5. To review the outcome of your logging, view the file LOGFILE.TXT (in your Access directory) in any text editor. If you followed the previous steps carefully, you should see matching entry and exit points for every routine. Nested levels are indented in the printout, and entry and exit points are marked differently (entry points with a "+" and exit points with a "-").

The module you imported from 07-03.MDB, basProfiler, includes all the code that maintains the profiler. There are five public entry points to the module, as shown in Table 7-3.

In general, the profiler works almost exactly like the simpler procedure stack shown in the Solution in Recipe 7.2. As a matter of fact, the code for this solution was written first and was then stripped down for use in the simpler example. This example includes the code necessary to write to the file on disk as well as to gather timing information. The next few paragraphs outline the major differences and how they work.

Whereas the Solution in Recipe 7.2 used a simple array of strings to hold the stack information, the profiler also needs to store starting and ending times for each routine. To create the stack, it uses an array of a user-defined type, acbStack, which is defined as follows:

  Type acbStack
     strItem As String
     lngStart As Long
     lngEnd As Long
  End Type
  Dim maStack(0 To acbcMaxStack) As acbStack

Access provides the Timer function, which returns the number of seconds since midnight, but this resolution won't give you enough information for measuring the duration of procedures in VBA. Another option is Windows's t function, which returns the number of milliseconds since you started Windows. TimeGetTime resets itself to every 48 days, whereas Timer resets once every day--if you need to time a lengthy operation, timeGetTime provides a mechanism for measuring time spans longer than a single day (and makes it possible to measure time spans that cross midnight). Of course, if you're timing an operation that takes more than a day, you're probably not going to care about millisecond accuracy, but that's what you get! The code in basProfiler calls timeGetTime to retrieve the current "time" whenever you push or pop a value and stores it in the stack array. You can call timeGetTime in any application, once you include this declaration in a global module:

  Public Declare Function timeGetTime _
   Lib "Kernel32" ( ) As Long

The code in basTestProfiler also uses timeGetTime in the Wait subroutine. This procedure does nothing but wait for the requested number of milliseconds, calling DoEvents inside the loop and giving Windows time to do its work:

  Public Sub Wait (intWait As Integer)
     Dim lngStart As Long
     lngStart = timeGetTime( )
     Do While timeGetTime( ) < lngStart + intWait
        DoEvents
     Loop
  End Sub

The code in basProfiler opens and closes the output file each time it needs to write a piece of information. This slows down your application, but it ensures that if your machine crashes for some reason, your log file will always be current. Although you'll never directly call this routine, if you've never used Access to write directly to a text file you may find it interesting to see how it does its work.

The acbProWriteToLog procedure first checks to see if an error has ever occurred in the logging mechanism (that is, if mfLogErrorOccurred has been set to True). If so, it doesn't try to write anything to the file, because something may be wrong with the disk. If not, it gets a free file handle, opens the log file for appending, writes the item to the file, and then closes it. The following is the source code for the acbProWriteToLog routine:

  Private Sub acbProWriteToLog (strItem As String)
      Dim intFile As Integer

      On Error GoTo HandleErr

      ' If an error has EVER occurred in this session,
      ' just get out of here.
      If mfLogErrorOccurred Then Exit Sub

      intFile = FreeFile
      Open acbcLogFile For Append As intFile
      Print #intFile, strItem
      Close #intFile

  ExitHere:
      Exit Sub

  HandleErr:
      mfLogErrorOccurred = True
      MsgBox Err & ": " & Err.Description, , "Writing to Log"
      Resume ExitHere
  End Sub

As in the Solution in Recipe 7.2, you'll find that for the procedure stack profiler mechanism to be of any value, you must be conscientious about the placement of your calls to acbProPushStack and acbProPopStack. If you have multiple exit points from routines, this is a good time to try to consolidate them. If you can't, you'll need to make sure that you've placed a call to acbProPopStack before every exit point in each procedure.

If you attempt to decipher the log file, you'll notice that the elapsed time for each procedure must also include any procedures it happens to call, as in the example of A calling B, which calls C, which calls D. The elapsed time for function A was 1,702 ms. That's the time that elapsed between the calls to acbProPushStack and acbProPopStack in function A, including the time it took to run all the calls to B, C, and D. This isn't necessarily a problem, nor is it wrong, but you should be aware that there's no way to "stop the clock" while in subordinate procedures.

The code for the profiler includes another public entry point, acbProLogString. The profiler doesn't actually call this procedure, but your own code can. Pass it a single string, and the profile will send that string to the log file for you. For example, the following code will append "This is a test" to the log file:

  acbProLogString "This is a test"

7.4 Multitask Your VBA Code

Problem

If your VBA code includes a loop that runs for more than just a second or two, Access seems to come to a halt. You can't move the windows on the screen, and mouse-clicks inside Access are disregarded until your code has finished running. Why is this happening? Is there something you can do to relinquish some control?

You may have noticed that it's possible to tie up Access with a simple bit of VBA code. Though 32-bit Windows is multithreaded, this helps only if the applications running under it are also multithreaded. It appears that the executing VBA code ties up Access's processing, so the multithreaded nature of Windows doesn't help. If your code contains loops that run for a while, you should make a conscious effort to give Windows time to catch up and do its own work. VBA includes the DoEvents statement, which effectively yields time to Windows so that Access can perform whatever other tasks it must. Effective use of DoEvents can make the difference between an Access application that hogs Access's ability to multitask and one that allows Access to run smoothly while your VBA code is executing.

To see the problem in action, load and run the form frmDoEvents (in 07-04.MDB). Figure 7-5 shows the form in use. The form includes three command buttons, each of which causes the label with the caption "Watch Me Grow!" to change its width from 500 to 3500 twips (in Figure 7-5, you can see only a portion of the label), in a loop like this:

  Me.lblGrow1.Width = 500
  For intI = 0 To 3000
      Me.lblGrow1.Width = Me.lblGrow1.Width + 1
      ' Without this call to Repaint, you'll
      ' never see any changes on the screen.
      Me.Repaint
  Next intI

Figure 7-5.  The sample DoEvents Test form, frmDoEvents, in action

To test the effects of DoEvents, try these steps:

1. Press the "Run Code Without DoEvents" button. The code attached to this button will change the width of the label inside a loop without yielding time to Access. While the code is running, try to click on another button on the form or to move or size the active window. You will find that any of these tasks is impossible while the label is expanding. Once the label has finished growing, Access will display any actions you attempted to make during the process.
   
2. Try the same loop with DoEvents inserted. Click the second button, labeled "Run Code With DoEvents 1". This time, as the code executes, you will be able to move or size the active window. In addition, you can click on any of the form's buttons while the code is running. The next step tests this capability.
   
3. While the label is growing, click on the "Run Code With DoEvents 1"  button many times in quick succession. Every time you click the button, Access starts up another instance of the Click event procedure, and each instance continues to make the label grow. This is called recursion, in which multiple calls are made into the same routine, each starting before the last instance has completed. Each time you call the Click event, you use a bit of Access's stack space (a memory area set aside for each procedure's entry information and local variables). It's possible that, with many invocations, you will use up that memory. Using versions of Access later than Access 95, we've never made this happen. Using Access 2, it was easy to do. The next step offers a solution to this recursion problem.
   
4. Click the third button, labeled "Run Code with DoEvents 2". While the label is expanding, try clicking on the button again. You'll see that this time your clicks won't have any effect. The code attached to this button checks to see if it's already running and, if so, exits the code. This method solves the problem of recursive calls to DoEvents.

The code attached to the first button does its work without any concern for Windows or other running applications. When you press it, it executes this code:

  Private Sub cmdNoDoevents_Click()
      Dim intI As Integer

      Me.lblGrow1.Width = 500
      For intI = 0 To 3000
          Me.lblGrow1.Width = Me.lblGrow1.Width + 1
          ' Without this call to Repaint, you'll
          ' never see any changes on the screen.
          Me.Repaint
      Next intI
  End Sub

Because the code never gives Windows time to "catch up," you must include the call to Me.Repaint to make sure the form repaints itself after each change. To see how this works, comment out that line and press the first button again. You'll see that the screen won't repaint until the entire operation is done.

The code attached to the second button does the same work, but it calls DoEvents within the loop. With that statement added, you no longer need the call to Me. Repaint, because DoEvents allows Windows to take care of the pending repaints. It also allows you to use the mouse and other applications while this loop is running. The code attached to the second button looks like this:

  Private Sub TestDoEvents()

      Dim intI As Integer

      Me.lblGrow1.Width = 500
      For intI = 0 To 3000
          Me.lblGrow1.Width = Me.lblGrow1.Width + 1
          DoEvents
      Next intI
  End Sub

Private Sub cmdDoEvents1_Click()

TestDoEvents End Sub

The problem with this code, as mentioned in Step 2, is that nothing keeps you from initiating it again while it's running; if you press the same button while the code is in the middle of the loop, Access will start up the same procedure again. Every time Access starts running a VBA routine, it stores information about the routine and its local variables in a reserved area of memory, called its "stack". The size of this area is fixed and limits the number of procedures that can run concurrently. If you press that button over and over again in quick succession, it's possible that you'll overrun Access's stack space.

It's doubtful that you'll ever be able to reproduce this problem with this tiny example. Though the stack space was limited to 40 KB in Access 2, it was increased to a much larger size in Access 95 and later versions. You'd have to press that button very fast for a very long time to fill up that much stack space. However, in more complex situations, if you were passing a large amount of data to a procedure in its parameter list, this could still be a problem.

The third button on the form demonstrates the solution to this problem. It ensures that its code isn't already running before it starts the loop. If it's already in progress, the code just exits. The code attached to the third button looks like this:

  Private Sub cmdDoEvents2_Click()
      Static blnInHere As Boolean

      If blnInHere Then Exit Sub
      blnInHere = True
      TestDoEvents
      blnInHere = False
  End Sub

It uses a static variable, blnInHere, to keep track of whether the routine is already running. If blnInHere is currently True, it exits. If not, it sets the variable to True and then calls cmdDoEvents1_Click (the previous code fragment). Once cmdDoEvents1_Click returns, cmdDoEvents2_Click sets blnInHere  back to False, clearing the way for another invocation.

DoEvents is one of the most misunderstood elements of VBA. No matter what programmers would like DoEvents to do, under versions of Access later than Access 95 it does nothing more than yield time to Access so it can process all the messages in its message queue. It has no effect on the Access database engine itself and can't be used to slow things down or help timing issues (other than those involving Windows messages). When used in VBA code, DoEvents releases control to the operating environment, which doesn't return control until it has processed the events in its queue and handled all the keys in the SendKeys queue. Access will ignore DoEvents in:

1. A user-defined procedure that calculates a field in a query, form, or report
   
2. A user-defined procedure that creates a list to fill a combo or list box

As you can see from the second button on the sample form, recursively calling DoEvents can lead to trouble. You should take steps, as in the example of the third button, to make sure that this won't occur in your applications.

7.5 Programmatically Add Items to a List or Combo Box

Problem

Getting items into a list or combo box from a data source is elementary in Access. Sometimes, though, you need to put things into a list box that you don't have stored in a table. In Visual Basic and other implementations of VBA-hosted environments, and in Access 2002 and later, this is simple: you just use the AddItem method. But Access list boxes in versions prior to 2002 don't support this method. How can you add to a list box items that aren't stored in a table?

Access list boxes (and combo boxes) in versions prior to Access 2002 didn't support the AddItem method that Visual Basic programmers are used to using. To make it easy for you to get bound data into list and combo boxes, the Access developers originally didn't supply a simple technique for loading unbound data. To get around this limitation, there are two methods you can use to place data into an Access list or combo box: you can programmatically build the RowSource string yourself, or you can call a list-filling callback function. Providing the RowSource string is easy, but it works in only the simplest of situations. A callback function, though, will work in any situation. This solution demonstrates both methods. In addition, this solution demonstrates using the AddItem method of ListBox and ComboBox controls, added in Access 2002.

One important question, of course, is why you would ever need either of the more complex techniques for filling your list or combo box. You can always pull data from a table, query, or SQL expression directly into the control, so why bother with all this work? The answer is simple. Sometimes you don't know ahead of time what data you're going to need, and the data's not stored in a table. Or perhaps you need to load the contents of an array into the control and you don't need to store the data permanently. Prior to Access 2002, you had no choice but to either create a list-
filling callback function, or modify the RowSource property of the control yourself. Starting in Access 2002, you can also use the AddItem method to solve many list filling requirements.

The following sections walk you through using all three of the techniques for modifying the contents of a list or combo box while your application is running. The first example modifies the value of the RowSource property, given that the RowSourceType property is set to Value List. The second example covers list-filling callback functions. The final example shows how to use the AddItem method of the control.

1. Open the form frmAddItem in 07-05.MDB.
   
2. Change the contents of the list box by choosing either Days or Months from the option group on the left. Try both settings and change the number of columns to get a feel for how this method works. Figure 7-6 shows the form set to
   display month names in three columns.
   
   
   Figure 7-6.  The sample form, frmRowSource, displaying months in three columns

1. Open the form frmRowSource in 07-05.MDB.
   
2. Change the contents of the list box by choosing either Days or Months from the option group on the left. Try both settings and change the number of columns, to get a feel for how this method works. Figure 7-6 shows the form set to display month names in three columns. 

Filling a list box by creating a list-filling callback function

1. Open the form frmListFill in 07-05.MDB.
   
2. Select a weekday from the first list box. The second list box will show you the date of that day this week, plus the next three instances of that weekday. Figure 7-7 shows the form with Wednesday, March 14, 2001, selected.
   
3. To use this method, set the control's RowSourceType property to the name of a function (without an equals sign or parentheses). Functions called this way must
   
   
   

   Figure 7-7.  Using list-filling callback functions to fill the lists on frmListFill
   
   meet strict requirements, as discussed in the next section. Figure 7-8 shows the properties sheet for the list box on frmListFill, showing the RowSourceType property with the name of the list-filling function.
   
   
   Figure 7-8.  The properties sheet entry for the list-filling function

This section explains the two methods for programmatically filling list and combo boxes. The text refers only to filling list boxes, but the same techniques apply to combo boxes. You may find it useful to open up the form module for each form as it's discussed here.

Calling the AddItem method

Starting with Access 2002, you can add items to a ListBox or ComboBox control by simply calling the AddItem method of the control. (You can remove items from the control by calling its RemoveItem method, specifying the item number or text to remove.) This technique is by far the simplest and should be your first choice, given the option.

Selecting an option in the Fill Choice group runs the following code:

  Private Sub grpChoice_AfterUpdate()
      Dim strList As String
      Dim intI As Integer
      Dim varStart As Variant

      lstAddItem.RowSourceType = "Value List"

      ' Clear out the list .
      lstAddItem.RowSource = vbNullString
      lstAddItem.ColumnCount = 1
      grpColumns = 1

      Select Case Me.grpChoice
          Case 1 ' Days
              ' Get last Sunday's date.
              varStart = Now - WeekDay(Now)
              ' Loop through all the week days.
              For intI = 1 To 7
                  lstAddItem.AddItem Format(varStart + intI, "dddd")
              Next intI

          Case 2 ' Months
              For intI = 1 To 12
                  lstAddItem.AddItem Format(DateSerial(2004, intI, 1), "mmmm")
              Next intI
      End Select

      Me.txtFillString = lstAddItem.RowSource
  End Sub

This code starts by setting the RowSourceType property of the control to the text, "Value List":

  lstAddItem.RowSourceType = "Value List"

This step is crucial: unless you've set the RowSourceType property correctly, either at design time or in your code, you won't be able to call the AddItem or RemoveItem methods.

Next, the code clears and resets the list's formatting:

  lstAddItem.RowSource = vbNullString 
  lstAddItem.ColumnCount = 1
  grpColumns = 1

Then, depending on the choice you've made, the code adds days of the week or months of the year to the ListBox control:

  Select Case Me.grpChoice
      Case 1 ' Days
          ' Get last Sunday's date.
          varStart = Now - WeekDay(Now)
          ' Loop through all the week days.
          For intI = 1 To 7
              lstAddItem.AddItem Format(varStart + intI, "dddd")
          Next intI

      Case 2 ' Months
          For intI = 1 To 12
              lstAddItem.AddItem Format(DateSerial(2004, intI, 1), "mmmm")
          Next intI
  End Select

In order to verify that, under the covers, the code is simply manipulating the RowSource property for you, the example ends by displaying the RowSource property in a TextBox control on the form:

  Me.txtFillString = lstAddItem.RowSource

Beware that even though it appears that you're actually adding items to the control, what you're really doing is modifying the RowSource property of the control. As such, you're limited by the same restrictions as if you were setting the property manually (see the next section). Specifically, you're limited to the allowed size of the RowSource property, which was 2048 characters in Access 2002 (the size may be larger in your version of Access).

If you're using Access 2002 or later, you won't want to use this technique. On the other hand, for earlier versions of Access, this can be a simple way to create unbound lists. If you set a list box's RowSourceType property to Value List, you can supply a list of items, separated with semicolons, that will fill the list. By placing this list in the control's RowSource property, you tell Access to display the items one by one in each row and column that it needs to fill. Because you're placing data directly into the properties sheet, you're limited by the amount of space available in the properties sheet (this value varies depending on the version of Access).

You can modify the RowSource property of a list box at any time by placing into it a semicolon-delimited list of values. The ColumnCount property plays a part, in that Access fills the rows first and then the columns. You can see this for yourself if you modify the ColumnCount property on the sample form (frmRowSource).

The sample form creates a list of either the days in a week or the months in a year, based on the value and option group on the form. The code that performs the work looks like this:

  Select Case Me.grpChoice
     Case 1 ' Days
        ' Get last Sunday's date.
        varStart = Now - WeekDay(Now)
        ' Loop through all the days of the week.
        For intI = 1 To 7
           strList = strList & ";" & Format(varStart + intI, "dddd")
        Next intI

     Case 2 ' Months
        For intI = 1 To 12
           strList = strList & ";" & Format(DateSerial(2004, intI, 1), "mmmm")
        Next intI
  End Select

  ' Get rid of the extra "; " at the beginning.
  strList = Mid(strList, 2)
  Me.txtFillString = strList

Depending on the choice in grpChoice, you'll end up with either a string of days like this:

  Sunday; Monday; Tuesday; Wednesday; Thursday; Friday; Saturday; Sunday

or a string of months like this:

  January; February; March; April; May; June; July; August; September; October; _ 
   November; December

Once you've built up the string, make sure that the RowSourceType property is set correctly and then insert the new RowSource string:

  lstChangeRowSource.RowSourceType = "Value List"
  lstChangeRowSource.RowSource = strList

If you intend to use this method, modifying the RowSource property, make sure you understand its main limitation: because it writes the string containing all the values for the control into the control's properties sheet, it's limited by the number of
characters the properties sheet can hold.

If you're using a version of Access prior to Access 2002, you can use at most 2,048 characters in the RowSource property. If you need more data than that, you'll need to use a different method. If you're using Access 2002 or later you shouldn't have a problem, because the size has been greatly expanded. On the other hand, in those versions, you're better off using the AddItem method instead.

This technique, which involves creating a special function that provides the information Access needs to fill your list box, is not well documented in the Access help. Filling a list using a callback function provides a great deal of flexibility, and it's not difficult. This technique provides the greatest flexibility, and isn't limited by the size of the RowSource property.

The concept is quite simple: you provide Access with a function that, when requested, returns information about the control you're attempting to fill. Access "asks you questions" about the number of rows, the number of columns, the width of the columns, the column formatting, and the actual data itself. Your function must react to these requests and provide the information so that Access can fill the control with data. This is the only situation in Access where you provide a function that you never need to call. Access calls your function as it needs information in order to fill the control. The sample form frmFillList uses two of these functions to fill its two list boxes.

To communicate with Access, your function must accept five specific parameters. Table 7-4 lists those parameters and explains the purpose of each. (The parameter names are arbitrary and are provided here as examples only. The order of the parameters, however, is not arbitrary; they must appear in the order listed in Table 7-4.)

Access uses the final parameter, intCode , to let you know what information it's currently requesting. Access places a particular value in that variable, and it's up to your code to react to that request and supply the necessary information as the return value of your function. Table 7-5 lists the possible values of intCode, the meaning of each, and the value your function must return to Access in response to each.

Table 7-5. The values of intCode, their meanings, and their return values

You'll find that almost all of your list-filling functions will be structured the same way. Therefore, you may find it useful to always start with the ListFillSkeleton function, which is set up to receive all the correct parameters and includes a Select Case statement to handle each of the useful values of intCode . All you need to do is change its name and make it return some real values. The ListFillSkeleton function is as follows:

  Function ListFillSkeleton (ctl As Control, _
   varId As Variant, lngRow As Long, lngCol As Long, _
   intCode As Integer) As Variant

    Dim varRetval As Variant

    Select Case intCode
       Case acLBInitialize
          ' Could you initialize?
          varRetval = True

       Case acLBOpen
          ' What's the unique identifier? 
          varRetval = Timer

       Case acLBGetRowCount
          ' How many rows are there to be?

       Case acLBGetColumnCount
          ' How many columns are there to be?

       Case acLBGetValue
          ' What's the value in each row/column to be?

       Case acLBGetColumnWidth
          ' How many twips wide should each column be?
          ' (optional)

       Case acLBGetFormat
          ' What's the format for each column to be?
          ' (optional)

       Case acLBEnd
          ' Just clean up, if necessary (optional, unless you use
          ' an array whose memory you want to release).

    End Select
    ListFillSkeleton = varRetval
  End Function

For example, the following function from frmListFill, ListFill1, fills in the first list box on the form. This function fills in a two-column list box, with the second column hidden (its width is set to 0 twips). Each time Access calls the function with acLBGetValue in intCode , the function calculates a new value for the date and returns it as the return value. The source code for ListFill1 is:

  Private Function ListFill1(ctl As Control, varId As Variant, _
   lngRow As Long, lngCol As Long, intCode As Integer)

    Select Case intCode
       Case acLBInitialize
          ' Could you initialize?
          ListFill1 = True

       Case acLBOpen
          ' What's the unique identifier?
          ListFill1 = Timer

       Case acLBGetRowCount
          ' How many rows are there to be?
          ListFill1 = 7

       Case acLBGetColumnCount
          ' How many columns are there to be?

          ' The first column will hold the day of the week.
          ' The second, hidden column will hold the actual date.
          ListFill1 = 2

       Case acLBGetColumnWidth
          ' How many twips wide should each column be?

          ' Set the width of the second column to 0.

          ' Remember, they're zero-based.
          If lngCol = 1 Then ListFill1 = 0

       Case acLBGetFormat
          ' What's the format for each column to be?

          ' Set the format for the first column so
          ' that it displays the day of the week.
          If lngCol = 0 Then
             ListFill1 = "dddd"
          Else
             ListFill1 = "mm/dd/yy"
          End If

       Case acLBGetValue
          ' What's the value for each row in each column to be?

          ' No matter which column you're in, return
          ' the date lngRow days from now.
          ListFill1 = Now + lngRow

       Case acLBEnd
          ' Just clean up, if necessary.

    End Select
  End Function

The next example, which fills the second list box on the sample form, fills an array of values in the initialization step (acLBInitialize) and returns items from the array when requested. This function, ListFill2, displays the next four instances of a particular day of the week. That is, if you choose Monday in the first list box, this function will fill the second list box with the date of the Monday in the current week, along with the dates of the next three Mondays. The source code for ListFill2 is:

  Private Function ListFill2( _
   ctl As Control, varId As Variant, lngRow As Long, _
   lngCol As Long, intCode As Integer)

  Const MAXDATES = 4

     Static varStartDate As Variant
     Static adtmDates(0 To MAXDATES) As Date
     Dim intI As Integer
     Dim varRetval As Variant

     Select Case intCode
         Case acLBInitialize
            ' Could you initialize?

            ' Do the initialization. This is code
            ' you only want to execute once.
            varStartDate = Me.lstTest1
            If Not IsNull(varStartDate) Then
              For intI = 0 To MAXDATES - 1
                  adtmDates(intI) = DateAdd("d", 7 * intI, varStartDate)
              Next intI
              varRetval = True
          Else
             varRetval = False
          End If

       Case acLBOpen
          ' What's the unique identifier?
          varRetval = Timer

       Case acLBGetRowCount
          ' How many rows are there to be?
          varRetval = MAXDATES

       Case acLBGetFormat
          ' What's the format for each column to be?
          varRetval = "mm/dd/yy"

       Case acLBGetValue
          ' What's the value for each row in each column to be?
          varRetval = adtmDates(lngRow)

       Case acLBEnd
          ' Just clean up, if necessary. 
          Erase adtmDates 
    End Select
    ListFill2 = varRetval
  End Function

Note that the array this function fills, adtmDates, is declared as a static variable. Declaring it this way makes it persistent: its value remains available between calls to the function. Because the code fills the array in the acLBInitialize case but doesn't use it until the multiple calls in the acLBGetValue case, adtmDates must "hang around" between calls to the function. If you fill an array with data for your control, it's imperative that you declare the array as static.

You should also consider the fact that Access calls the acLBInitialize case only once, but it calls the acLBGetValue case at least once for every data item to be displayed. In this tiny example, that barely makes a difference. If you're doing considerable work to calculate values for display, however, you should put all the time-consuming work in the acLBInitialize case and have the acLBGetValue case do as little as possible. This optimization can make a big difference if you have a large number of values to calculate and display.

There are three more things you should note about this second list box example:

• In the acLBEnd case, the function clears out the memory used by the array. In this small example, this hardly matters. If you are filling a large array with data, you'd want to make sure that the data is released at this point. For dynamic arrays (where you specify the size at runtime), Erase releases all the memory. For fixed-size arrays, Erase empties out all the elements.

1. This example didn't include code for all the possible cases of intCode . If you don't need a specific case, don't bother coding for it. There was no need to set the column widths here, so there's no code handling acLBGetColumnWidth.
   
2. At the time of this writing, there's a small error in the way Access handles these callback functions. Although it correctly calls the acLBInitialize case only once when you open a form that requires a control to be filled with the function, if you later change the RowSourceType in code, Access will call the acLBInitialize case twice. This doesn't come up often, but you should be aware that there are circumstances under which Access will erroneously call this section of your code more times than you intended. To solve this problem, you can use a static or global variable as a flag to keep track of the fact that the initialization has been done and opt not to execute the code after the first pass through.

In the list-filling callback function method, when Access requests the number of rows in the control (i.e., when it passes acLBGetRowCount in intCode ), you'll usually be able to return an accurate value. Sometimes, however, you won't know the number of rows or won't be able to get the information easily. For example, if you're filling the list box with the results of a query that returns a large number of rows, you won't want to perform the MoveLast method you'd need to find out how many rows the query returned--MoveLast requires Access to walk through all the rows returned from the query and would make the load time for the list box too long. Instead, respond to acLBGetRowCount with a -1. This tells Access that you'll tell it later how many rows there are. Then, in response to the acLBGetValue case, return data until you've reached the end. Once you return Null in response to the acLBGetValue case, Access understands that there's no more data.

This method has its pitfalls, too. Although it allows you to load the list box with data almost immediately, the vertical scrollbar won't be able to operate correctly until you've scrolled down to the end. If you can tolerate this side effect, returning -1 in response to acLBGetRowCount will significantly speed the loading of massive amounts of data into list and combo box controls.

To provide values for the acLBGetColumnWidth case, you can specify a different width for each column based on the lngCol  parameter. To convert from inches to twips, multiply the value by 1,440. For example, to specify a 1/2-inch column, return 0.5 x 1,440.

You might wonder when you would use any of these techniques. In Access 2002 or later, your best bet is to use the AddItem method whenever possible. Under the covers, this method executes the same sort of code as if you were to modify the RowSource property value yourself. (You don't really need to ever modify the RowSource property manually, in Access 2002 or later--calling the AddItem and RemoveItem methods does the same sort of thing for you.) Remember, however, that the RowSource property value is limited in size. For large lists of values, perhaps with many columns, you may run out of space before you run out of data. In that case, you'll be required to use the list-filling callback function technique. If you're using Access 2000 or an earlier version, youll need to use the list-filling callback technique for complex lists, or to create the RowSource property value in code yourself for simpler lists.

Please check back next week for the conclusion of this article.