Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Changing Font Color Conditional Formatting


With the conditional formatting feature of Microsoft Access, we can apply up to three colors to the font or background of a Textbox, because only three sets of conditions can be set on a field at one time.  If we need more than that then what?  Well, we can do that job ourselves by checking for the specific condition and applying whatever color we want to the font.

The Color Table.

For example, we have a Category Code Combo Box Field on the Form having values ranging from A to F. When the user selects one of these codes from the Combo box the item Description Field’s Font Color should change as per the following color table:

Color Table
Category Color Decimal Hex
A GREEN 32768 #008000
B BLUE 10485760 #000080
C LIGHT BLUE 16711680 #0000FF
D LIGHT GREEN 65280 #00FF00
E RED 128 #800000
F LIGHT RED 255 #FF0000

Design a Form.

Let us try this out on a sample Form.

  1. Open a new Form in Design View.

  2. Click on the Control Wizards button on the Toolbox to enable it.

  3. Select the Combo box Tool and draw a Combo box in the Detail Section of the Form.

  4. Select the Radio Button with the Caption: I will Type in the Values that I want then Click Next.

  5. Type A, B, C, D, E & F in separate rows under Col1 and Click on Finish Command Button.

  6. While the Combo box is still in the selected state display the Property Sheet (F4).

  7. Click on the Other tab of the Property Sheet.

  8. Change the Name Property Value to cboCat.

  9. Select the Event Tab on the Property Sheet.

  10. Select [Event Procedure] from the After Update Event Property.

  11. Click on the Build (. . .) button at the right edge of the After Update Event property to open the VBA Module of the Form.

    Form Module Code.

  12. Copy and paste the following Code overwriting the empty lines of the After Update Event Procedure:

    Private Sub cboCat_AfterUpdate()
    Dim strtxt, num As Integer
    Dim colr As Long
    strtxt = Nz(Me![cboCat], "")
    If Len(strtxt) > 0 Then
      num = Asc(strtxt) - 64
      colr = Choose(num, 32768, 10485760, 16711680, 65280, 128, 255)
      With Me!Desc
         .ForeColor = colr
      End With
    End If
    End Sub
  13. Close the VBA Editing Window to come back to the Form Design.

  14. Create a Textbox to the right of the Combobox.

  15. Display its Property Sheet (F4) and select the Other Tab.

  16. Change the Name Property Value to Desc.

  17. Save and Close the Form with the name Sample.

    Try out the Form.

  18. Open the Sample Form in Normal View.

  19. Type your name in the Text box.

  20. Try out our creation by selecting the Category Code (A, B, C, D, E, F) one after the other or in random order, and watch the color of your name change.

Technorati Tags:

Earlier Post Link References:


Duplicating fields with Conditional Formatting


To make data entry tasks faster in Microsoft Access we duplicate certain field values (Ctrl+") to bring them forward to the current record from the previous record field, wherever it becomes necessary.  If there is more than one field and needs to duplicate the same into more than one record, then repeating Ctrl+” everywhere is asking for more manual action, even though it is quicker than typing all the information literally.

For example, let us assume that we are creating a Mailing List of all Family members of all the residents in our locality and the sample data entry Form looks like the image given below:

If there are five members in a family, then the data fields Family Code and Address line field values must be repeated for each member of the family in the next four records, as shown in the next image.  Only the unique information needs to be keyed in.

A Sample Trial Run - Duplicating Field Values.

  1. Create a new table with the following structure and save it with the name FamilyDetails.

    Table: FamilyDetails
    Srl Field Name Data Type Field Size
    1 ID AutoNumber  
    2 FamilyID Integer  
    3 Title Text 15
    4 FName Text 25
    5 LName Text 25
    6 Add1 Text 50
    7 Add2 Text 50
    8 City Text 50
    9 State Text 50
    10 Country Text 50
    11 PIN Text 10
  2. Use the Form Wizard and create a Form in Column format, as shown in the image above, using the FamilyDetails Table and save the Form with the name frmFamilyDetails.

  3. Create a Textbox (wide enough to type a list of field names separated with commas) on the header of the Form as shown in the image above.

  4. Click on the Text Box to select it and display its Property Sheet (F4).

  5. Change the Name Property Value to FieldList.

  6. Create a Command Button below the Textbox, change its Name Property Value to cmdDup and change the Caption Property Value to Set Carry Forward.

  7. Display the Form’s VBA Module (Design - - > Tools - - > View Code). Copy and paste the following Code into the Form

    Module and save the form:

    The Form Module Code.

    Private Sub cmdDup_Click()
    'Set 'CarryForward' Text in the submitted field's TAG property
    SetTagValue Me, "FieldList"
    End Sub
    Private Sub Form_AfterUpdate()
    'Set Default Property Value
    SetDefaultValue Me
    End Sub
    Private Sub Form_Current()
    'Change color of Duplicated field to Red.
    SetColorChange Me
    End Sub
  8. Save and close the Form.

  9. Open the VBA Editing Window (ALT+F11).

  10. Create a new Standard Module (Insert - - > Module).

    Functions in Standard Module.

  11. Copy and paste the following VBA Code (consisting of three Functions) into the new Module and save it:

    Public Function SetTagValue(ByVal frm As Form, ByVal fldList As String)
    'Author: a.p.r. pillai
    'Date  : Feb 2012
    'Rights: All Rights Rserved by www.msaccesstips.com
    Dim txt, splt, ctl As Control
    Dim ctlName As String, ctlType As Integer, j As Integer
    Dim resetFlag As Boolean, ctrl_name As String
    resetFlag = False
    txt = Nz(frm.Controls(fldList).Value, "")
    ctrl_name = frm.Controls(fldList).Name
    If Len(txt) = 0 Then
       resetFlag = True
       'split the field list sepparate with commas
       'and load them into the Array: splt()
       splt = Split(txt, ",")
    End If
    '   Set frm = Me
       'initialize all Textboxes and Combobox Tag & Default Value
       'Properties, except the FieldList textbox.
       'and change all field's Forecolor to black. 
       For Each ctl In frm.Controls
          ctlType = ctl.ControlType
          ctlName = ctl.Name
          If ctlName = ctrl_name Then GoTo nextitem
          If ctlType = 109 Or ctlType = 111 Then
             ctl.Tag = ""
             ctl.DefaultValue = ""
             ctl.ForeColor = vbBlack
          End If
       frm.Repaint 'show the change color on the form
       If resetFlag Then 'if the fieldlist textbox was empty then exit
         Exit Function
       End If
    'Compare each field's name on the form with the field names 
    'selected and stored in the 'FieldList' Array.
       For Each ctl In frm.Controls
          ctlName = ctl.Name
          ctlType = ctl.ControlType
          'control-type 109 is Textbox and 111 is combobox
          'only these controls are duplicated
          If ctlType = 109 Or ctlType = 111 Then
             For j = 0 To UBound(splt)
                'if a match found then change it's Tag Property Value to 'CarryForward'.
                If Trim(splt(j)) = ctlName Then
                   ctl.Tag = "CarryForward"
                End If
          End If
    End Function

    Public Function SetDefaultValue(ByVal frm As Form)
    'Author: a.p.r. pillai
    'Date  : Feb 2012
    'Rights: All Rights Rserved by www.msaccesstips.com
    'Run this Procedure when the Form_Update() Event Procedure fires.
    Dim ctl As Control
    For Each ctl In frm.Controls
    If ctl.ControlType = 109 Or ctl.ControlType = 111 Then
          'if a control found with it's Tag Property set with the text 'CarryForward'
          'copy the current field value into the Default Property.
          'When the new record is created the Default Value will automatically
          'appear in the new record.
          If ctl.Tag = "CarryForward" Then
              ctl.DefaultValue = Chr$(34) & ctl.Value & Chr$(34)
          End If
               'change the color of the data to black
               ctl.ForeColor = vbBlack
    End If
    Next ctl
    End Function

    Public Function SetColorChange(ByVal frm As Form)
    'Author: a.p.r. pillai
    'Date  : Feb 2012
    'Rights: All Rights Rserved by www.msaccesstips.com
    'Note  : This Function is run from the Form_Current() Event Procedure.
    Dim ctl As Control
    If frm.NewRecord Then
         For Each ctl In frm.Controls
             If ctl.ControlType = 109 Or ctl.ControlType = 111 Then
                'Format the copied values with Red Color.
                If ctl.Tag = "CarryForward" Then
                   ctl.ForeColor = vbRed
                End If
             End If
    End If
    End Function

    Data Entry.

  12. Open the FamilyDetails Form in Design View.

  13. Key in your own personal information (or the sample data from the first image above), but don't move to the next new record now.

  14. Key in the following data field names in the text box, in the header of the Form, separated with commas.


    Set the Child Labels of TextBoxes with the actual field names (as shown in the above image) so that Users can type the required field names correctly from these labels.

  15. Click on the Command Button.

    Note: The Command Button Click Event Procedure calls the SetTagValue() Function, with the Form object and the Textbox name: FieldList as parameters, that reads the Field List and records the text 'CarryForward' in each field's Tag Property. Remember, we have not yet updated the current record in the Table.

  16. Press Ctrl+S to update the current record and run the Form_AfterUpdate() Event Procedure that runs SetDefaultValue() function.

    The current record field (having their Tag Property set with the text 'CarryForward') values are copied into the Default Value Property of that field. The default values will appear in those fields in a new record automatically.

    Note: The Tag Property Value & Default Value is saved in those properties in Form View, not in the design view. When you close the Form these settings are lost from these Properties. If you open the Form again, you must specify the list of fields in the text box and click the Command Button, to set the 'CarryForward' text in the Tag Property again.

    But, if you want certain fields to be assigned to a permanent duplication feature on new records, then open the form in the design view and type the text CarryForward in the Tag Property manually and save the Form. In that case, doesn't need this TextBox for the field list and Command Button on the header of the Form.

  17. Press Ctrl++ to create a new record.

    The values we have saved in the Default Value Property (through the Form_AfterUpdate() event procedure) appear in the new record. The Form_Current() event procedure runs too, and the color of the duplicated text changes to red. Now, all that's to do is to key in the rest of the information.

    The Ctrl+S first to update the current record and then the Ctrl+Plus next to create a new record step we have taken only to explain what happens at each stage.

    After keying in the data on a record the User can directly press Ctrl+Plus (click on the New (Blank) Record control on the navigation control at the bottom of the form) to create a new record. This action will also fire the Form_AfterUpdate() event first and then the Form_Current() event next.

Technorati Tags:




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code