ActiveX Controls and Forms

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > May-1999 >

ActiveX Controls and Forms

Ken Getz            
 
In this month's "Access Answers" column, Ken takes on a series of questions he's received lately, all dealing with using ActiveX controls on Access forms.
 
I'm trying to use some ActiveX controls, but I just can't get them to work properly in Access. For example, I wanted to use the ActiveMovie control on an Access form, so I used the Insert | ActiveX Control... menu item to select it. When I try to run the form, I get the message "There is no object in this control," and I'm stuck. The controls that I'm trying to use work fine in Visual Basic 6.0. What's the problem here? Why can't Access forms use these controls?
 
I still clearly remember getting word in the last minute of the beta program for Access 2.0 that Access would support ActiveX controls. Of course, at the time there weren't any actually available (and they were still called OLE controls back in those days), and the rest of the world was using VBX controls for VB, but still, it sure was exciting. Unfortunately, Access's support for ActiveX controls doesn't appear to have changed much since then, except for the switch to 32-bit in the ill-fated Access 95.
 
It's true. Access forms have a really serious deficiency when it comes to handling ActiveX controls. As a matter of fact, Access is so sad in this area that most ActiveX control vendors, with the exception of FMS, Inc., haven't even attempted to supply ActiveX controls for Access.
 
The problem is in the communication between applications. Although it might not seem like it, an ActiveX control is a separate application running within Access. When you place an ActiveX control on an Access form, Access first places a kind of container onto the form, and that container hosts the actual control. The container and the control must be in constant communication and must speak the same "language" (that is, they must support the correct COM interfaces, a topic for some other day).
 
Unfortunately, and for reasons unknown to me, the Access ActiveX control container is, well, somewhat "backwards." Perhaps it got dropped on its head early in life, or perhaps its developers were ingesting unfortunate substances during gestation (okay, is that enough mixed metaphors?), but it's not as talented as other applications' ActiveX containers. Access's ActiveX control container has never been able to support the ISimpleFrame COM interface, for instance, which is why Access can't support ActiveX controls that contain other ActiveX controls. Access also has never been able to support ActiveX controls that can be bound to complex data sources (data bound grid controls, for example).
 
But the real problem, as I see it, is all of the other controls that ought to work in Access, but don't. As an example, look at the Web Browser control. The Web Browser control is installed with Internet Explorer, and, when Internet Explorer 3 was current, you could plop a Web Browser control down onto an Access form and everyone was happy. Try it with IE4 or IE5, and you get the dreaded "There is no object in this control." The ActiveMovie control is just another example of Access's inability to host a large number of controls (as are any of a number of other controls you might like to use, many from Microsoft). They're just not happening.
 
Along the way, however, someone suggested a workaround to me that causes a number of controls to work in Access (if you are this person, please e-mail me. I'd like to be able to credit you for this tip, but can't remember who sent it.) Turns out that if you cut a recalcitrant control to the clipboard, and then paste it right back on, it might magically work. Try it out yourself with the Web Browser control, if you have IE4 or IE5 installed:
 
1. Create a new form.
 
2. Use the Insert | ActiveX Control menu item to add an instance of the Microsoft Web Browser control to your form. Set its Name property to webDemo.
 
3. Add a CommandButton to the form. Set its name to cmdDemo.
 
4. Add this code so that your CommandButton's Click event procedure looks like the following (choose some URL that you can reach):
 

Private Sub cmdDemo_Click()

 Me.webDemo.Object.Navigate _

      "http://www.microsoft.com"

End Sub

 
 
5. Run the form. Not only will get you the irritating alert on the way into the form, but to add insult to injury, you get it on the way back to Design view, as well.
 
6. Return to Design view and delete the Web Browser control (you must start with a control that hasn't been used in Form view for this technique to work).
 
7. Insert another Web Browser control, and, again, set its name to webDemo.
 
8. Resize the control to the size you'd like it to appear on your form (better do this now -- it's the only chance you'll get to resize the control).
 
9. With the Web Browser control selected, press Ctrl-X to cut it to the clipboard, and then press Ctrl-C to paste it back onto the form.
 
10. Run your form again, click the button, and everything should work fine. It's magic!
 
To be completely honest, I have no idea why this works. Or if it always works. Or if it works in every situation (rumor reached me that this doesn't work in an application distributed with the Access runtime, but I've not been able to test that, and I can't imagine why it wouldn't work).
 
One thing to note: Once that Web Browser control gets placed onto the form in a condition in which it will work for you, you won't be able to resize the control again. No attempt I've made has budged it. In code at runtime or by hand at design time, the control always appears at the size it had when you first cut it to the clipboard. (I know, you tried resizing after it had worked by cutting and pasting again, hoping that the control would change its size. Nope, it doesn't work.)
 
The ActiveMovie control is another story. It, too, gives you the "There is no object in this control" message when you first try to use it. If you just re-open the form, however, it works fine. Feel free to try the cut and paste method -- if you do this before you ever use the control, you won't ever see that message. But even if you don't cut and paste, the second time you open the form, it works fine. As I discuss later in the article, you can even resize it.
 
At least, this is the behavior I saw as I was playing with the control to write these answers. The control might work completely differently on your machine. Because it's all seemingly random and somehow magical, I can't guarantee that the behavior will be the same for you. Or your users.
 
I'm using a bunch of ActiveX controls on my forms, and I'm sick and tired of the fact that I have to refer to those controls, from code, using the Object property of the control. Why can't I just refer to the control itself, like I do in Visual Basic? It wouldn't be so bad, except that you don't get any help when referring to properties and methods of the control. I have to look everything up in the Object Browser or in online help. Is there something I'm missing?
 
Peter Vogel discussed this in his "Access Answers" column in the May 1998 issue of Smart Access (see "Printers, Disks, and Controls"). I'm going to do a brief review of Peter's material, adding some detail, and then go on to build a more complete solution.
 
When you place an ActiveX control onto an Access form, there are really two "pieces" involved -- there's the ActiveX control container, and there's the object within that container (the ActiveX control itself). Access provides the container, which is the same for all ActiveX controls placed onto Access forms. This container provides properties like Width, Height, Visible, and Enabled, which all of the controls within Access share. The ActiveX control itself exposes its normal set of properties, specific to the control itself, that are provided by the developer who created the control. Access does a reasonably good job at hiding this dichotomy from you at design time by merging the properties provided by the container and the control into the Access Properties window.
 
The problem is that Access doesn't do a good job at merging these two objects together when you want to write code that manipulates the ActiveX control. If you want to set or retrieve the value of a property provided by the control (not by the container), you must refer to the Object property of the container. This means that all of your references to the control from code are late bound, and you don't get the inestimable benefit of IntelliSense providing drop-down lists of available properties and methods. I sure can't write code without IntelliSense anymore! On the other hand, if you want to modify a property of the container (such as Width, Height, and so on), you just refer to what you thought was the control itself, which is, in fact, the container that the control is in.
 
Given this problem working with ActiveX controls on Access forms, I decided to explore and see whether there was some way to make it easier to work with the controls from VBA code. I started with the solution that Peter provided. This consists of declaring, in each procedure where I wanted to work with the control, a variable of the same type as the control, and setting the variable equal to the actual control by accessing the container's object property. This technique works great, and I ended with code like the following (this code uses the ActiveMovie control from the previous question):
 

Private Sub Command1_Click()

   Dim amv As ActiveMovie

   Set amv = Me.amvTest.Object

   amv.FileName = "X:\avi\welcome1.avi"

   amv.AboutBox

End Sub

 
 
As you can see, the code in this procedure uses the variable amv and, as long as I use amv, I get the nice IntelliSense drop-down lists (and the benefit of early binding, as well).
 
After writing a few procedures this way, I noticed that I was declaring the same variable over and over again and setting it to refer to the real control in each procedure. It made a lot more sense to declare amv at the module level instead, and so I changed the code to work that way. That meant, of course, that I had to set the variable to refer to the control in the form's Load event procedure, and set it to Nothing in the form's Unload event procedure so that the variable was properly initialized when I used it elsewhere in the form. By now, I'd also determined that the only place that I could resize the ActiveMovie control was from the ReadyStateChange event of the control. So now my code looked like the following code (note that to change the size of the control, I had to refer to the container, amvTest, and not use the variable amv that refers to the control itself):
 

Private amv As ActiveMovie

 

Private Sub amvTest_ReadyStateChange _(

 ByVal ReadyState As Long)

 ' If the movie is loaded, it's time to go!

If ReadyState = amvComplete Then

' Resize the form to display the whole image.

   Me.Detail.Height = amvTest.Top + _

                    amvTest.Height

   Me.Width = (amvTest.Left * 2) + _

                    amvTest.Width

   DoCmd.RunCommand acCmdSizeToFitForm

   ' Play the movie.

   amv.Run

End If

End Sub

 

Private Sub Command1_Click()

   Dim amv As ActiveMovie

   Set amv = Me.amvTest.Object

   amv.FileName = "X:\avi\welcome1.avi"

End Sub

 

Private Sub Form_Unload(Cancel As Integer)

   On Error Resume Next

   amv.Stop

   Set amv = Nothing

End Sub

 
 
And this was much better. I now had one variable throughout the entire form module, and IntelliSense was happening. But one more thing was bothering me: Why did I have to refer to amv, my own variable, to use properties and methods, but the container, amvTest, to refer to events? This seemed like just one more confusing issue. To solve the problem, I decided to add the WithEvents keyword to the declaration of the variable (you'll need Access 97 to use WithEvents). With amv declared using WithEvents, the code would receive events associated with the control directly, instead of getting them through the control container. These final modifications made the code look like this:
 

Private WithEvents amv As ActiveMovie

 

Private Sub amv_ReadyStateChange _

 (ByVal ReadyState As Long)

   ' If the movie is loaded, it's time to go!

   If ReadyState = amvComplete Then

       

' code removed here.

 
 
To see this form working, check out frmAMDemo in the sample database, available in the accompanying Download file. You'll need to modify the path of the displayed movie to some AVI file you have lying around (and, yes, you can use the Dancing Baby, if you must).
 
I don't know about you, but I'm just plain sick and tired of using those complicated list-filling callback functions to fill list and combo boxes with data that doesn't come from a table. I mean, all I really want to be able to do is add and delete items that aren't in a table at runtime. Doing this using an Access list box is nearly impossible without somehow writing data to a table and then manipulating it there. Do you have a better solution?
 
Yes, the Access list-filling callback mechanism is an awkward beast. You would have thought by now that if a list or combo box wasn't bound to a live data source, the Access designers would allow you to manipulate the list by adding and removing items programmatically. But no, that hasn't happened. Like you, I've gotten sick and tired of writing those callback functions and have declared a personal moratorium.
 
What's my solution, then (besides jumping ship and writing applications in VB)? For many situations, the MSForms package of controls might be the answer. This group of controls (which includes CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls) are all available to use on your Access forms. MSForms is a package provided with all VBA host applications, including VB and Access, that have their own forms packages. You can easily distribute these controls with your Access applications, and they have interesting features that Access controls don't.
 
For example, neat features of the MSForms list box control include:

You can set the MatchEntry property to FirstLetter or Complete, allowing users to type a single letter or a full word to match items in the list box.

You can set the ListStyle property to Normal, and get a normal list box, or Option, and get option buttons or check boxes next to the items (depending on the value in the MultiSelect property).

You can assign an array directly into the List property (for a single-dimensional array), or the Column property (for a multi-dimensional array). The ability to assign a multi-dimensional array is useful when you've loaded data from a recordset into an array, using the recordset's GetRows method, as I'll demonstrate later in the article.

 
What can't you do with this list box? Well, for one, you can't easily bind it to a table, query, or SQL string in Access. If you want to do those things, use a standard Access list box. If you want to be able to easily add or remove an item from a list, or if you want to use some of the neat features listed previously, use the MSForms list box.
 
Once you've added the MSForms 2.0 list box to your form, you can work with it just like any other ActiveX control. You can use the technique I mentioned in the previous answer. In the sample code, I've declared a WithEvents module-level variable to refer to the control, so I only have to use one name for the control, both in setting its properties and dealing with its events. The sample form frmDemoListBox, available in the accompanying Download file, allows you to add and delete items without updating a table (you delete items by selecting them in the list and pressing the Delete key). The following code does the trick:
 

Private WithEvents lst As MSForms.ListBox

 

Private Sub cmdAdd_Click()

   lst.AddItem txtItem

   txtItem.SetFocus

End Sub

 

Private Sub Form_Load()

   Set lst = Me.lstDemo.Object

End Sub

 

Private Sub Form_Unload(Cancel As Integer)

   Set lst = Nothing

End Sub

 

Private Sub lst_KeyDown( _

ByVal KeyCode As MSForms.ReturnInteger, _

ByVal Shift As Integer)

   If KeyCode = vbKeyDelete Then

       If lst.ListIndex >= 0 Then

           lst.RemoveItem lst.ListIndex

       End If

   End If

End Sub

 
 
As you can see, it couldn't be much easier to add and delete items from the list box -- it works almost identically to the similar VB ListBox control. There are some things to look out for, however:

If you attempt to visually line up an Access control and an MSForms control on a form, you'll be dismayed to see that you can't. Their borders are drawn differently, and the controls simply won't line up on a form without manual juggling. (I found that if I move either of the Access or MSForms controls one pixel in either direction, I can get them to line up. Just what we needed . . .)

If you use MSForms controls (actually, any ActiveX control) and import a form into a new database, the form and its code get brought over, but Access doesn't update the references to the control library in the new database. You could solve this problem by going to the new database's References list and adding a reference to the control. An easier way to do this is to place a new instance of the same control on your form, and then immediately delete it. Microsoft sees this as "working as designed." I see it as a pain.

 
If you want to use an MSForms list or combo box to display data from a recordset while still taking advantage of the control's features, it's easy to fill the list using the GetRows method. You'll be limited to 10 columns, though, because the control is unbound (see the MSForms online help for more information).
 
The sample form frmDemoListBox in the accompanying Download file demonstrates this technique. The DAO GetRows method requires you to specify the maximum number of rows to display, even though it would be great if you could leave off that value and have GetRows retrieve all of the rows. Thanks to Andy Baron, who pointed out this useful technique. It's documented in the MSForms Help file, but I'd missed it. Here's the relevant code from frmDemoListBox:
 

Private Sub cmdLoadRecordset_Click()

   Dim db As Database

   Dim rst As Recordset

   

   lst.Clear

   Set db = CurrentDb

   Set rst = db.OpenRecordset( _

    "SELECT * FROM tblCustomer " & _

    "ORDER BY LastName")

   lst.Column = rst.GetRows(1000)

   rst.Close

   Set rst = Nothing

   Set db = Nothing

End Sub

 
 
You'll need to study the various MSForms controls to see all that they're capable of. They're not going to replace the intrinsic Access controls, but for specific problems, they can provide functionality that you need. I have to wonder why there's no Sorted property on this list box, but maybe next time . . .
 
I've also included an example form that shows how to use the MSForms ListBox control to emulate a two-listbox-chooser layout. The sample form, frmMultipickMSForms, includes a good deal of code to make this all possible. You might want to investigate how much easier using this control is compared to using Access's built-in control. I've also used other MSForms controls successfully in various Access applications, so you might want to dig in and see what goodies they supply.
 
Read about the download SA9905AA.ZIP on this page