Pass a subform fieldname-ID as a string variable in CF expression

[Bubba002]

How do you pass a subform fieldname-ID as a string variable in Conditional Formatting expression.

I have two tables that are identical. One is my “live” data, and one is “aged” data. I compare fields in “live” data with the identical field in the “aged” data in a query that contains both tables. This way I can show changes on a subform instantly when the live data is changed from the aged data by making the text color red.

In the example above, the “Signal” field in the live data is being compared to the “Signal” field in the aged data. While this works, I had to hard code the field for each expression, as well as in each field I want to compare. This is very prone to syntax and typo errors. As my database changes with use, adding new fields, etc., this has become a time consuming effort to check, identify, and fix any errors.

I’m looking for a way to have a conditional formatting expression for a field to always refer to the same “fieldname” having the formatting rule applied. I have tried the “Me.” and “Me!” self-identifiers with no success. Access must know what field the conditional formatting rules are being applied for. I just don’t know how to extract and capture that info to do what I need.

So in the example above, I would like to see a variable in the two conditional formatting expressions for “Signal”. Is there a way to define such a variable for a subform? Or perhaps there is another (ie.better) way of accomplishing this. Any ideas would be greatly appreciated.

 

[dhookom]

You should be able to use VBA to set the CF as per this video from Richard Roth.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Bubba002]

Thanks dhookom for the reference, but Richard Roth is offering some classes showing many ways to do CF with code. I have been looking into this further and have come across an example by MajP in 2020 that is very similar to what I need to do. I just can't seem to get it to work on my database.

The example MajP used was on a OnGotFocus event. I have tried using the following procedure to define a variable (SVI) to use in the CF expression. But I'm not having any luck. I always get the <Out of context> error message.

Option Compare Database

Private Sub qryIS_1_Signal_GotFocus()
' Create a Script Variable Indirection (SVI) ID for Conditional Formatting expression with an
' “Got Focus” event.

Dim SVI_FieldName As String
Dim SVI As String

' Capture current Control Name value
SVI_FieldName = Me.ActiveControl

' Define SVI as current “SVI_FieldName” without preceding table-ID.
' For example if a SVI_FieldName “qryIS-1.Signal”, I only want “Signal”.

SVI = Mid([SVI_FieldName], 8, 20)
End Sub

Can you see what I may be doing wrong?
I believe once I can define the variable (SVI), I will be able to use it in the CF expression definition. Again, thanks for your help.

 

[dhookom]

Search the web for "Access formatcondition.add".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

If your form is not datasheet or continuous, you may be able to set the conditional formatting via code in the On Current event. The following compares the values of Quantity and Quantity1 and sets the background to yellow if different.

Private Sub Form_Current()
    Dim ctrl As TextBox
    Set ctrl = Me.Quantity2
    If ctrl.FormatConditions.Count = 1 Then
        ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, Me.Quantity
     Else
        Me.Quantity2.FormatConditions.Add acFieldValue, acNotEqual, Me.Quantity
    End If
    ctrl.FormatConditions(0).BackColor = vbYellow
End Sub

I'm not sure this can be dynamic in a continuous form.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

I found a method for assigning control names dynamically.

Private Sub Form_Open(Cancel As Integer)
    Dim ctrl As TextBox
    Dim strTB1 As String   [COLOR=#A40000]'name of one control to compare[/color]
    Dim strTB2 As String   [COLOR=#A40000]'name of other control to compare[/color]
    strTB1 = "Quantity"
    strTB2 = "Quantity2"
    
    Set ctrl = Me(strTB2)
    ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB1 & "]"
    ctrl.FormatConditions(0).BackColor = vbYellow
    
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Bubba002]

Thanks dhookom, for your example of assigning control names dynamically. This looks just looks like what I could use.

Yes, my subform is formatted as datasheet.

What I am planning to do is use a function on the standard CF definition expression box for the "OnGotFocus" event for all the controls on my subform (guidance from MajP). This allows one function to handle multiple events. Since I am comparing the "current" data with the "aged" data for all the fields, it makes sense to have one function that can be used dynamically.

I just needed a way to capture the current fieldname as a string, that can then be manipulated to remove the unneeded "prefix". Then I can create a variable with the correct “aged” data prefix.

Below is something that I will try, …… tomorrow. Watching football today. Hope my syntax is correct.

Thanks for the help.

Private Function SetSVI()
Dim ctrl As TextBox
Dim strTB1 As String ‘ full name of current control
Dim strTB2 As String ‘ partial name of current control

strTB1 = Me.Name ‘ full name property of current control

‘ All the TextBox controls are named as “prefix.” & “fieldname”
‘ for their “Name” property. So for “qryIS-1.”, I need to remove
‘ the first 7 characters. The string variable I need begins at
‘ character 8.

strTB2 = Mid([strTB1],8,20) ‘ ”fieldname” only variable

‘ I can now use this variable to create the complete
‘ CF variable for use in the CF expression box.
‘ In my case the prefix is “tblqryIS-1c.” for the “aged” data.

Set ctrl = “tblqryIS-1c.” & “(strTB2)”

‘ I can now use this variable (ctrl) in the CF expression box.

 

[Bubba002]

I'm still not having any luck. I believe I understand how the code should be working, but, either the syntax is wrong, or there is something else that escapes me right now. For all 3 variables I get <Out of context> errors in the watch list. I am using my "SetSVI" function in the "OnEnter" event of the control. So whenever the control is entered, the function will automatically create the dynamic CF variable used in the CF expression box. See my code below:

Private Function SetSVI()

    Dim SVI As String       ' complete CF variable for expression
    Dim strTB1 As String    ' full name of current control
    Dim strTB2 As String    ' partial name of current control
    
'   Capture  full name property of current control
    strTB1 = Me.Name

' All the TextBox controls are named as “prefix.” & “fieldname” for
' their “Name” property. So for “qryIS-1.”, I need to remove the
' first 7 characters. The string variable I need begins at character 8.

'   Create ”fieldname” only variable
    strTB2 = Mid([strTB1], 8, 20)

' I can now use this variable to create the complete CF variable for use
' in the CF expression box.
' In my case the prefix is “tblqryIS-1c.” for the “aged” data. Must use
' underscore, so,“tblqryIS_1c.”
    
' Create complete CF variable
    SVI = "tblqryIS_1c." & [strTB2]

' I can now use this variable (SVI) in the CF expression box.

End Function

Now that I'm thinking about it more, this function should run when I open the subform, so that all the controls are correctly formatted with the CF expression. Will that happen using the "OnOpen" event for the subform?

Any help is appreciated. Thanks.

Bubba002
Technical User

 

[Andrzejek]

It is not a Function since it does not return any value. Well, it does return a SetSVI as Variant, but it is always empty.
And you do assign the value to SVI, but since it is declared locally, "I can now use this variable (SVI) in the CF expression box." - well, you cannot. SVI is not available anywhere outside this 'Function' :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Bubba002]

Thanks Andrzejek, I see my issue is a bit more complex than I thought. I'll need to re-think this on how to approach it. I'm going to continue because I believe there is a way, without having to hard code everything.

Bubba002
Technical User

 

[dhookom]

Me.Name is the form name. Set a breakpoint in your code so you can step through and troubleshoot.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Just my guess here, but looks like you want to have a Function to which you pass a control's name and return a "variable (SVI) [to be used] in the CF expression box".

If so, something like:

Private Function SVI(ByRef strCtrlName As String) As String
    SVI = "tblqryIS_1c." & Mid(strCtrlName, 8)
End Function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Bubba002]

Thanks dhookom, I just printed out a reference for Form and Subform properties and controls, I see the correct way to refer to a control on subform-1 from subform-1 is Me!ControlName. I'll be using that from now on.

Bubba002
Technical User

 

[Bubba002]

Thanks Andrzejek, that function looks just like what I want to do. But how do I make strCtrlName dynamically refer to the self-reference control name?

My subform contains many fields where ‘live’ data is compared to ‘aged’ data (datasheet view). So when the subform opens, all those fields need to have the same comparison to the ‘aged’ data to correctly Conditionally Format. I have it all working now by hard-coding each CF expression for each control (just to keep the workflow going). I was thinking if all the controls had the same function done on them, then on the subform opening, with the self-reference, the exact same function can be referenced on each control, thus eliminating the need for hard-coding each control. I got this idea from a post by MajP a few years ago (see link). What You Need to Know About Access Events | Access World Forums

I think this is better design, and it will make it easier to modify my database, as changes always seem to be needed.

To show what I am aiming for graphically, in the example below I need to replace the word “Signal” in the two CF rules with a variable (I’ll call it “SVI”).

Using your code with an OnEnter event on just one control, I’ve tried replacing strCtrlName with Me!Name, (Me!Name), [Me!Name], but they don’t work either. The error I get is:
The expression you entered has a function containing the wrong number of arguments.


Bubba002
Technical User

 

[dhookom]

You haven't shown us a copy of the desired results in your form view. I expect you could use the on open of a form to iterate through the controls taking the control name or control source to set the conditional formatting.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Bubba002]

Hey dhookom,below is what I want my CF Rules Manager box to look like:

Instead of hard-coding “[tblqryIS-1c.Signal]” with the ‘prefix’&’field’ (in this example the prefix is ‘tblqryIS-1c.’ and the field is ‘Signal’), I would use my variable (SVI). I would define the prefix as “tblqryIS-1c.”, and have the code self-reference each control in my subform with the field name to make the complete CF variable defined.

I will try executing the code with the subform ‘OnOpen’ event once I have the CF variable defined. I believe that would iterate through the controls at runtime giving each control a unique variable (SVI) related to it’s on control name.

But right now I can’t seem to get the syntax right, or something, because whenever I test my expression to define the CF variable I always get errors.

I could attach and example of the database if that would help identify what I am doing wrong. Again, thanks for your help.


Bubba002
Technical User

 

[dhookom]

I created similar tables with fields/text boxes that had a different prefix. My controls were named like:
[pre]PDT vPDT
UDT vUDT
GPM vGPM
MPDT vMPDT
MUDT vMUDT
MGPM vMGPM
PDT0 vPDT0[/pre]

The code allows you to enter the prefix which in my case is "v". When the form opens, there is code to loop through the controls finding those beginning with the prefix. It then sets the conditional formatting based on the "twin" control value.

Private Sub Form_Open(Cancel As Integer)
    Dim strPrefix As String         'text at beginning of a duplicate control name
    Dim strTB1 As String            'name of one control to compare
    Dim strTB2 As String            'name of other control to compare
    Dim intPrefixLen As Integer
    Dim ctrl As Control
    
    strPrefix = "v"
    intPrefixLen = Len(strPrefix)
    
    For Each ctrl In Me.Controls
        If Left(ctrl.Name, intPrefixLen) = strPrefix Then 'find the controls to compare with CF
            strTB1 = Mid(ctrl.Name, intPrefixLen + 1)
            strTB2 = ctrl.Name
            Debug.Print strTB1, strTB2
            If ctrl.FormatConditions.Count = 1 Then
                ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, Me(strTB1)
              Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB1 & "]"
            End If
            ctrl.FormatConditions(0).BackColor = vbYellow
        End If
    Next
End Sub

This is the form view. The duplicate text boxes are stacked. You can see the pairs where the values different will have the bottom one highlighted with a yellow background.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Bubba002]

Thanks dhookom, I'm going to take a close look at your approach.

Bubba002
Technical User

 

[Bubba002]

Hey dhookom, While looking close at your code,

The formula for defining variable strTB1 seems to remove the ‘prefix’ and returns only the ‘field’ name, which is what I am looking for in the CF expression box. However, I’m unclear on how the two If statements will work. In the Modify statement the current value is compared to itself?:
acFieldValue, acNotEqual, Me(strTB1). Am I missing something here?

Let me show how I use the prefix to determine what data is included.

Live data prefix is always “qryIS-1”. This is based on a parent-child relation of 2 tables.
Aged data prefix is always “qryIS-1c”. This is also based on a parent-child relation of 2 tables that have been saved at certain times during a project.
For performance reasons I have decided to create and use a table of the aged data (from “qryIS-1c”) for my comparison forms. (While forms with 2 queries works, comparing one query to a table greatly improves the performance). That being said, my aged data prefix is always “tblqryIS-1c”. My subform controls are the current data. So my CF expressions are looking at the current data, and comparing it to the aged data.

I can hard code the aged data prefix “tblqryIS-1c” in the CF expression for all controls, the only difference needed is the ‘field’ name for each control. That is why I’m trying to define a variable that self-references for each control. That way, my CF expression will be identical for all controls as they are added, modified, etc., thus reducing chances for errors as changes are made.

Thanks for the help.


Bubba002
Technical User

 

[dhookom]

your text boxes each have a copy with a different name. Do your text boxes have a pattern to their names? Or, is the pattern with a prefix just in the name of the field/control source? Maybe you could provide a two column list of the patterned names/sources.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016