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.

 

[Bubba002]

Yes, email your demo file and I’ll take a look at it.

I seem to be sooo close. I’ve been busy using F8 and stepping through most of today. I can tell what part of the If statements are running. I can see the values for the ctrl.FormatConditions(0).Modify and ctrl.FormatConditions.Add statements, but I only get the red for the acNotEqual statements. I can’t seem to get the red for when the aged data is Null. When hard coding, I was able to get everything to work with just two CF rules.

Here is my code as of today. The code actually executes Modify and Add statements on the first part of the second If statement. It also executes the two Add statements on the second part of the second If statement. However, I don’t get the red! I must be overlooking something.

Why can’t I make a second CF rule?

Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
    '?forms("frmConditionalFormatCode").Controls("[qryIS-1.Loc]").FormatConditions(0).expression1
    ' [qryIS-1.Loc]<>[tblqryIS-1c.Loc]
    ' trap error 438 for non-control source
    Dim strPrefix1 As String         'text at beginning of a duplicate (live) control source
    Dim strPrefix2 As String         'text at beginning of a duplicate (aged) control source
    Dim strField As String
    Dim strTB1 As String             'name of one (live) control to compare
    Dim strTB2 As String             'name of other (aged) control to compare
    Dim intPrefixLen As Integer
    Dim ctrl As Control
    
    strPrefix1 = "qryIS-1"           'the name of the first (live) table/query
    strPrefix2 = "tblqryIS-1c"       'the name of the second (aged) table/query
    intPrefixLen = Len(strPrefix1)

        On Error GoTo errFormOpen
    For Each ctrl In Me.Controls     'live & old data controls on subform
        
        If Left(ctrl.ControlSource, intPrefixLen + 2) = "[" & strPrefix1 & "]" Then 'find the controls to compare with CF
            strTB1 = ctrl.Name
            strTB2 = Replace(strTB1, strPrefix1, strPrefix2)
            If ctrl.FormatConditions.Count = 1 Then
                ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, "[" & strTB2 & "]"
                ctrl.FormatConditions.Add acExpression, acEqual, IsNull("[" & strTB2 & "]")
                strField = "Modify" & "  " & IsNull("[" & strTB2 & "]")
                Debug.Print strTB2, Me(strTB2), strField
               Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
                ctrl.FormatConditions.Add acExpression, acEqual, IsNull(Me(strTB2))
                strField = "Add" & "  " & IsNull(Me(strTB2))
                Debug.Print strTB2, Me(strTB2), strField
                End If
            ctrl.FormatConditions(0).ForeColor = vbRed
        End If
NextControl:
    Next
Exit Sub
    
errFormOpen:
    Select Case Err.Number
        Case 438  'no control source
            Resume NextControl
        Case Else
            MsgBox Err.Number, Err.Description
    End Select
End Sub

Bubba002
Technical User

 

[dhookom]

I'm not sure about your IsNull(Me(strTB2)) since it will return true or false.

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

 

[Bubba002]

Hey dhookom,
Your are correct, IsNull(Me(strTB2)) does return True or False. I understood that is what makes the text red or not.

When hard coding, the two rules that worked are:
Value <> [tblqryIS-1c.Signal]
Expression Is IsNull([tblqryIS-1c.Signal])

So far, these vba CF rules are working:

For 1 format condition defined;
ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, "[" & strTB2 & "]"

For 0 format conditions defined;
ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"

Both give me red when there is aged data, great! But I also want red when there is no aged data. Most controls are text, some are numbers.

Now, to add a second CF rule, I thought adding this line
ctrl.FormatConditions.Add acExpression, acEqual, IsNull("[" & strTB2 & "]")

below the working line would work. The line executes, and I think this should get a new CF rule added if the second expression is true. I’ve checked and when the aged data is Null, I get ‘True’ on the debug.print. But I don’t get the red.

Am I going about it wrong to create a second CF condition?

Should I need to use two similar If statements? One for ‘Value <> [tblqryIS-1c.Signal]’ hard-coded statement, and one for ‘Expression Is IsNull([tblqryIS-1c.Signal])’ hard-coded statement?
That wouldn’t be efficient code IMHO.

I’m still struggling. Why can’t I make a second CF rule?

Thanks again for your guidance.


Bubba002
Technical User

 

[dhookom]

I think you might need ”IsNull([" & strTB2 & "])"


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

 

[Bubba002]

I tried ”IsNull([" & strTB2 & "])" but I get error ‘Expected: end of statement’. What I am learning is that expressions are not intuitive and do not return what I might expect.

The following works for getting red:
ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"

The following works until I reach a record with Null in the aged data. Then I get error 13 Mismatched Type.
ctrl.FormatConditions.Add acFieldValue, acNotEqual, Me(strTB2)

Break.print is correct returning True or False for:
strField = "Add" & " " & IsNull(Me(strTB2))

Break.print is not correct for:
strField = "Add" & " " & IsNull("[" & strTB2 & "]")
as it always returns False.

These expressions are not consistent. It depends on code statement used.

One test I did was to remove the ‘.Modify acFieldValue’ statements and just have the ‘.Add acExpression’ statements.

That worked and gave me red when the aged data was Null.

Reading a post for ‘Conditional Formating with multiple conditions’ (just what I’m looking to do), this guidance was given:

In case anybody else needs something similar in the conditional formatting you have to go into Expression is and then your criteria... It makes so much sense now...

I am going to try a test with ‘acExpression, acEqual,’ and then an expression with an OR statement for both conditions I’m looking for.

I’ll let you know how it goes.


Bubba002
Technical User

 

[Andrzejek]

Break.print is not correct for:
strField = "Add" & " " & IsNull("[" & strTB2 & "]")
as it always returns False.

If that is NOT what you want, then - what do you want to return?

Something like this, maybe...?
[tt]strField = "Add IsNull([" & strTB2 & "])"[/tt]

Just a guess.

---- Andy

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

 

[dhookom]

You use the terms Live and Aged which I would like you to replace with tblqryIS-1c and qryIS-1. I don't know which is which and don't care to look through the long thread to find out. Which Null are you looking for: tbl or qry? When you add another condition, you need to add another format like:

ctrl.FormatConditions(1).BackColor = vbYellow

ForeColor of a null value won't show anything.

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

 

[Bubba002]

Hey dhookom,
Sorry, To clarify:
Live = qryIS-1
Aged = tblqryIS-1
I'm looking for Null in tblqryIS-1 (aged) data.

Basically, as new engineering data is entered it gets red.
First time 'new' data is looking at 'Null' and should be red.
Subsequent 'new' data is looking at 'Not Equal' and also should be red.

This way any engineer working in the database can identify what has changed since the last issue (to client). This is valuable as there are several iterations (issues to client) of the design during the execution of a project.

Bubba002
Technical User

 

[dhookom]

Based on my best guess, all formatting should be performed on text boxes bound to qryIS-1. Keep in mind if a value is null, there will not be text to make red. This is the code that should work:

Private Sub Form_Open(Cancel As Integer)
    [COLOR=#4E9A06]'? forms("frmConditionalFormatCode").Controls("[tblqryIS-1c.Loc]").FormatConditions(0).expression1
    ' [tblqryIS-1c.Loc]<>[qryIS-1.Loc]
    ' trap error 438 for non-control source[/color]
    Dim strPrefix1 As String         [COLOR=#4E9A06]'text at beginning of a duplicate control source[/color]
    Dim strPrefix2 As String        [COLOR=#4E9A06] 'text at beginning of a duplicate control source[/color]
    Dim strFieldName As String
    Dim strTB1 As String             [COLOR=#4E9A06]'name of one control to compare[/color]
    Dim strTB2 As String             [COLOR=#4E9A06]'name of other control to compare[/color]
    Dim intPrefixLen As Integer
    Dim ctrl As Control
    
    strPrefix1 = "qryIS-1"           [COLOR=#4E9A06]'the name of the first table/query[/color]
    strPrefix2 = "tblqryIS-1c"       [COLOR=#4E9A06]'the name of the second table/query[/color]

    
    intPrefixLen = Len(strPrefix1)
    
    On Error GoTo errFormOpen
    For Each ctrl In Me.Controls
        If Left(ctrl.ControlSource, intPrefixLen + 2) = "[" & strPrefix1 & "]" Then [COLOR=#4E9A06]'find the controls to compare with CF[/color]
            strTB1 = ctrl.Name
            strTB2 = Replace(strTB1, strPrefix1, strPrefix2)
            Debug.Print "strTB1: " & strTB1, Me(strTB1), "strTB2:" & strTB2, Me(strTB2)
            If ctrl.FormatConditions.Count = 1 Then
                ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, "[" & strTB2 & "]"
              Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
                ctrl.FormatConditions.Add acExpression, , "IsNull([" & strTB1 & "])"
            End If
            ctrl.FormatConditions(0).ForeColor = vbRed
            ctrl.FormatConditions(1).BackColor = vbYellow
        End If
NextControl:
    Next
Exit Sub
    
errFormOpen:
    Select Case Err.Number
        Case 438  [COLOR=#4E9A06]'no control source[/color]
            Resume NextControl
        Case Else
            MsgBox Err.Number & " " & Err.Description
    End Select
    
End Sub

If there is no conditional formatting set in design view, you could probably get rid of the If ctrl.FormatConditions.Count = 1 Then and related lines.

Send me your email if you would like a small sample database file.

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

 

[Bubba002]

Hey dhookom,
Eureka!!!
It's working, red for first time new data, and red for changed data!
Thanks so much for you help!

Rather than using one CF command based on multiple conditions, making two CF condition commands works perfectly. I did some quick testing as it is a subform, and it works on the main form as well. It's also persistent.

This subform was one tab on my main form. I have another issue on the second tab, but I'm taking a break for now. We're going to the Mardi Gras.

If your interested, my final code is below.
Thanks again for all your help.

Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
    '?forms("frmConditionalFormatCode").Controls("[qryIS-1.Loc]").FormatConditions(0).expression1
    ' [qryIS-1.Loc]<>[tblqryIS-1c.Loc]
    ' trap error 438 for non-control source
    Dim strPrefix1 As String         'text at beginning of a duplicate (live) control source
    Dim strPrefix2 As String         'text at beginning of a duplicate (aged) control source
    Dim strTB1 As String             'name of one (live) control to compare
    Dim strTB2 As String             'name of other (aged) control to compare
    Dim intPrefixLen As Integer
    Dim ctrl As Control
    
    strPrefix1 = "qryIS-1"           'the name of the first (live) table/query
    strPrefix2 = "tblqryIS-1c"       'the name of the second (aged) table/query
    intPrefixLen = Len(strPrefix1)

        On Error GoTo errFormOpen
    For Each ctrl In Me.Controls     'live & old data controls on subform
           If Left(ctrl.ControlSource, intPrefixLen + 2) = "[" & strPrefix1 & "]" Then 'find the controls to compare with CF
              strTB1 = ctrl.Name
              strTB2 = Replace(strTB1, strPrefix1, strPrefix2)
              ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
              ctrl.FormatConditions.Add acExpression, , "IsNull([" & strTB2 & "])"
              ctrl.FormatConditions(0).ForeColor = vbRed
              ctrl.FormatConditions(1).ForeColor = vbRed
        End If
NextControl:
    Next
Exit Sub
    
errFormOpen:
    Select Case Err.Number
        Case 438  'no control source
            Resume NextControl
        Case Else
            MsgBox Err.Number, Err.Description
    End Select
End Sub

Bubba002
Technical User

 

[dhookom]

Glad to hear you got this resolved. We both learned a few lessons regarding coding conditional formatting.

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