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.

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.

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.

 SubCreateConditionalFormat()  Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimfrm1AsForm Dimctl1AsControl Dimctl2AsControl Dimfrc1AsFormatCondition Dimint1AsInteger Dimint2AsInteger Dimstr1AsString     'Createtheconnection Setcnn1=NewADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind.mdb;"     'Createrecordsetreferenceandsetitsproperties 'topermitread/writeaccesstorecordset Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.CursorLocation=adUseClient     'Openrecordsetbasedonordersin1998for 'frmSimpleUnboundForm rst1.Open "SELECT*FROMOrderso " &_  "WHEREYear(o.OrderDate)=1998",cnn1     'Openamain/subform,assignpointersformain 'formandsubformcontrol,andassignrecordset 'tothemainform'sRecordsetproperty str1= "frmMyOrders" DoCmd.OpenFormstr1 Setfrm1=Forms(str1) Setctl1=frm1.[MyOrderDetails] ctl1.Form.SubdatasheetExpanded=False Setfrm1.Recordset=rst1     'Dependingonuserinput,addaformatcondition 'toamainformcontrolorasubformcontrol int1=MsgBox("DoyouwanttoboldOrderID " &_  "valuesontheMainform?(choosing'No'boldsDiscounts " &_  "onthesubformgreaterthan14%.)",vbYesNoCancel,_  "ProgrammingMicrosoftAccessVersion2003") Ifint1=vbYesThen DoCmd.GoToRecord,,acLast int2=frm1.Controls("OrderID") DoCmd.GoToRecord,,acFirst Setfrc1=frm1.Controls("OrderID")._ FormatConditions.Add(acFieldValue,_ acLessThanOrEqual,int2) ElseIfint1=vbNoThen Setctl2=ctl1.Form.Controls("Discount") Setfrc1=ctl2.FormatConditions._ Add(acFieldValue,acGreaterThan,0.14) Else GoToConditionalFormatSample_Exit EndIf     'Setaformatconditiontoboldacontrolvalue Withfrc1 .FontBold=True EndWith     'Cleanupobjects ConditionalFormatSample_Exit: Setctl2=Nothing Setctl1=Nothing DoCmd.CloseacForm,frm1.Name Setfrm1=Nothing rst1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 

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.

 SubPersistAConditionalFormat()  Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimfrm1AsForm Dimctl1AsControl Dimctl2AsControl Dimfrc1AsFormatCondition Dimint1AsInteger Dimbol1AsBoolean #ConstSubFormFix=True     'Openamain/subform,assignpointersformain 'formandsubformcontrol,andsave 'SubdatasheetExpandedsetting DoCmd.OpenForm "frmMyOrders" Setfrm1=Forms("frmMyOrders") Setctl1=frm1.MyOrderDetails bol1=ctl1.Form.SubdatasheetExpanded     'Dependingonuserinput,addaformatcondition 'toamainformcontrolorasubformcontrol int1=MsgBox("DoyouwanttoboldOrderID " &_  "10248ontheMainform?(choosing'No'boldsDiscounts " &_  "onthesubformgreaterthan14%.)",vbYesNoCancel,_  "ProgrammingMicrosoftAccessVersion2003") Ifint1=vbYesThen Setfrc1=frm1.Controls("OrderID")._ FormatConditions.Add(acFieldValue,acEqual,10248) ElseIfint1=vbNoThen #IfSubFormFix=FalseThen Setctl2=ctl1.Form.Controls("Discount") Setfrc1=ctl2.FormatConditions._ Add(acFieldValue,acGreaterThan,0.14) #Else 'Closethemain/subformsothatyoucanopenthe 'subformasastandaloneform DoCmd.CloseacForm, "frmMyOrders" 'Opensubform,applyconditionalformat,andsaveit DoCmd.OpenForm "frmMyOrderDetails" Setfrc1=Forms("frmMyOrderDetails")._ Controls("Discount").FormatConditions._ Add(acFieldValue,acGreaterThan,0.14) Withfrc1 .FontBold=True EndWith 'Forms("frmMyOrderDetails").SubdatasheetExpanded=True 'Forms("frmMyOrderDetails").SubdatasheetExpanded=False DoCmd.SaveacForm, "frmMyOrderDetails" DoCmd.CloseacForm, "frmMyOrderDetails" 'Reopenmain/subform DoCmd.OpenForm "frmMyOrders" Setfrm1=Forms("frmMyOrders") Setctl1=frm1.MyOrderDetails ctl1.Form.SubdatasheetExpanded=bol1 ExitSub #EndIf Else GoToConditionalFormatSample_Exit EndIf     'Setaformatconditiontoboldacontrolvalue Withfrc1 .FontBold=True EndWith     'Saveconditionalformats DoCmd.SaveacForm, "frmMyOrders" DoCmd.CloseacForm, "frmMyOrders"     'Cleanupobjects ConditionalFormatSample_Exit: Setctl2=Nothing Setctl1=Nothing Setfrm1=Nothing     EndSub 

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.

 ConstconSingleForm=0 ConstconDatasheet=2 Dimstr1AsString     PrivateSubCommand0_Click() 'RunPersistAConditionalFormatprocedure PersistAConditionalFormat DoCmd.CloseacForm, "frmMyOrders"     EndSub     PrivateSubCommand1_Click()     'MakeSingleFormDefaultViewpropertyforfrmMyOrderDetails str1= "frmMyOrderDetails" DoCmd.OpenFormstr1,acDesign Forms(str1).DefaultView=conSingleForm DoCmd.SaveacForm,str1 DoCmd.CloseacForm,str1     'OpenfrmMyOrders DoCmd.OpenForm "frmMyOrders"     EndSub     PrivateSubCommand2_Click()     'MakeDatasheetDefaultViewpropertyforfrmMyOrderDetails str1= "frmMyOrderDetails" DoCmd.OpenFormstr1,acDesign Forms(str1).DefaultView=conDatasheet DoCmd.SaveacForm,str1 DoCmd.CloseacForm,str1     'OpenfrmMyOrders DoCmd.OpenForm "frmMyOrders"     'Manipulatesubdatasheettofixappearanceofthe 'formattedcontrol Forms("frmMyOrders").MyOrderDetails.Form.SubdatasheetExpanded_ =True Forms("frmMyOrders").MyOrderDetails.Form.SubdatasheetExpanded_ =False     EndSub     PrivateSubCommand3_Click()     'Removeformat str1= "frmMyOrderDetails" DoCmd.OpenFormstr1,acDesign Forms(str1).Controls("Discount").FormatConditions.Delete DoCmd.SaveacForm,str1 DoCmd.CloseacForm,str1     EndSub 

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.

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.

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.

 PrivateSubForm_Current()  #ConstEnableOnOff=True     #IfEnableOnOff=TrueThen 'IfcellisnotNull,protectthe 'primarykeyofthepreviouslyenteredrecord; 'otherwise,enabletheOrderIDtextbox IfIsNull(Me.OrderID)=FalseThen Me.OrderID.Enabled=False Else Me.OrderID.Enabled=True Me.CustomerID.SetFocus EndIf #EndIf     EndSub 

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:

 PrivateSubForm_Current() #ConstEnableOnOff=True     #IfEnableOnOff=TrueThen 'IfprimarykeyisnotNull,protectthe 'primarykeyofthepreviouslyenteredrecord; 'otherwise,enabletheProductIDtextbox IfIsNull(Me.OrderID)=FalseAnd_ IsNull(Me.ProductID)=FalseThen Me.Quantity.SetFocus Me.OrderID.Enabled=False Me.ProductID.Enabled=False Else Me.ProductID.Enabled=True Me.ProductID.SetFocus EndIf #EndIf     EndSub