Take Advantage Of The Class Module Features Of Your Forms
When Access 97 came out, one of its hidden features is that Form objects now were actually Class modules. Whoopee do you might say. Quite a few years on and guess what, it finally dawned on me that I was using Forms more and more as class modules. This has gradually came about as clients are requiring more intuitive interfaces. This article outlines ways that you can take advantage of class modules features that I have used over the last few years. You will note as you read the article that I have not gone really overboard on technicalities but instead have concentrated on showing you ways to illicit even more mileage out your most valuable forms.
A Useful Trick
Sometimes when I open a form in visual basic, I avoid using the Button Wizard as all the error code may not be appropriate. Instead I make a blank button by canceling the wizard. Then, as per usual, I do not quite remember the name of the form so I will type the following visual basic
Docmd.openForm form_
and hit the Ctrl key and Space Bar together to show a list of forms.
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.
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
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. The fields that we will transfer are the OrderId, subtotal, freight and total.
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.
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).
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.
