This month, Ken answers four questions culled from msnews.microsoft.com (Microsoft's support newsgroups on the Internet). First, he digs into the undocumented _AccessProperty object. Then, he takes on a challenge: removing the Most Recently Used file list from the File menu in Access 97. Next, he shows how to retrieve just the path of the current database, and finally, how to highlight a single control on a continuous form.
Unlike most Office products, Access conveniently supplies a Properties collection for most of its objects. And that's really useful. But I need some way to find out what kind of property any given property is -- for example, I'm writing an add-in that needs to clear out all the event property values for a given object, but leave the rest alone. At this point, I'm doing some ugly stuff to find out which ones are events, and I keep thinking that there has to be some way to retrieve this information.
Interesting. A reader, Jeff Killeen, recently sent a piece of mail suggesting a solution to this problem. And even more interesting: I had battled this same problem about a year ago when writing an add-in for Smart Access. That add-in could walk through a form's class module and, for the events that had associated code in the module, set the event property to "[Event Procedure]". You might feel this is unnecessary, but sometimes Access gets confused as to when it should display the "[Event Procedure]" in a form's property list. This confusion can prevent Access from subsequently calling the appropriate event code. Fortunately, each new version of Access has gotten smarter about this.
The solution Jeff suggested uses the _AccessProperty object, a hidden object found in both Access 95 and 97, which makes it possible to retrieve the information you need. Access versions 2.0 through 97 provided a Property object, but they didn't include a Category property. On the other hand, the _AccessProperty object (which I'm assuming is based on the standard Property object) does have a Category property. This added property tells you which type of property you've got. Here are its possible values:
Constant Value Property page
acPropCatData 2 Data
acPropCatEvent 4 Event
acPropCatLayout 1 Format
acPropCatNA 0 (No page)
acPropCatOther 8 Other
These values aren't the only ones, either. Two form properties (RecordsetType and RecordLocks) gave me a value of 66 for the Category property of their _AccessProperty object. Of course, because this object and its properties are undocumented, we have no way of knowing what's really going on under the covers. Experiment, if you're interested, using the code shown here to get you started.
So, how can you use the _AccessProperty object? I'm pretty sure that the only time you'd need to use it is when you need to enumerate through the Properties collection of an object. Since Access 2.0, you've been able to write code like this to work through all the properties of a TableDef (or any other DAO object):
Dim prp As Property
For Each prp In obj.Properties
' Do something with each property.
You can also use the similar, expanded _AccessProperty object in place of the Property object. When you do, you'll get three new "properties of a property" that you couldn't get with Property object. One small problem: VBA doesn't allow you to use object names or types that begin with an underscore. To work around this, you'll need to surround the reference to the _AccessProperty type with brackets. Therefore, your code will look like this:
Dim prp As [_AccessProperty]
For Each prp In obj.Properties
' Do something with each property.
' You can use the properties you know
' from the Property object and
' the new properties of the _AccessProperty
The standard Property object (provided by DAO for Access) supports these five properties:
Inherited Returns whether the property is inherited from an underlying object.
Name The name of the property.
Properties Returns a collection of properties of this property.
Type The data type for the property.
Value Sets or returns the value of the property.
The _AccessProperty object adds these additional properties:
Application Returns a reference to the parent application.
Category Returns the property's category.
Parent Returns a reference to the parent object.
Therefore, as long as you're interested in the properties of an Access object (a form or a control, for example), you can use the _AccessProperty object in place of DAO's Property object.
How are you going to use this information? Most likely, the only property of the _AccessProperty object that's going to be very interesting is that Category property. In the case of the add-in I was trying to write, I needed to remove "[Event Procedure]" from all (and just) the event properties. I couldn't just look for "[Event Procedure]" in a property value, because someone, somewhere, might put "[Event Procedure]" as a caption on a button! As a result, I needed a way to identify the event properties. With the _AccessProperty object, the code might look like this:
Dim prp As [_AccessProperty]
For Each prp In Me.Properties
If prp.Category = acPropCatEvent Then
If prp.Value = "[Event Procedure]" Then
prp.Value = ""
If you're writing add-ins, I can think of other uses for the _AccessProperty object. For instance, you could combine this technique with the information you can get from undocumented calls to Application.GetOption. (GetOption can be used to find out which page of the property sheet the user has selected.)
By the way, perhaps you're interested in how I dug all this up after I received Jeff's original pointer. The Object Browser in Access 97 (and all the other VBA 5.0 hosts) allows you to view otherwise hidden objects. To see these hidden objects, open the object browser (press F2 from any module window), and then right click. From the pop-up menu, choose Show Hidden Members. If you're in Access, you can then search on acPropCat to find all the constants, or _AccessProperty to find the object itself. Once you've gotten that far, the rest is just speculation because there's no help available for these items in the standard help file.
Thanks to Jeff Killeen for pointing out this interesting and useful object. It certainly fulfills a need I've had. This undocumented stuff isn't for everyone, that's for sure. But if you're interested in wringing the most from Access 95 and 97, you may find this as helpful as I did.
I want to be able to get rid of the Most Recently Used (MRU) list in the File menu when running Access 97. I'm running the Access application object created in an in-process DLL. I've tried setting the CommandBar.Control Visible property to False and also set the Caption to a "" string, but both result in an OLE error. How can I get rid of it using Visual Basic Automation? Diane Carter
Interesting problem! I spent a good deal of time experimenting with CommandBars for a book chapter, and thought I'd tried most of the things that people would want to do. But I hadn't tried this. I thought it'd be informative to document what didn't work in my quest for a solution to this problem.
You're getting errors because Access won't allow you to do any of the things you're trying. You can't set the Visible property to False, nor can you change the caption for the MRU list items on the menus. I tried and duplicated the errors you received.
Then I had a brilliant idea (it wasn't actually so brilliant, because it doesn't work). I checked out the ID property of each of the MRU entries. Each has an ID of 831. More importantly, those are the only CommandBarControl objects with that particular ID value, so I figured I'd just remove all the items with an ID of 831 from the File menu. No luck. I tried making them invisible. Failure again. It seems that Access just doesn't want you to do this using Automation.
With no reasonable alternatives left, I tried something totally bizarre: I removed the File menu altogether and then reset the main menu. The code looks like this:
With CommandBars("Menu Bar")
Of course, there's a terrible side effect to using this code: it completely resets your main menu. If you've made other changes to the main menu bar, or even if your users have made changes, they'll all be lost. If this doesn't bother you, feel free to use this technique in order to remove the Most Recently Used file list from the Access File menu.
I need to import some delimited text files into my Access documents. I'll put the Access file in different locations, but I'll always want to import a text file that is in the same directory as the Access file. Therefore, I need some way to find out the path of the current database, and just the path. Once I have that, I can import the documents I need. Can you help?
Normally, I'd just tell you to use the Name property of the current database: it returns the full path and filename for the database. But you want to remove the filename portion, and that makes the problem a bit more complex. There are a number of ways to retrieve just the path name, given a full path and filename. I've often done it by walking backwards through the filename, searching for the final backslash, and working from there, for instance. But I recently came across a quick and elegant solution that requires a tiny bit of VBA code. I published the concept in a Smart Access tip a few months back, but the context was different -- I hadn't even considered applying the idea to retrieving just the path portion of a full filename at the time.
If you use the Name property of the current database, you'll get a full path and filename back, like this:
But you want to retrieve just the path portion, so you can tack on a different file name for import:
It turns out that the Dir function can come to your aid. The Dir function returns the first filename (and just the filename) that matches a given file specification, which can be a full pathname. In other words, for an existing file, Dir converts a full pathname to just a filename. If you call Dir like this:
it'll return this:
You can then use this string to find the path portion, by subtracting the length of the filename from the length of the full path. One caveat: this technique works only if the file you're investigating actually exists. Of course, if you use the Name property of the current database, you're guaranteed that the file exists, right?
Here's a function that will do what you need:
Function GetDBPath () As String
Dim strTemp As String
Dim strFullName As String
Dim db As Database
Set db = CurrentDB()
' In Access 95/97, you can dispense with
' the database object and use the following:
' strFullName = Currentdb.Name
strFullName = db.Name
' Get just the filename.
strTemp = Dir(strFullName)
' Now subtract the filename from the full path.
GetDBPath = Left(strFullName, Len(strFullName) _
You can use GetDBPath() anywhere you need the path of the current database.
I'm building a continuous form that has 109 records displayed. I want to be able to click on a control (a field in the record) and color the control's background red. I'm able to color the background, but the entire column turns red. Is there something I can do so that only the control that has the focus has a red background? Doug Hinnant,
I'd love to say I thought of this solution myself, but I didn't. The question was posted on Microsoft's support newsgroup (msnews.microsoft.com) some time back. Before I could get back to say that Access doesn't support specific formatting for individual rows in tabular view, Jeffrey Cumpsty (email@example.com) produced this solution:
* Set the background color of the form's Detail section to the background color you want. This is the color that will display for all the controls except the one with the focus.
* Select all the controls on your form and set their background color to Red (or whatever color you want to highlight with).
* With all the controls still selected, set the BackStyle property to Transparent.
That's it! Now when you display your form all the controls will have the same background color as the form's detail section. However, when a control gets the focus, its BackStyle will switch from transparent to reveal the control's background color.
Thanks to Doug for supplying the question, and to Jeffrey for supplying the elegant solution.
I've been writing the Access Answers column for the past 24 months or so, and I've decided that it's time to move on to new adventures. I'll still be using and investigating Access, that's for sure. Although the questions never run dry, I believe it's time to let someone else take over this column. Thanks to all who've supplied both questions and answers, and to those who've contacted me via e-mail. I do hope that I've managed to help some people in their Access projects with the information in this column, and I feel sure that my successor will be just as informative.
Read about the download GETZ45.EXE on this page