Protecting Forms by using VBA Code

The best ways to protect and secure the design of individual forms are to compile the database into MDE format (see the section "The MDE Database Format Revisited" in this chapter), to use the Hidden property (see Chapter 3), and to use workgroup security (see Chapter 8 and the section "A Workgroup Security Refresher" earlier in this chapter). If you are not sure about using the MDE format, consider using the following programming technique to stop people from opening your forms in design view.

With the release of Access 97, Access forms became a much more versatile class object. By exploiting this object technology, we can open Access forms in a different way so that the users cannot use the Design View commands. To demonstrate how this process works, I have set up a simple form (called frmClassy ) that opens another form as a class object. This process works because you can open the form by creating a new instance of the form class, albeit an invisible one. When the Visible property of the form class object is set to True, the form appears just like an ordinary form, with one exception: the Design View command on the menu and toolbars is disabled. You can see the disabled design button in two places by looking at Figure 11-8.

Figure 11-8: A form opened as a class object cannot be put in design mode.

Some VBA code that illustrates this process is stored in a module called basOpenProtected in the demonstration database. As you can see in the following subroutine, the first thing that has to happen is that a specific form class object (called clsFrmOrders ) must be declared and instantiated . Once this happens, you can manipulate all the methods and properties of the form. Then, in the important last step in the code, you make the form visible. I personally use form properties and methods when setting up interfaces to be more intuitive for my clients .

 Sub ProtectOrdersForm() ' Open the Orders form as a class object to protect it from ' being opened in design mode. ' If you include this code in a form module, the form below ' will be closed when the form that opens it closes. On Error GoTo ProtectOrdersForm_error Static clsFrmOrders As Form_frmOrders Set clsFrmOrders = New Form_frmOrders With clsFrmOrders    .Caption = "Real World Orders Form That Cannot Be Designed"    .Detail.BackColor = vbGreen    .Visible = True End With ProtectOrdersForm_exit:    Exit Sub ProtectOrdersForm_error:    MsgBox "Error number " & Err.Number & " ... " & Err.Description    GoTo ProtectOrdersForm_exit End Sub 

Now, you may be wondering why I had to place this code in a module and not in the more logical place, in the code behind the form. It has to do with a behavior of the form class object, called scope. When you open a form by using code in a module, you have to declare the new form object as a static variable. Because you use a static variable, the form class remains in scope even when the subroutine has completed running. An example of the Static statement follows :

 Static frmOrders As Form_frmOrders 

Now, you should be aware that the form class behavior changes according to where you place the Static statement. If you declare the same static variable in the code behind a form, the second form will close if the form that holds the static variable is closed. Because you would generally want to avoid this behavior, I recommend that you call a subroutine in a separate module, as follows:

 Private Sub cmdOpenClassyForm_Click() ' Open a form as a class object, thus protecting it. ' This subroutine should appear in a module except when you ' want the instance of the form class to close when this form closes. Call ProtectOrdersForm End Sub 

If this "scope behavior" stuff doesn't make sense, try experimenting with the basOpenProtected module. When that works, copy the code module and place it in the Click event of a new command button on a form. Then press the new button to open the form class, and close the form with the new button.

Before finishing this topic, I will give you some other things to contemplate when using forms as class modules:

• You need to have the Has Module property of the form set to Yes for the form to be available as a class module.

• You need to ensure that you have adequate error handling code in your forms, or a bug can send the user into the VBA code debugger.

• You can actually open multiple versions of the form, but you will need to keep an array of form objects.

If you want to read more on this topic, see the "Further Reading" section at the end of this chapter for information on how to reach a link to an article that I wrote on scope behavior for the Smart Access magazine.

Watch Out for Cut and Pasted Datasheets

One great thing about Access forms is that you can readily switch between the single record view to the spreadsheet-like datasheet view. Though this power is terrific , you need to be aware that the datasheet and continuous form views make it very easy for the end user to cut and paste all the records in a form into a product like Microsoft Excel or Word. If your intention was never to provide the user with this facility, then make sure that you turn off the form's Allow Datasheet View property.