How to change the font/color of form field values in VBA on the fly? 1

[Jawad77]

I have a recordet that I am updating in VBA. That recordset is based on a table and the table is the recordsource of my subform. How can I change the font of data values displayed on my subform' through textbox control when I update my recordet in VBA.

I like to change the font or color so that the user can see which data values of the particular field have changed when i update my recordset.

Thanks for your help in advance.

Jay

 

[PHV]

In the BeforeUpdate event procedure of the subform you may test the OldValue property of the bound controls.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

The conditional formatting feature is powerful if you know how to use it. You can set it to a boolean function by changing the first setting to "Expression Is" and the second to the name of the function, such as:

Expression Is : HasChanged("txtCompany")

If the function returns true, the format will automatically change. An example function might look like this:

Function HasChanged(ByVal strControlName As String) As Boolean
On Error GoTo ErrHandler
  Dim strVal As String
  Dim ctl As Object
  Set ctl = Me.Controls(strControlName)
  
  On Error Resume Next
  strVal = ctl.OldValue
  If Err = 0 Then
    If ctl.Value <> ctl.OldValue Then
      HasChanged = True
    End If
  End If
    
ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

The problem with using OldValue is that it updates with every change, so it might be better to capture the original "OldValue" in the Form_Current() event and store it in a module-level variable for comparison.

VBSlammer

Unemployed in Houston, Texas

 

[Jawad77]

VBSlammer,

I really appreciate your feedback on conditional formatting but I am having a hard time understanding your code. Can you be more specfic on how the above code will work.

I am new at VBA programming and doesn't know much about all the powerful features it offers. Thanks!

 

[VBslammer]

I've added some verbose commenting to make things more readable:

[green]
'********************
'*  §lamKeys §oftware® (mailto: programmer@coder.org)
'*
'*  @CREATED  :   1/12/2005 4:01:54 PM
'*  @PARAMS   :   strControlName - the name of the control.
'*  @RETURNS  :   True if the control's value is not equal
'*            :   to its OldValue, False otherwise.
'*  @NOTES    :   Null controls don't have an OldValue.
'*            :   This function must reside in the form
'*            :   module containing the controls.
'*  @USAGE    :   =HasChanged("Text1")
'********************[/green]
Function HasChanged(ByVal strControlName As String) As Boolean
On Error GoTo ErrHandler
  Dim strVal As String
  Dim ctl As Object
  
  [green]'Attempt to reference the control
  'using the argument passed in. If
  'an error occurs, the control doesn't
  'exist and execution will jump to the label
  'named 'ErrHandler:'[/green]
  Set ctl = Me.Controls(strControlName)
  
  [green]'Since some controls may not have an OldValue,
  'we're anticipating errors, so resume when that
  'happens and check the Err.Number to see what
  'happened.[/green]
  On Error Resume Next
  strVal = ctl.OldValue
  If Err.Number = 0 Then
  
    [green]'If we get here, no error occurred, so let's
    'compare the control's current value with its
    'OldValue. If they aren't equal, then set the
    'function's value to True for the return value.
    'If they are equal, we do nothing because the
    'function will return False by default.[/green]
    If ctl.Value <> ctl.OldValue Then
      HasChanged = True
    End If
  End If
    
ExitHere:
  [green]'This is the normal exit to the function[/green]
  Exit Function
ErrHandler:
  [green]'The only way this code will execute is if an error
  'occurs before we set the 'On Error Resume Next'
  'statement, which could happen when we try to
  'reference the control in the first step.[/green]
  Debug.Print Err, Err.Description
  [green]'This line clears the error and continues code
  'execution at the 'ExitHere:' label, ending
  'the function.[/green]
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas

 

[Jawad77]

Thanks a lot VBSlammer. I really appreciate your help