7.6 Pass a Variable Number of Parameters to a Procedure
Problem
You need a procedure that will work on a list of items, and you don't know ahead of time how many there will be. You know that VBA will allow you to use optional parameters, but this requires you to know exactly how many items you might ever need to pass, and in your case, it's impossible to predict that value. How can you accomplish this?
Solution
You have two choices in solving this problem: you can pass an array as a parameter, or you can pass a comma-delimited list, which Access will convert into an array for you. An array (an ordered list of items) must contain a single data type. By using the variant data type, though, you can pass a list of varying types into your procedure. This solution demonstrates both these techniques.
From 07-06.MDB , load the module basArrays in design mode and do the following:
Open the Immediate window (press Ctrl+G or choose the View -> Immediate Window menu item). In these steps, you will run code from the Immediate window.
If you need a procedure that will take a list of words and convert each to uppercase, you can use the UCaseArray procedure. To test it, type the following in the Immediate window:
TestUCase 5
You can replace the 5 in the command line with any value between 1 and 26. The procedure will create as many strings as you request, place them into an array, and then call UCaseArray. This procedure will convert all the strings in the array to uppercase. The test procedure will display the original version, followed by the altered version of the array. As you can see, no matter how many items you specify for the UCaseArray procedure to work on, it'll convert them all to uppercase. Figure 7-9 shows this procedure in use.
Figure 7-9. TestUCase with five strings converted
Say you need a procedure that can accept any number of numeric arguments and perform some operation on them. The sample procedure SumThemUp accepts an array of integers, calculates their sum, and returns the total. To try it, type:
TestSum 15
in the Immediate window (you can use any number between 1 and 20). The sample routine, TestSum, will generate an array full of random integers between 1 and 9 and will send the array to SumThemUp for processing. Figure 7-10 shows TestSum working with 15 values.
You may need to write a function that can accept a list of values instead of an array. The ParamArray declaration modifier allows you to do this. Try the MinValue function in basArrays: pass to it a comma-delimited list of values, and the function will return the minimum numeric value from the list you entered. For example:
varMin = MinValue(0, -10, 15)
will return -10, which is the minimum of the three values you passed it.
Figure 7-10.TestSum summing 15 values
Both UCaseArray and SumThemUp accept a variant as a parameter. This variant variable can hold either a single value or an array of values. From the calling end, you can pass either a variant or an actual array of values. To send an array as a parameter, you must add the trailing ( ) characters, indicating to Access that the variable represents an array. Therefore, to pass the array named aintValues to SumThemUp, call the function like this, making sure to include the ( ) in the array name:
varSum = SumThemUp(aintValues( ))
To receive a parameter that is an array, the procedure declaration can include the parentheses:
Public Function SumThemUp (aintValues( ) As Integer) As Variant
in which case you can pass only an array. You can also declare it like this:
Public Function SumThemUp (varValues As Variant) As Variant
in which case you can pass it either a single variant value or an array of values.
Once the procedure has received the array, it needs a way to loop through all the elements of the array. Access provides two methods for walking the array: looping through the items either with a For...Next loop (by index number), or with a For Each...Next loop (without using the index). UCaseArray uses the first method to loop through all the members of its array, and SumThemUp uses the second.
To loop through the elements of an array by number, you must know the bounds of the array; i.e., the lowest and highest element numbers. Access provides two functions, LBound and UBound, to retrieve the lowest and highest element numbers. UCaseArray includes code like this:
For intI = LBound(varValues) To UBound(varValues) varValues(intI) = UCase(varValues(intI)) Next intI
This code loops through all the elements in the array, no matter what the starting and ending items are. In Basic, you can declare an array with any positive integer as its start and end points. For example, in this expression:
Dim avarArray(13 To 97) as Integer
you'd need to loop from 13 to 97 to access each element of the array. The LBound and UBound functions make it possible for generic routines to loop through all the elements of an array, even though they don't know ahead of time how many elements there will be.
The UCaseArray procedure is quite simple: once it determines that the input value is actually an array (using the IsArray function), it loops through all the elements of the passed-in array, converting each to uppercase. The array is passed by reference, using the ByRef keyword, which means that the modified array is returned to the calling procedure. The code for UCaseArray is:
Public Sub UCaseArray(ByRef varValues As Variant)
' Convert the entire passed-in array to uppercase. Dim intI As Integer
If IsArray(varValues) Then For intI = LBound(varValues) To UBound(varValues) varValues(intI) = UCase(varValues(intI)) Next intI Else varValues = UCase(varValues) End If End Sub
The SumThemUp function is no more complex. It uses the For Each...Next syntax to walk through all the elements of the array, maintaining a running sum as it loops. In this case, the variant variablevarItem takes on the value of each element of the array as it loops through the items, and adds its value to varSum . The source code for SumThemUp is:
Public Function SumThemUp(varValues As Variant) As Variant
' Find the sum of the values passed in. Dim varItem As Variant Dim varSum As Variant
varSum = 0 If IsArray(varValues) Then For Each varItem In varValues varSum = varSum + varItem Next varItem Else varSum = varValues End If SumThemUp = varSum End Function
Passing a list that Access converts to an array for you is no more difficult. To use this technique, you must declare your procedure's formal parameters so that the list of values is the last parameter the procedure expects to receive. Use the ParamArray keyword to indicate that you want to treat an incoming list as an array, and declare your array parameter as an array of variants:
Public Function MinValue(ParamArray varValues( ) As Variant) As Variant
Once inside the procedure, you can treat the array parameter like any other array. That is, you can either loop from LBound to UBound for the array, or use a For Each...Next loop to visit each element.
Discussion
To use this method effectively, be aware that unless told otherwise, Access always creates arrays with the first element numbered 0. Some programmers insist on starting all arrays with 1 and so use the Option Base 1 statement in their modules' Declarations areas. Others are happy with 0 as their starting point, and some leave the option base setting at 0 (its default) but disregard the element numbered 0. You must never assume anything about the lower or upper bounds on arrays, or sooner or later generic routines won't work. If you're writing code that will be called by other programmers, you need to be aware of these variations on the normal usage.
If you decide to use the For Each...Next syntax to access all of the elements of an array, both the variable you use to loop through the elements and the array itself must be variants. In addition, note that you cannot set the values of items in an array using the For Each...Next syntax; it only allows you to retrieve the values from the array. If you want to loop through an array to set its values, you must use the standard For...Next syntax, using a numeric value as the loop counter.
In Access 2000 and later, you can use an array as the return value for a function. Thus, you could rewrite the UCaseArray procedure as follows:
Public Function UCaseArrayFunc(ByVal varValues As Variant) As String() ' Convert the entire passed in array to upper case. Dim intI As Integer Dim astrWorking() As String If IsArray(varValues) Then ReDim astrWorking(LBound(varValues) To UBound(varValues)) For intI = LBound(varValues) To UBound(varValues) astrWorking(intI) = CStr(UCase(varValues(intI))) Next intI UCaseArrayFunc = astrWorking End If End Function
The advantage of this technique is that the function returns a second array and the original array, varValues, is not modified. Unlike the first example, UCaseArray, the array is passed ByVal, which means that UCaseArrayFunc works with a copy of the original array. Any modifications occurring in UCaseArrayFunc will affect only this copy, leaving the original array in the calling procedure unchanged.
Although it's a database product, Access doesn't include a way to sort an array. You need to present sorted arrays in an application, and you can't find a reasonable way to sort them without first saving them to a table. You know you've seen array-sorting methods in other languages. Can you write a sorting routine that executes quickly?
Solution
It's true that Access doesn't provide a built-in sorting mechanism for arrays. Entire volumes in libraries are devoted to the study of various sorting and searching algorithms, but its not necessary to dig too deep for array-sorting methods for Access. Because you'll probably place any large data sets into a table, most arrays in Access aren't very large. Therefore, almost any sort will do. This solution uses a variant of the standard quicksort algorithm. (For more information on various sorting and searching algorithms, consult your computer library. This is a big topic!)
To try the sorting mechanism, load the module named basSortDemo in 07-07.MDB. From the Immediate window, type:
TestSort 6
where the 6 can be any integer between 1 and 20, indicating the number of random integers between 1 and 99 that you want the routine to sort. The sample routine, TestSort, will create the array of integers and send it off to VisSortArray, a special version of the sorting routine acbSortArray that shows what it's doing as it works. Figure 7-11 shows the output from a sample session.
Figure 7-11.The output from a sample run of TestSort
To use this sorting code in your own applications, follow these steps:
Import the module named basSortArray into your application.
Create the array you'd like to sort. This must be an array of variants, but those variants can hold any datatype; this solution uses an array of Integers and the Solution in Recipe 7.8 uses an array of Strings.
Call acbSortArray, passing to it the name of the array you'd like to sort. For example, to sort an array named avarStates, use the following call:
acbSortArray avarStates( )
After the call to acbSortArray, your array will be sorted. Remember that acbSortArray is sorting your array in place: once it's sorted, there's no going back! If you don't want to sort your only copy of the array, make a duplicate first.
Discussion
The quicksort algorithm works by breaking the array into smaller and smaller chunks, sorting each one, until all the chunks are one element long. The acbSortArray procedure calls the main sorting routine, QuickSort, passing to it the array and the start and end points for sorting. The QuickSort routine breaks the array into two chunks, then calls itself twice to sort each of the two halves.
At this point, you might be grumbling about recursive routines and how they use lots of memory. Normally, that's true. This version of the sorting algorithm, however, tries to be conservative about how it uses memory. At each level, it sorts the smaller of the two chunks first. This means that it will have fewer recursive levels: the small chunk will end up containing a single element much more quickly than the large chunk. By always working with the smallest chunk first, this method avoids calling itself more often than it has to.
The code for the QuickSort procedure is:
Private Sub QuickSort(varArray As Variant, _ intLeft As Integer, intRight As Integer) Dim i As Integer Dim j As Integer Dim varTestVal As Variant Dim intMid As Integer
If intLeft < intRight Then intMid = (intLeft + intRight) \ 2 varTestVal = varArray(intMid) i = intLeft j = intRight Do Do While varArray(i) < varTestVal i = i + 1 Loop Do While varArray(j) > varTestVal j = j - 1 Loop If i <= j Then SwapElements varArray( ), i, j i = i + 1 j = j - 1 End If Loop Until i > j ' To optimize the sort, always sort the ' smallest segment first. If j <= intMid Then QuickSort varArray( ), intLeft, j QuickSort varArray( ), i, intRight Else QuickSort varArray( ), i, intRight QuickSort varArray( ), intLeft, j End If End If End Sub
The following are the basic steps of the QuickSort procedure. These steps use intLeft to refer to the beginning sort item and intRight for the ending item:
If intLeft isn't less than intRight, the sort is done.
The sort takes the value in the middle of the subset of the array that's being sorted as the "comparison" value. Its value will be the dividing factor for the two chunks. There are different schools of thought on how to choose the dividing item. This version of the sort uses the item that's physically in the middle of the chosen list of items:
The sort starts from the left, walking along the array until it finds an item that isn't less than the dividing value. This search is guaranteed to stop at the dividing value, which certainly isn't less than itself:
Do While varArray(i) < varTestVal i = i + 1 Loop
The sort starts from the right, walking backward through the array until it finds an item that isn't greater than the dividing value. This search is guaranteed to stop at the dividing value, which certainly isn't more than itself:
Do While varArray(j) > varTestVal j = j - 1 Loop
If the position from Step 3 is less than or equal to the position found in Step 4, the sort swaps the elements at the two positions, then increments the pointer for Step 3 and decrements the pointer for Step 4:
If i <= j Then SwapElements varArray( ), i, j i = i + 1 j = j - 1 End If
The sort repeats Steps 3 through 5 until the pointer from Step 3 is greater than the pointer from Step 4 (i> j ). At this point, every item to the left of the dividing element is less than or equal to it, and everything to the right is greater than or equal to it.
Choosing the smaller partition first, the sort repeats all these steps on each of the subsets to either side of the dividing value, until Step 1 indicates that it's done:
If j <= intMid Then QuickSort varArray( ), intLeft, j QuickSort varArray( ), i, intRight Else QuickSort varArray( ), i, intRight QuickSort varArray( ), intLeft, j End If
There are probably sort algorithms that are simpler than the quicksort algorithm, but for arrays that aren't already sorted, quicksort's speed is hard to beat. (For presorted arrays, it doesn't do as well as some other sorts. But most arrays don't come to the QuickSort subroutine in order.) As it is, the QuickSort subroutine is capable of handling only single-column arrays. If you need to sort multicolumn arrays, you'll need to either modify the code to handle those cases or move the data into a table and let Access do the sorting for you.
See Also
See the next solution for an example of using QuickSort.
You need to present your users with a sorted list of files with a specific filename extension in a particular directory. You found the Dir function, but you can't find a way to get this information into a list box. Is there a way to do this?
Solution
This problem provides the perfect opportunity to use the past three solutions. It involves creating a list-filling callback function, passing arrays as parameters, and sorting an array. In addition, you'll fill that array with a list of files matching a particular criterion, using the Dir function.
Load the form frmTestFillDirList from 07-08.MDB. Enter a file specification into the text box (for example, c:\*.exe). Once you leave the text box (by pressing either Tab or Return), the code attached to the AfterUpdate event will force the list box to requery. When that happens, the list box will fill in with the matching filenames. Figure 7-12 shows the results of a search for c:\\*.*.
To include this functionality in your own applications, follow these steps:
On a form, create a text box and a list box, with properties set as shown in Table 7-6.
Figure 7-12. frmTestFillDirList, searching for *.* in the C:\folder
Table 7-6. Property settings for the controls on the directory list form
Control
Property
Setting
Text box
Name
txtFileSpec
AfterUpdate
[Event Procedure]
List box
Name
lstDirList
RowSourceType
FillList
AfterUpdate
[Event Procedure]
Enter the following code in the text boxs AfterUpdate event procedure. (See the Preface for more information on creating event procedures.) This code forces the list box to requery itself when you enter a value in the text box, and then move to some other control:
Sub txtFileSpec_AfterUpdate ( ) Me.lstDirList.Requery End Sub
Enter the following code in the list box's AfterUpdate event. This is sample code that pops up a message box indicating which file you chose:
Sub lstDirList_AfterUpdate ( ) MsgBox "You chose: " & Me.lstDirList.Value End Sub
Enter the following code into a global module so that it can be called from any form. Though this code would work fine in a form's module, it's general enough that it will serve you best as part of a global module that can be copied from one database to another. This is the function that fills the array of files:
Public Function FillDirList(ByVal strFileSpec As String, _ astrFiles() As String) As Integer ' Given the file specification in strFileSpec, fill in the ' dynamic array passed in avarFiles().
Dim intNumFiles As Integer Dim strTemp As String
On Error GoTo HandleErr intNumFiles = 0
' Set the filespec for the dir() and get the first filename. strTemp = Dir(strFileSpec) Do While Len(strTemp) > 0 intNumFiles = intNumFiles + 1 astrFiles(intNumFiles - 1) = strTemp strTemp = Dir Loop
ExitHere: If intNumFiles > 0 Then ReDim Preserve astrFiles(intNumFiles - 1) acbSortArray astrFiles() End If FillDirList = intNumFiles Exit Function
HandleErr: Select Case Err.Number Case 9 ' The array needs to be resized ' Just add room for 100 more files. ReDim Preserve astrFiles(intNumFiles + 100) Resume Case Else FillDirList = intNumFiles Resume ExitHere End Select End Function
Rather than resizing the array for each matching file name, the Fill-DirList function traps the error that occurs when the array is full, and resizes it 100 slots at a time. Using the Redim Preserve statement is quite expensive in VBA, and you should consider looking for ways to call it as seldom as possible. In this example, the code resizes the array to the correct size once it's done filling in all the file names.
Import basSortArray from 07-08.MDB. This is the same sorting code that we used in the Solution in Recipe 7.7.
Discussion
The list box in this example uses a list-filling callback function, FillList, to supply its data. (See the Solution in Recipe 7.5 for information on callback functions.) Here's the code:
Private Function FillList(ctl As Control, _ varID As Variant, lngRow As Long, lngCol As Long, _ intCode As Integer) Static astrFiles() As String Static intFileCount As Integer
Select Case intCode Case acLBInitialize If Not IsNull(Me.txtFileSpec) Then intFileCount = FillDirList(Me.txtFileSpec, astrFiles()) End If FillList = True
Case acLBOpen FillList = Timer
Case acLBGetRowCount FillList = intFileCount
Case acLBGetValue FillList = astrFiles(lngRow)
Case acLBEnd Erase astrFiles End Select End Function
In FillList's acLBInitialize case, it calls the FillDirList function to fill in the astrFiles array, based on the value in the txtFileSpec text box. FillDirList fills in the array, calling acbSortArray along the way to sort the list of files, and returns the number of files it found. Given that completed array, FillList can return the value from the array that it needs when requested in the acLBGetValue case. It uses the return value from FillDirList, the number of files found, in response to the acLBGetRowCount case.
There's also an interesting situation you should note in the FillList and FillDirList routines. FillList declares a dynamic array, astrFiles, but doesn't give a size because it doesn't yet know the number of files that will be found. FillList passes the array off to FillDirList, which adds filenames to the array based on the file specification until it doesn't find any more matches. FillDirList returns the number of matching filenames, but it also has the side effect of having set the array's size and filled it in. Here's the code that does the work. This code fragment uses the ReDim Preserve keywords to resize the array every time it finds a matching filename:
' Set the filespec for the dir() and get the first filename. strTemp = Dir(strFileSpec) Do While Len(strTemp) > 0 intNumFiles = intNumFiles + 1 astrFiles(intNumFiles - 1) = strTemp strTemp = Dir Loop
FillDirList uses the Dir function to create the list of files. This function is unusual in that you call it multiple times. The first time you call it, you send it the file specification you're trying to match, and Dir returns the first matching filename. If it returns a nonempty value, you continue to call it, with no parameters, until it does return an empty value. Each time you call Dir, it returns the next matching filename.
Once FillDirList has finished retrieving the list of filenames, it sorts the names in the array. Its return value is the number of files it found. The following code shows how this works:
If intNumFiles > 0 Then ReDim Preserve astrFiles(intNumFiles - 1) acbSortArray astrFiles() End If FillDirList = intNumFiles
Note that when Access calls the list-filling callback function, values for the lngRow and lngCol parameters are always zero-based. Therefore, when you use arrays within callback functions, you should always consider using zero-based arrays to hold the data you'll display in the control. If you don't, you'll always be dealing with "off by one" errors. Using a zero-based array will mean that the row values (sent to your code in lngRow) will match your array indices.
You don't understand how to get and set property values in Access. It seems as if there are different kinds of properties, and what works for one object and property doesn't work for another. Is there some way to settle this once and for all?
Solution
There really are two kinds of properties for objects in Access. Built-in properties are those that always exist for an object, and user-defined properties are properties that you or Access creates for an object when requested. The syntax for referring to each type is different, but this solution provides a method that works for either type. This solution uses the user-defined Description property as an example, but the techniques will work just as well for any other property. The interesting part of this solution is that the Description property is not a built-in property, and attempting to set or retrieve this property using the standard object.property syntax will fail.
This solution provides a sample form, which is useful only for demonstrating the technique. The real power of the solution comes from the module, basHandleProperties, which provides procedures you can use to set and get any kind of property. To try out the sample form shown in Figure 7-13, load and run frmTestProperties from 07-09.MDB. Choose a table from the list of tables, and notice the Description property shown in the text box below the list. If you choose a field from the list of fields, you'll also see the description for that field in the text box below the list. You can enter new text into the two text boxes, and the code attached to the AfterUpdate event of either text box will write the text back to the Description property of the selected table or field.
Figure 7-13. frmTestProperties lets you set and get the Description property of any table or field
The sample form uses two functions from basHandleProperties, as shown in Table 7-7. These functions allow you to get or set any property of any object, as long as the object either already supports the property you're working with or allows you to create new properties to add the property if it doesn't already exist.
Table 7-7. Using the acbGetProperty and acbSetProperty functions
Function name
Usage
Parameters
Returns
acbGetProperty
Retrieve the value of the specified property of the specified object.
obj As Object: a reference to any existing object.
The value of the requested property, or Null if that property or object doesn’t exist.
strProperty As String: the name of the property to retrieve.
acbSetProperty
Set the value of the specified property of the specified object.
obj As Object: a reference to any existing object.
The old value of the property, if it existed, or Null otherwise.
strProperty As String: the name of the property to set.
varValue As Variant: the value of the property;
varPropType As Variant (optional): the data type of the new property (if the code has to create it). One of dbBoolean, dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate, dbText, dbLongBinary, dbMemo, or dbGUID. If you skip this, Access will use dbText.
The only objects to which you can add properties are databases, tables, queries, fields, indexes, and relations. Attempts to add a new property to any other kind of object will fail.
To use these new functions in your own applications, follow these steps:
Import basHandleProperties into your application.
To set a property, call acbSetProperty. This function returns the old value of the property. For example:
Dim db As DAO.Database Dim varOldDescription As Variant
Set db = CurrentDb( ) varOldDescription = acbSetProperty(db, "Description", "Sample Database") If Not IsNull(varOldDescription) Then MsgBox "The old Description was: " & varOldDescription End If
To get the value of a property, call acbGetProperty. For example:
Dim db As DAO.Database Dim varDescription As Variant
Set db = CurrentDb( ) varDescription = acbGetProperty(db, "Description") If Not IsNull(varDescription) Then MsgBox "The database description is: " & varDescription End If
Discussion
Access provides two types of properties: built-in and user-defined. Built-in properties always exist and are part of the definition of the object. For example, the Name and Type properties are crucial for the existence of most objects. These are built-in properties. On the other hand, the Jet engine allows you to create new properties and add them to the Properties collection for all the objects it supports, including TableDefs, QueryDefs, Indexes, Fields, Relations, and Containers. These are user-defined properties.
In addition, Access itself, as a client of the Jet engine, creates several properties for you. For example, when you right-click on an object in the Database Explorer and choose Properties from the floating menu, Access allows you to specify the Description for the object. That Description property doesn't exist until you request that Access create it, using that dialog or in your own VBA code. The same goes for the Caption, ValidationRule, and DefaultValue properties of fields: those properties don't exist until you request that Access create them for you.
If you attempt to retrieve or set the value of a property that doesn't yet exist, Access will trigger a runtime error. Your code must be ready to deal with this problem. In addition, you may be used to working with built-in properties, to which you can refer using the simple object.property syntax. This syntax works only for built-in properties. For user-defined (and Access-created user-defined) properties, you must refer to the property using an explicit reference to the Properties collection that contains it. For example, to set the Format property of the City field within tblCustomers, you'll need an expression like this (and this expression will fail with a runtime error if the Format property hasn't yet been set):
Because you can always refer to any property using an explicit reference to the Properties collection, you can simplify your code, and ensure that all property references work, by using the same syntax for built-in and user-defined properties. For example, field objects support the AllowZeroLength property as a built-in property. Therefore, this reference will work:
This ability to refer to built-in and user-defined properties using the same syntax is the secret of the code presented in this solution.
To create a new property, you must follow these three steps:
Create a new property object, using the CreateProperty method of an existing object.
Set the properties of this new property, including its name, type, and default value (you can merge this step with the previous step by supplying the information when you call CreateProperty).
Append the new property to the Properties collection of the host object. For example, to add a Description property to the current database, you might write code like this:
To combine Steps 1 and 2, you could set the properties of the new property at the time you create it:
' Steps 1 and 2 Set prp = db.CreateProperty("Description", dbText, "Sample Database")
' Step 3 db.Properties.Append prp
Once you've followed these steps, you should be able to retrieve the database's Description property with a statement like this (note that you must use the explicit reference to the Properties collection in this case, because Description is a user-defined property):
Debug.Print CurrentDb.Properties!Description
To relieve you from worrying about the differences between user-defined and built-in properties and whether or not a property already exists for a given object, we've provided the acbGetProperty and acbSetProperty functions.
The acbGetProperty function is the simpler of the two: it attempts to retrieve the requested property. acbGetProperty may fail for two reasons: the object itself doesn't exist, or the property you've tried to retrieve doesn't exist (errors acbcErrNotInCollection and acbcErrPropertyNotFound, respectively). If either of these errors occurs, the function returns Null. If any other error occurs, the function alerts you with a message box before returning Null. If no error occurs, the function returns the value of the requested property. For an example of calling acbGetProperty, see Section 7.9.2 and 07-09.MDB.
The source code for acbGetProperty is:
Public Function acbGetProperty(obj As Object, _ strProperty As String) As Variant ' Retrieve property for an object. ' Return the value if found, or Null if not.
On Error GoTo HandleErr
acbGetProperty = obj.Properties(strProperty)
ExitHere: Exit Function
HandleErr: Select Case Err.Number Case 3265, 3270 ' Not in collection, not found. ' Do nothing! Case Else MsgBox Err.Number & ": " & Err.Description, , "acbGetProperty" End Select acbGetProperty = Null Resume ExitHere End Function
The acbSetProperty function is more interesting. It attempts to set the value of the property you pass to it. This function has several interesting characteristics:
If you ask it to set a property that doesn't currently exist, it attempts to create that property and then sets its value.
The data type is declared optional, using the DataTypeEnum enumerated type, with dbText as the default value. If you don't tell it what the data type of the new property is to be (i.e., if you leave that parameter blank), the code will use the dbText type by default.
The function returns the old value of the property, if there was one, so you can store it away and perhaps reset it once you're done with your application.
To make sure the code will work with either user-defined or built-in properties, the code uses an explicit reference to the Properties collection.
To tell if it needs to try to create the property, the function traps the acbcErrPropertyNotFound error condition (error 3270); if that error occurs, it uses the CreateProperty method to try to create the necessary property.
If you try to assign an invalid property value, Access triggers the acbcErrDataTypeConversion error condition (error 3421). In that case, there's not much acbSetProperty can do besides alerting you to that fact and returning Null.
The source code for acbSetProperty is:
Public Function acbSetProperty( _ obj As Object, strProperty As String, varValue As Variant, _ Optional propType As DataTypeEnum = dbText)
' Set the value of a property. On Error GoTo HandleErr
Dim varOldValue As Variant
' This'll fail if the property doesn't exist. varOldValue = obj.Properties(strProperty) obj.Properties(strProperty) = varValue acbSetProperty = varOldValue
ExitHere: Exit Function
HandleErr: Select Case Err.Number Case 3270 ' Property not found ' If the property wasn't there, try to create it. If acbCreateProperty(obj, strProperty, varValue, propType) Then Resume Next End If Case 3421 ' Data type conversion error MsgBox "Invalid data type!", vbExclamation, "acbSetProperty" Case Else MsgBox Err.Number & ": " & Err.Description, , "acbSetProperty" End Select acbSetProperty = Null Resume ExitHere End Function
Only objects that are maintained by the Jet engine allow you to create new properties. That is, you can add properties to the Properties collections of Database, TableDef, QueryDef, Index, Field, Relation, and Container objects. You won't be able to add new properties to any object that Access controls, such as forms, reports, and controls. If you attempt to use acbSetProperty to set a user-defined property for an invalid object, the function will return Null. You can, however, use acbSetProperty and acbGetProperty with any Access object, as long as you confine yourself to built-in properties for those objects that don't support user-defined properties. For example, this code fragment will work as long as frmTestProperties is currently open:
If IsNull(acbSetProperty(Forms("frmTestProperties"), "Caption", _ "Test Properties")) Then MsgBox "Unable to set the property!" End If
User-defined properties are persistent from session to session. That is, they are saved in the TableDef along with the built-in and Access-defined properties. You can, however, delete a user-defined property using the Delete method on the property's parent collection. For example, you could delete the user-defined property defined earlier using the following statement:
You create and delete objects as your application runs. At some point, you need to be able to tell whether an object exists and make decisions based on that fact. But you can't find a function in Access that will tell you if a specific object already exists. Are you missing something? This ought to be a basic part of the product!
Solution
You haven't missed anything: Access really doesn't supply a simple method of determining if a specific object already exists. On the other hand, this is really quite simple, as long as you understand two important concepts: Accesss support for DAO Container objects, and the ways you can use error handling to retrieve information. This solution uses these two subjects to provide a function you can call to check for the existence of any object.
Load and run frmTestExist from 07-10.MDB. This form, shown in Figure 7-14, lets you specify an object name and its type and then tells you whether that object exists. Certainly, you wouldn't use this form as-is in any application--its purpose is to demonstrate the acbDoesObjExist function in basExists (07-10.MDB). To make your exploration of frmTestExist easier, Table 7-8 lists the objects that exist in 07-10. MDB. Try entering names that do and don't exist, and get the types right and wrong, to convince yourself that the acbDoesObjExist function does its job correctly.
Figure 7-14. frmTestExist lets you check for the existence of any object in the current database
Table 7-8. The sample objects in 07-10.MDB
Object name
Object type
tblTest
Table
qryTest
Query
frmTest
Form
frmTestExist
Form
basExists
Module
Follow these steps to use acbDoesObjExist in your own applications:
Import the module basExists from 07-10.MDB. This module contains the acbDoesObjExist function.
To check for the existence of any object, call acbDoesObjExist, passing to it the name of the object to check for and a value from the AcObjectType enumeration indicating the object's type. The type parameter must be chosen from the values acTable, acQuery, acForm, acReport, acMacro, or acModule. For example, to check for the existence of a table named "Customers", call acbDoesObjExist like this:
If acbDoesObjExist("Customers", acTable) Then ' You know the table exists. Else MsgBox "The table 'Customers' doesn't exist!" End If
Discussion
The acbDoesObjExist function, shown in full here, checks for the existence of an object by attempting to retrieve that object's Name property. Because every object that exists exposes a Name property, this action can't fail unless the object doesn't exist. In skeleton format, the code works like this:
Dim strName As String On Error Goto acbDoesObjExist_Err
That is, the code sets up an error handler and then attempts to retrieve the Name property of the requested object. If it succeeds, the code falls through, sets the return value to True, and returns. If it triggers an error, the procedure can be assured that the object doesn't exist, and it will return False.
The only other issue is how to convert a string containing the name of the object and an integer containing its type to a real object reference. This is where the Jet engine's Container objects come in handy. The Container collections, supplied by Access so the Jet engine can support security for all the Access objects, contain collections of Document objects (one for each saved object in your database). The Containers collection contains collections named Tables, Forms, Reports, Scripts (that's macros for us users!), and Modules. Except for tables and queries, the code checks in those collections of documents, looking for the document whose name you've supplied. For tables and queries, it's simpler to use the TableDefs and QueryDefs collections directly. Access lumps tables and queries together in the Tables container, but keeps them separate in the TableDefs and QueryDefs collections. If the code looked in the Tables container, it would have to take an extra step to distinguish tables from queries; that step isn't necessary if it uses the collections instead.
The code for acbDoesObjExist is as follows:
Public Function acbDoesObjExist( _ strObj As String, objectType As AcObjectType) Dim db As DAO.Database Dim strCon As String Dim strName As String
On Error GoTo HandleErr
Set db = CurrentDb() Select Case objectType Case acTable strName = db.TableDefs(strObj).Name Case acQuery strName = db.QueryDefs(strObj).Name Case acForm, acReport, acMacro, acModule Select Case objectType Case acForm strCon = "Forms" Case acReport strCon = "Reports" Case acMacro strCon = "Scripts" Case acModule strCon = "Modules" End Select strName = db.Containers(strCon).Documents(strObj).Name End Select acbDoesObjExist = True
ExitHere: Exit Function HandleErr: acbDoesObjExist = False Resume ExitHere End Function
Note that in the Select Case statement, the code first checks to see if you're asking about a table or a query. If so, it looks in the appropriate collection:
Select Case objectType Case acTable strName = db.TableDefs(strObj).Name Case acQuery strName = db.QueryDefs(strObj).Name . . . End Select
If not, it assigns to strCon the name of the container it will need and then attempts to retrieve the Name property of the particular document within the selected container:
Case acForm, acReport, acMacro, acModule Select Case objectType Case acForm strCon = "Forms" Case acReport strCon = "Reports" Case acMacro strCon = "Scripts" Case acModule strCon = "Modules" End Select strName = db.Containers(strCon).Documents(strObj).Name
See Also
If you haven't done much investigation of DAO in Access, you may find it useful to study the appropriate chapters in the Building Applications manual that ships with Access. Though complete coverage of DAO is beyond the scope of this book, there are several examples using DAO in other chapters, especially Chapter 4 and Chapter 6. In addition, DAO Object Model: The Definitive Reference, by Helen Feddema (O'Reilly), provides complete documentation of the DAO object model.
Microsoft Access 
VBA Details
