Microsoft Access Forms Are All Class

Docmd.openForm  form_    

and hit the Ctrl key and Space Bar together to show a list of forms.

Figure 1   Retrieve all the form class objects in your database.

This list is actually all the forms in that database that have code behind the form.  These are all  stored in your database as class modules.  And guess what you can use these modules.   So back to my trick.   From this list I can select the form that I want.  I then delete the “form_” before the form name and I  now have my form name.  If you are missing a form in this list, you must ensure that the HasModule property of the form is set to True.  This can make for slower loading of forms if they have no code so beware about getting too carried away with setting this property to True. NOTE: the form must have some code behind it to appear in this list.

The Download Database

The download database consists of a database in Access 2000.   I originally started doing all the samples in Access 97 but stopped because Access 97 would not support recordsets behind the form.   So if you want to use the samples in Access 97, save the database from Access 2000 back to Access 97 using either Access 2000 or 2002.  Then delete the code that doesn't compile and what is left is the code that is usable in Access 97.

Starting To Use The Form As A Class Module

When you open a form with the DoCmd.openForm method, there are a limited number of things that you can to the form including showing the form in datasheet or normal mode, filtering records behind the form or making it display the form in Dialog mode.  If you are really tricky you can use the OpenArg argument to pass all sorts of clever text strings to the form..  These clever text strings can then be manipulated in the Form_open event.   The alternative to this is to use the form as a class object and manipulate it like in the following example.

DoCmd.OpenForm "Orders"

With Form_Orders

  .AllowEdits = True

  .RecordSelectors = True

  .NavigationButtons = False

  .Caption = "Smart Access Demonstration"

  .CustomerID.StatusBarText = "My Message"

End With

As you can see, many of the things that you might manipulate manually such as turning the recordselectors and navigation controls on and off can be handled easily in code.   This is very useful as it allows you to modify the way the form reacts whilst it is being opened.   i.e.  Navigation buttons are useful for managing many records but if all you want to do is add one new record, then you can turn them off as they clutter up the user interface.

To get started using this feature, there is nothing more to the manipulation of the controls on the your form than adding a code block as I have for the “orders” form

with form_Orders.

end with

Now you can type a full stop inside the With block and the following programmable items for the form will appear.

1) All the methods such as Undo, SetFocus,  Requery, Refresh, Repaint

2) An abundance of properties including Dirty,  Hwnd, Cycle, DatasheetFontName.   

3) All the text boxes, combo boxes and other data handling controls that exist in the form.  Once you have selected a control such as the OrderId control, you can now review/manipulate the properties of that control. Examples include defaultValue, font characteristics and Control Tip plus all the other properties that you are used to editing manually. 

Setup The Form For Different Data Entry Modes

The primary reason why I have been manipulating the properties of forms in code is to setup different styles of interfaces for the end users. The following code demonstrates how the user interface can be switched to either Add or Edit mode according to a field on the sample form (shown in figure 2).   If the user leaves the order number field blank, the form is setup in DataEntry  mode.   If the order number is not blank, then the form receives the focus in code and then the FindRecord method of the DoCmd is used to find the actual order itself. 

The code for this is shown below and whilst there is not much of it, the differences reflect things that might make the user interface more confusing than it needs to be.  For example the record navigation control is on  when an order exists and off when you are adding a new order.  Further on in the article, I show you how the form recordset can be used to determine if an order actually exists.

Dim UserResponse As Variant

DoCmd.OpenForm "Orders", , , , , acHidden

With Form_Orders

  If IsNull(Me!orderReq) Then

    UserResponse = MsgBox("You have not entered an order number, would you like to create a new order ? ", vbYesNo + vbDefaultButton2, "Create A New Order Number")

    If UserResponse = vbYes Then

      .AllowAdditions = True

      .DataEntry = True

      .NavigationButtons = False

      .Visible = True

    End If

  Else

    .Visible = True

     .DataEntry = False

     .NavigationButtons = True

    .OrderID.SetFocus

    DoCmd.FindRecord Me!orderReq, acEntire, , acSearchAll

  End If

End With

Figure 2 - The Demonstration Form With Most Of The Form Class Manipulation Code

Setting Up Your Own Filters

Whilst doCmd.openForm allows you to add your own where clause or query to filter your data, you may find it cleaner to program filters using the filter properties of the form class object.  The following example show easy it is to modify the filter to show only the records for a particular customer.

DoCmd.OpenForm "Orders", , , , acFormAdd, acHidden

With Form_Orders

 .Filter = "CustomerId = '" & Me!CustomerReq & "'"

 .FilterOn = True

 .NavigationButtons = True

 .Visible = True

End With

A word of caution here if performance is a big issue.  When you use DoCmd.openForm and do not apply either a filter query or a where statement, the form will open with a recordset that comprises of all data.  Applying the filter in the OpenForm method ensures that the recordset behind the form is filtered before it is displayed.  As this exercise is all about using the filterOn and filter properties of the form, it is wise to open the form using the acFormAdd constant for the dataMode argument.  This opens the form with no data being displayed.  This is fast and your data is immediately displayed whenever the filter is turned on.   I also like to hide the form from the user whilst the form is being manipulated using the acHidden property.  The form is made visible by setting the form class to visible at the end of the manipulation.

Look Mum – No Hands

If ever I had a golden rule for computing, it could be that you never want to program the same thing twice, especially if it involves money.  I ran into this issue with a programming buddy where we realised that after spending hours trying to calculate a total on a form using sub forms and other bits of code, that we then had to  rewrite all that code to use those result in a report.  In this case I realised that the form actually contained all the business logic for the calcualtions so I worked out how to transfer the calculation results across to a table using the forms class module.

This brings me to the next example where I show you how to transfer calculations from the good old Northwind Orders form across to a temporary table (see figure 3) .  The fields that we will transfer are the OrderId, subtotal, freight and total.

(Click picture to enlarge) 

Figure 3 - The slightly modified Northwind orders form

Initially I start by opening the orders form and filtering the customer records.  In this case, I call the code that is under the find customers button.  Never forget that code under a button can be reused elsewhere.  We now have an open form with only the orders for the one customer. 

Call cmdFindCustomers_Click

We can manipulate those records using the forms class object the recordset property.   This is really exciting, because you can walk through records using all the familiar moveNext, moveFirst, EOF methods of a recordset.  You can also test if the recordset has no records and stop any action.   I also test RecordCount property when opening forms to see if any data exists for a particular key field such as Date or Order.  Unfortunately this does not work in Access 97 as the form class module does not have a Recordset property.

With Form_Orders

  .Recordset.MoveFirst

  DoCmd.SetWarnings False

  If .Recordset.RecordCount = 0 Then

    MsgBox "No orders were found for " & _

    Me!CustomerReq, vbOKCancel, "Try Again"

    DoCmd.Close acForm, "Orders"

    GoTo exit_cmdTotals_Click

  Else

Now we have established that we have some records, we loop through the records for that customer.  You may want to do this with the form hidden once you form is in production.  As I am about to add the order totals to a blank table, I need to clear it first and turn off all the Access insert warnings.

DoCmd.SetWarnings False

DoCmd.RunSQL "delete from MyOrders"

Form_Orders.Visible = True

Now it is a matter of looping through the forms recordset which has the effect of showing each record on the form.   It seems to be necessary to use the forms recalulation method for each record to ensure that calculated fields are populated.    In this example, I have decided to use an Insert SQL statement rather than using another recordset to add the order totals to the temporary table.    It is very important to realize that all this code occurs in another form and not in the orders form.   As you can see below, I have referred to the orders form subtotal field using the class object of the form. The full property that we reference is Form_Orders.Subtotal.value   And to move to the next record for this customer, we simply use the .MoveNext method of the recordset.   The form then shows the next record and as I indicated before, “Look Mum, No Hands”

 Form_Orders..Recalc

 sqlStr = "INSERT INTO MyOrders " & _

"( OrderID, SubTotal, Freight, Total ) values " & _

"(" & Form_Orders.OrderID & "," & _

  Form_Orders.Subtotal & "," & _

  Form_Orders.Freight & "," & _

  Form_Orders.Total & ")"

  DoCmd.RunSQL sqlStr

  Form_Orders.Recordset.MoveNext

Wend

DoCmd.OpenTable "MyOrders"

Open The Same Form Twice

On the Orders form as shown in figure 4, I have setup a button to show this form again.  Yes you can actually display a copy of the your current form without copying it to a new name in the database container.  The reason that you can do this is because the form is a class object and hence can be instantiated as a new object.  The following code illustrates how I can make a copy of the form, filter it for the current order and modify a few properties so that the form looks different.  I find this technique is useful for comparing 2 complex records using a standard form view.  

(Click picture to enlarge)

Figure 4.   Create a new instance of the orders form and display the current order

Static lastTop As Long, lastLeft As Long

Set frmOrders = New Form_Orders

With frmOrders

  .Visible = True

  .Filter = "orderId = " & orderReq

  .FilterOn = True

  .Caption = "Filter:  " & .Filter

  .Detail.BackColor = vbWhite

  lastTop = lastTop + 100

  lastLeft = lastLeft + 100

  DoCmd.MoveSize lastTop, lastLeft

  .cmdCopyOrder.Visible = False

End With

Of course there are a few tricky things to realize about this new form object.  These include setting the visible property of the form to true.  Even more perplexing is that in your first attempt, you will probably write the code in such a manner as the form immediately drops out of scope once it has been displayed.     It does this if you declare a local object variable as follows

Sub CopyOrder

  Dim frmOrders As Form_Orders

  Set frmOrders = New Form_Orders

  With frmOrders

    .visible = true

  End With

End sub

What you have to do is to declare the new form object as static so that it remains in scope even when the software has completed running. 

Static frmOrders As Form_Orders

This has an interesting effect in that if you then run the same code again, it seems to save the record that you are editing and then refresh the same object with the new properties that you have set for the forms class.  This means that if you want to maintain multiple versions of the class objects, you will need to keep an array of form objects.   An even more technical approach is to make your own collection of form objects and manage the forms in that special Collection.

So when does this second form actually close down (go out of scope)  ?   It will close down if the user closes it down manually and it will close down if the form that holds the static variable is closed down.   

Setting Up Your Own Events And Methods

Now everything has been pretty exciting thus far, but there is more.  You can make your forms really clever by exposing your private subroutines and functions as public.  For the example where I made a new instance of the orders form, I decided that it would be cool to not only open the orders form but to use a special method of that form to display an order in a separate form as well.   How I managed this was to make a subroutine called CopyOrder and declare it as public.    I also added an optional argument to this subroutine so that I could pass any order number of my choosing into the subroutine.

Public Sub CopyOrder(Optional ShowOrderID As Variant)

Static lastTop As Long, lastLeft As Long

Static frmOrders As Form_Orders

Dim orderReq As Variant

' If this function is called as a class method,

' the programmer will pass an order number to

' designate which order to display

If IsMissing(ShowOrderID) Then

   orderReq = Me!OrderID

Else

  orderReq = ShowOrderID

End If

End sub

As you can see in Figure 5, this public function now appears in the form_orders class as a method.   I now can call this new method using an order number on my sample form as follows.  

With Form_Orders

  .CopyOrder Me!orderReq

End With

Its that easy and note that public functions also act as methods except that they can return variables after they are completed as well.   There is also the option of adding public properties for the form if you choose Insert .. Procedures from the visual basic top menu (whilst in the code behind the form).

Figure 5 – Once you setup a form class method, it immediately becomes visible with vb intellisense

Summing Up

Your Access forms are pretty smart objects and are one of the reasons why Microsoft Access is such as configurable tool.  Now that you have seen how the form can be manipulated because it is a class object, you can make really smart forms to suit the user interface requirements of your clients.    The forms class object also includes all the properties and events that you are used to manipulating manually.  Now you can manage this quite sensibly in code before you expose the form to your user.   In some cases such as recordset manipulation, you can even leverage considerable work that you have put into your forms to transfer information to tables that you can use in reporting and other activities.   So in summing up, forms are a class act so why not start using them as the class objects that they truly are.

Garry Robinson

Useful Further Reading and Resources

Search your Access help for "Program with Class Modules".  Choose the topic "Create a class module that is not associated with a form or report".  The links for this page take you to information about  programming form class objects.

Search your visual basic 6 help for the following key words

“Customizing Form Classes Hidden”

The Access 2000 Developers handbook (desktop edition) covers this topic in great detail.  Read my review at    http://www.vb123.com/books/

Author Bio.

Garry Robinson works for GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues,  visit his companies web site at  http://www.vb123.com/  or sign up for his Access email newsletter here.

Other Pages On This Site You Might Like To Read

Duplicate Data Entry For Access
Uncorrupt your access database - plenty of options
Make The Office Balloon Object Work For Your (Access) Application
Samples of Object Protection and Other Security Measures
 

Click on the following button to jump to the next page in the document loop.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals