Team LiB
Previous Section Next Section

Formatting Controls on Main and Subforms

Formatting form controls—especially dynamically—is always an interesting topic. Often you will want to contrast some control values with other values. This section details three approaches to this. The first approach relies on manually creating conditional formats, which you can use to format controls dynamically without writing programs. Even experienced coders might find it desirable to try a formatting look without writing any programs. Second, you can programmatically set conditional formats using the FormatConditions collection and FormatCondition objects. The third approach—using event procedures and control properties—was the only way to achieve conditional formatting for controls before conditional formats were introduced in Access 2000. This approach might be more code intensive than working with conditional formats manually or programmatically, but it offers more flexibility.

Conditional Formatting

Access 2003 lets you conditionally format the data displayed by a Text Box or Combo Box control without programming. You can selectively apply formatting to form controls for both bound and calculated fields.

Figure 5-13 shows three instances of the same form. I used conditional formatting to control the appearance of the Discount and Extended Price fields. The Discount field in the top form is disabled. The middle form highlights the value in the Extended Price field using bold and italic formatting. The bottom form enables the Discount field and highlights the value in the Extended Price field.

Click To expand
Figure 5.13: Conditional formatting controls the appearance of the Extended Price and Discount fields on this form.

The Extended Price field is calculated; it does not derive its value directly from an underlying table. The expression [UnitPrice]*[Quantity]*(1-[Discount]) in the text box's ControlSource property setting computes the value when the user moves to a new record or updates the UnitPrice, Quantity, or Discount field of the current record. (The terms in brackets reference controls, not field names for the underlying data source.)

Note 

Novice programmers sometimes give fields and controls the same name. This practice can be confusing and can lead to errors. (The AutoForm Wizard and the Northwind sample are also guilty of this practice.) Consider adding prefixes to control names to distinguish them from their underlying field names. For example, txtUnitPrice is a good name for a Text Box control that is bound to a field named UnitPrice.

To apply conditional formatting to a control, select the control and choose Conditional Formatting from the Format menu to open the Conditional Formatting dialog box, shown in Figure 5-14. Every control with conditional formatting has at least two formats—a default format and one special format when a specified condition is True. The Conditional Formatting dialog box permits up to three conditional formats for a control. The dialog box offers six formatting controls to help you specify each conditional format. These permit you to manage the application of boldface, italics, underlining, background color, and foreground color, as well as whether a control is enabled.

Click To expand
Figure 5.14: The Conditional Formatting dialog box.

You can format based on a control's field value, its expression value, or whether it has the focus. When you work with the field value for a control, you can select from a list of operators, such as equal to (=), greater than (>), and less than (<). The condition for the Discount field in Figure 5-14 is that the Field value is equal to 0. The formatting option for this condition disables the control when the discount is 0.

If you apply conditional formatting to a calculated field, such as Extended Price, you must write an expression using standard VBA operators. The condition for the Extended Price field is that the expression is txtExtendedPrice.value>500. (The name of the control that displays the calculated value is txtExtendedPrice.) When the field is greater than 500, bold and italic formatting highlight the text box contents.

You can easily apply another condition and special format to a control by clicking the Add button in the Conditional Formatting dialog box and specifying the new condition and its formatting information.

Programming Conditional Formats

As desirable as conditional formats are for eliminating or minimizing the programming of special formats for form controls, there are legitimate reasons for programming conditional formats. The Conditional Formatting dialog box restricts you to three formats (plus a default format) per control. If your application requires more diversity, you can dynamically manage conditional formats through their programmatic interface. In addition, if you want to apply a set of formats to several forms in the same or different applications, having the conditional formats programmatically defined simplifies applying the identical set of formats to different forms.

Each text box and combo box on a form has a FormatConditions collection containing FormatCondition objects. Even with the programmatic interface, each control is limited to three special conditions at any one time. However, you can program these conditions to have different values under different circumstances, thus multiplying the number of formats that you can manage programmatically. Because FormatCondition objects do not have name properties, you must reference them by their index numbers or property settings. For example, the Type property indicates that you apply the FormatCondition object via an expression or field value, or depending on whether a control has focus. Other properties let you set the expression values that determine whether to impose a format and its features, such as boldface, italics, and color. These property settings both define and identify the FormatCondition object.

The conditional-format programming sample that follows demonstrates several features of programmatically managing forms. The code begins by reinforcing your understanding of the process for dynamically assigning a recordset to the main form of a main/subform. After opening the form with an assigned recordset, the code prompts the user for the type of bolding on the form. Users can choose to bold all values for the OrderID control on the main form or any Discount control value greater than 14 percent on the subform, or they can select no boldface option at all. An If…ElseIf statement processes the user's reply to a message box to determine which path to pursue.

If a user chooses to bold all OrderID control values, the procedure uses the DoCmd object's GoToRecord method to navigate to the last record. After this, it saves the OrderID value for that record. After moving back to the first record, the code creates an expression with the Add method for a FormatCondition object that is True for any OrderID value less than or equal to that of the last record.

If the user chooses to apply a bold font to any Discount control value greater than .14 (14 percent), the procedure creates an object reference pointing to the Discount control on the subform. Then, the procedure invokes the Add method for the FormatConditions collection of the object reference. The syntax creates an expression that is True for any Discount control with a value greater than 14 percent. If the user chooses either Yes or No at the message box prompt, the code assigns a bold font to the FormatCondition object, frc1. The expression and the control for the FormatCondition object determine when to apply a bold font and which control to apply it to. If the user chooses Cancel in reply to the message box prompt, the program bypasses the creation of a FormatCondition object and the assignment of a format property for it.

Sub CreateConditionalFormat() 
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim frm1 As Form
Dim ctl1 As Control
Dim ctl2 As Control
Dim frc1 As FormatCondition
Dim int1 As Integer
Dim int2 As Integer
Dim str1 As String
   
'Create the connection
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb;"
   
'Create recordset reference and set its properties
'to permit read/write access to recordset
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.CursorLocation = adUseClient
   
'Open recordset based on orders in 1998 for
'frmSimpleUnboundForm
rst1.Open "SELECT * FROM Orders o " & _
    "WHERE Year(o.OrderDate) = 1998", cnn1
   
'Open a main/subform, assign pointers for main
'form and subform control, and assign recordset
'to the main form's Recordset property
str1 = "frmMyOrders"
DoCmd.OpenForm str1
Set frm1 = Forms(str1)
Set ctl1 = frm1.[MyOrderDetails]
ctl1.Form.SubdatasheetExpanded = False
Set frm1.Recordset = rst1
   
'Depending on user input, add a format condition
'to a main form control or a subform control
int1 = MsgBox("Do you want to bold OrderID " & _
    "values on the Main form? (choosing 'No' bolds Discounts " & _
    "on the subform greater than 14%.)", vbYesNoCancel, _
    "Programming Microsoft Access Version 2003")
If int1 = vbYes Then
    DoCmd.GoToRecord , , acLast
    int2 = frm1.Controls("OrderID")
    DoCmd.GoToRecord , , acFirst
    Set frc1 = frm1.Controls("OrderID"). _
        FormatConditions.Add(acFieldValue, _
            acLessThanOrEqual, int2)
ElseIf int1 = vbNo Then
    Set ctl2 = ctl1.Form.Controls("Discount")
    Set frc1 = ctl2.FormatConditions. _
        Add(acFieldValue, acGreaterThan, 0.14)
Else
    GoTo ConditionalFormatSample_Exit
End If
   
'Set a format condition to bold a control value
With frc1
    .FontBold = True
End With
   
'Clean up objects
ConditionalFormatSample_Exit:
Set ctl2 = Nothing
Set ctl1 = Nothing
DoCmd.Close acForm, frm1.Name
Set frm1 = Nothing
rst1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
   
End Sub

This sample offers an attractive template for dynamically applying conditional formats to controls on main forms and subforms. The sample does not persist conditional formats to a Form object. Therefore, if a user opens a form outside the application code for dynamically assigning the conditional formats, the form controls will not appear with the formats. You can programmatically persist conditional formats to a form object, however. The next sample demonstrates the syntax for doing this to both main and subforms.

There are two tricks for persisting conditional formats. The first one requires you to use the Save method of the DoCmd object to save the form after creating the conditional format. The acSaveYes argument for the DoCmd object's Close method does not persist conditional formats to Form objects when it closes them. The second trick is to not persist conditional formats for subform controls through the Form property of a subform control on a main form. Instead, you must close the main form and open the subform so that you can apply conditional formats to it as a standalone form. Then, you can close the subform and restore the main form. This convention is a departure from many of the subform samples discussed so far. In fact, the preceding sample demonstrates that you can create a conditional format for a control on a subform through the Form property of a subform control. However, you cannot save a conditional format created this way.

The next sample shows how to save conditional formats for controls on main forms and subforms. Because the subforms in this code sample operate differently than in many of the preceding examples, this sample includes a conditional compilation with an original value of True for the SubFormFix compilation constant. This setting causes the code to save successfully conditional formats for subform controls. Setting the constant to False follows the traditional route for processing subform controls, and it does not persist a conditional format created for a subform control.

The sample to follow starts by opening the frmMyOrders form and setting references to the main form and subform control on the main form. The code also saves the SubdatasheetExpanded property setting so that it can restore the setting later, if necessary. Next, the sample presents a message box prompt as in the preceding sample. If the user chooses to set a conditional format for the OrderID control on the main form, the program logic proceeds as in the earlier sample until it reaches the code block with the label 'Save conditional formats. The code block contains two lines. One invokes the DoCmd object's Save method with the form name as an argument. This action persists the conditional format created for the OrderID control. If you set the SubFormFix compilation constant to a value of False, the sample attempts to save a conditional format for a subform control after creating it the same way as in the preceding sample. However, the action fails silently for the subform control.

When the SubFormFix compilation is True, the sample adopts special measures to create and save the conditional format for the subform control. Examine the #Else path to see the code for creating and saving a conditional format for a subform. This code segment starts by closing the main form, frmMyOrders. Then, it opens the subform, frmMyOrderDetails, as a standalone form. Next, it creates a FormatCondition object for the Discount control on the form and assigns a bold formatting property setting to the FormatCondition object. The task of persisting the conditional form requires you to save and close the form. For your convenience, this sample restores the main form before exiting the procedure. It even reinstates the status of the SubdatasheetExpanded property.

Sub PersistAConditionalFormat() 
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim frm1 As Form
Dim ctl1 As Control
Dim ctl2 As Control
Dim frc1 As FormatCondition
Dim int1 As Integer
Dim bol1 As Boolean
#Const SubFormFix = True
   
'Open a main/subform, assign pointers for main
'form and subform control, and save
'SubdatasheetExpanded setting
DoCmd.OpenForm "frmMyOrders"
Set frm1 = Forms("frmMyOrders")
Set ctl1 = frm1.MyOrderDetails
bol1 = ctl1.Form.SubdatasheetExpanded
   
'Depending on user input, add a format condition
'to a main form control or a subform control
int1 = MsgBox("Do you want to bold OrderID " & _
    "10248 on the Main form? (choosing 'No' bolds Discounts " & _
    "on the subform greater than 14%.)", vbYesNoCancel, _
    "Programming Microsoft Access Version 2003")
If int1 = vbYes Then
    Set frc1 = frm1.Controls("OrderID"). _
        FormatConditions.Add(acFieldValue, acEqual, 10248)
ElseIf int1 = vbNo Then
    #If SubFormFix = False Then
        Set ctl2 = ctl1.Form.Controls("Discount")
        Set frc1 = ctl2.FormatConditions. _
            Add(acFieldValue, acGreaterThan, 0.14)
    #Else
'Close the main/subform so that you can open the
'subform as a standalone form
        DoCmd.Close acForm, "frmMyOrders"
'Open subform, apply conditional format, and save it
        DoCmd.OpenForm "frmMyOrderDetails"
        Set frc1 = Forms("frmMyOrderDetails"). _
            Controls("Discount").FormatConditions. _
            Add(acFieldValue, acGreaterThan, 0.14)
        With frc1
            .FontBold = True
        End With
        'Forms("frmMyOrderDetails").SubdatasheetExpanded = True
        'Forms("frmMyOrderDetails").SubdatasheetExpanded = False
        DoCmd.Save acForm, "frmMyOrderDetails"
        DoCmd.Close acForm, "frmMyOrderDetails"
'Reopen main/subform
        DoCmd.OpenForm "frmMyOrders"
        Set frm1 = Forms("frmMyOrders")
        Set ctl1 = frm1.MyOrderDetails
        ctl1.Form.SubdatasheetExpanded = bol1
        Exit Sub
    #End If
Else
    GoTo ConditionalFormatSample_Exit
End If
   
'Set a format condition to bold a control value
With frc1
    .FontBold = True
End With
   
'Save conditional formats
DoCmd.Save acForm, "frmMyOrders"
DoCmd.Close acForm, "frmMyOrders"
   
'Clean up objects
ConditionalFormatSample_Exit:
Set ctl2 = Nothing
Set ctl1 = Nothing
Set frm1 = Nothing
   
End Sub

Selecting the option to format the OrderID field on the main form causes frmMyOrders to close. Users can view the result of the formatting by manually re-opening the main form. If you choose to persist a format for the Discount field on the frmMyOrderDetails subform, the main form remains open and shows the bold format for all Discount field values greater than .14. However, closing and then re-opening the form causes the Discount field to flicker so that its values are difficult to read.

The frmPersistedFormatManager form offers workarounds to the issue associated with persisting a format to subform fields, such as Discount. The form contains four buttons named Command0 through Command3 from top to bottom. The Click event procedure for the first button invokes the PersistAConditionalFormat procedure and closes the form. This step requires a user to open the frmMyOrders form to see any selected formatting. The second button converts the DefaultView property for the frmMyOrderDetails form from Datasheet to SingleForm. This suppresses the flickering of the Discount field and shows the formatting. The third button assigns Datasheet as the DefaultView property of frmMyOrderDetails before opening frmMyOrders, the main form. In order to suppress the flickering for the Discount field, the Click event procedure for the third button expands and then collapses the subform. The latter step suppresses flickering for the Discount field. Even with these options for suppressing flickering, there is still a flickering problem. If a user opens frmMyOrders outside of the frmPersistedManager, the Discount field will still flicker. This is because of the conditional format assigned to the Discount field by the PersistAConditionalFormat procedure. Therefore, the fourth button on frmPersistedFormatManager removes the conditional format for the Discount field. This enables a user to open frmMyOrders without viewing flickering for the Discount field (although it also fails to highlight Discount values greater than .14).

The code for the four Click event procedures behind frmPersistedFormatManager appears below. Three global declarations precede the procedure listings. At least two procedures use each of the global declarations.

Const conSingleForm = 0
Const conDatasheet = 2
Dim str1 As String
   
Private Sub Command0_Click()
    
'Run PersistAConditionalFormat procedure
PersistAConditionalFormat
DoCmd.Close acForm, "frmMyOrders"
   
End Sub
   
Private Sub Command1_Click()
   
'Make SingleForm DefaultView property for frmMyOrderDetails
str1 = "frmMyOrderDetails"
DoCmd.OpenForm str1, acDesign
Forms(str1).DefaultView = conSingleForm
DoCmd.Save acForm, str1
DoCmd.Close acForm, str1
   
'Open frmMyOrders
DoCmd.OpenForm "frmMyOrders"
   
End Sub
   
Private Sub Command2_Click()
   
'Make Datasheet DefaultView property for frmMyOrderDetails
str1 = "frmMyOrderDetails"
DoCmd.OpenForm str1, acDesign
Forms(str1).DefaultView = conDatasheet
DoCmd.Save acForm, str1
DoCmd.Close acForm, str1
   
'Open frmMyOrders
DoCmd.OpenForm "frmMyOrders"
   
'Manipulate subdatasheet to fix appearance of the
'formatted control
Forms("frmMyOrders").MyOrderDetails.Form.SubdatasheetExpanded _
    = True
Forms("frmMyOrders").MyOrderDetails.Form.SubdatasheetExpanded _
    = False
   
End Sub
   
Private Sub Command3_Click()
   
'Remove format
str1 = "frmMyOrderDetails"
DoCmd.OpenForm str1, acDesign
Forms(str1).Controls("Discount").FormatConditions.Delete
DoCmd.Save acForm, str1
DoCmd.Close acForm, str1
   
End Sub

Formatting with Conditional Formats and Event Procedures

Before the introduction of conditional formats, the most popular way to assign formats to form controls was with event procedures for form events. Event procedures can test conditions for imposing a format on a control. The next example of formatting form controls mixes both conditional formats and event procedures to control the display of content on a main/subform.

The sample form we'll discuss appears in Figure 5-15. It shows a main/subform for the Orders and Order Details tables. These two tables are linked in the Chapter05.mdb sample file that points back to the Northwind database. The main form's name is frmConditionalMainSub, and the subform's name is frmConditionalOD. The figure shows that the OrderID control is disabled on the main form. In addition, the OrderID and ProductID controls on the subform are disabled. Recall that the ProductID control relies on a lookup field that automatically shows the ProductName from the Products table instead of the matching ProductID value in the Order Details table. Event procedures disable the main form and subform controls. In addition, the Discount control on the subform has three conditional formats. Values less than 5 percent appear in a green font, and values greater than 15 percent appear in red. Discount control values ranging from 5 to 15 percent appear in a shade of orange when the monitor is set to Highest (32 bit) Color quality.

Click To expand
Figure 5.15: Conditional formatting controls the appearance of the Discount control values on this form, and event procedures conditionally disable the OrderID control on the main form and the OrderID and ProductID controls on the subform.

Figure 5-16 presents the Conditional Formatting dialog box for the Discount control in the subform. Its top, middle, and bottom expressions set green, orange, and red fonts, respectively. I opened the dialog box by selecting the Discount control on the subform in Design view and choosing Format, Conditional Formatting.

Click To expand
Figure 5.16: The expressions for the Discount control values in Figure5-13.

The event procedures for the main and subforms rely on the Current event. Recall that this form event occurs when the focus moves to a record, making it the current one, or when a user requeries or refreshes a form so that the values of the form's controls might change. The Current event actually occurs before the record gains focus or the refresh or requery operation takes place. This event allows your program to test the control values before they are displayed. The sample's event procedures set the Enabled property of the controls. A compilation constant at the beginning of both event procedures simplifies turning off the effects of the procedures.

In the sample's main form, the Form_Current event procedure sets the OrderID control's Enabled property to a Boolean value. Setting a control's Enabled property to False protects its value from change. The OrderID field is the primary key for the record source behind the main form. While you might want to protect the value in this field for existing records, you will definitely want the control enabled if you have to enter values into it to complete a new record. When the control is Null (for example, when a user creates a new record), the Form_Current event procedure sets the control's Enabled property to True. This enabled setting is necessary so that Access can assign a new AutoNumber to the control. (A user can't edit the AutoNumber field, even if it is enabled.) The procedure moves the focus to the CustomerID control so that the user can start entering data with that control.

Private Sub Form_Current() 
#Const EnableOnOff = True
   
#If EnableOnOff = True Then
'If cell is not Null, protect the
'primary key of the previously entered record;
'otherwise, enable the OrderID text box
    If IsNull(Me.OrderID) = False Then
        Me.OrderID.Enabled = False
    Else
        Me.OrderID.Enabled = True
        Me.CustomerID.SetFocus
    End If
#End If
   
End Sub

The next Form_Current event procedure is for the subform. Notice that in the event procedures for both the main and subform, you can use the simple Me notation to reference controls on a form. You can create or edit the Current event procedure for a subform in the usual way. First, select the subform. Do this by clicking the top-left box of the subform control on the main form. Then, open its property sheet in Design view and click the Build button next to the On Current event setting. This will open the shell for a new or an existing Form_Current event procedure.

The Form_Current event procedure for the subform disables the OrderID and ProductID controls by setting their Enabled property to False if the OrderID control and the ProductID control are both populated. In this case, the event procedure sets the focus to Quantity, the first control after ProductID on the subform. When you move to a blank main form record to enter a new order and select a customer, Access automatically enters a value into the OrderID control on the main form and current row of the subform. However, ProductID value for the current row on the subform contains a Null, and the procedure enables the ProductID controls. In addition, it sets the focus to the ProductID control. This is perfectly reasonable here because the user needs to input values to the ProductID control to complete the record so that Access will enter it into the record source for the subform. Here's the subform's event procedure:

Private Sub Form_Current()
#Const EnableOnOff = True
   
#If EnableOnOff = True Then
'If primary key is not Null, protect the
'primary key of the previously entered record;
'otherwise, enable the ProductID text box
    If IsNull(Me.OrderID) = False And _
        IsNull(Me.ProductID) = False Then
        Me.Quantity.SetFocus
        Me.OrderID.Enabled = False
        Me.ProductID.Enabled = False
    Else
        Me.ProductID.Enabled = True
        Me.ProductID.SetFocus
    End If
#End If
   
End Sub

Team LiB
Previous Section Next Section