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]

Here are example lists of live and aged data with column header identifying the control name. FYI this is engineering data on instrumentation. The fields are for Location, Type, and Signal for the instrument. (MOV stands for Motor Operated Valve)

Live data

Aged data

For example, if the record for the Field MOV signal needs to change from 480VAC to 120VAC, in my comparison form when the change is saved in the control, it will automatically change the text from black to red. So any data shown in red identifies a change made since the last issue of the engineering data.

It would look like this on the screen:

So the way I actually use my prefixes is:
Live data prefix is always “qryIS-1”.
Aged data prefix is always “tblqryIS-1c”.

FYI, I use a key field to assure my records in each recordset are linked correctly.

Again, thanks for your help.


Bubba002
Technical User

 

[dhookom]

Pasting pictures of data makes helping more difficult. If you create a table or list I could simply copy and paste the data etc into a table.
Why don't I see any record with a value of Field - MOV - 120VAC?

Apparently your control names can't be relied on for consistent names, only the control sources/field names. Can I assume the record source of your form is something like:

SELECT [tblqryIS-1c].KeyField, [qryIS-1].Loc, [qryIS-1].Type, [qryIS-1].Signal, [tblqryIS-1c].Loc, [tblqryIS-1c].Type, [tblqryIS-1c].Signal
FROM [qryIS-1] INNER JOIN [tblqryIS-1c] ON [qryIS-1].KeyField = [tblqryIS-1c].KeyField;

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

 

[Bubba002]

Sorry about that. Here are the tables converted to text with a tab separator for the columns. Just copying tables just concatenated the text in every row when I did the ‘Preview’.

Live data

qryIS-1.Loc	qryIS-1.Type	qryIS-1.Signal
Field		2Pos SW		120VAC
Field		PB		120VAC
Field		PB		120VAC
02129-UC-001	DI		120VAC
DCS-N.Fracs	DI-soft		---
DCS-N.Fracs	DO-soft		---
DCS-N.Fracs	DO-soft		---
02129-UC-001	DO		120VAC
02129-UC-001	DO		120VAC
Field		MOV		480VAC
Field		LIM SW		---
Field		LIM SW		---
02129-UC-001	DI		120VAC
02129-UC-001	DI		120VAC
DCS-N.Fracs	DI-soft		---
DCS-N.Fracs	DI-soft		---
DCS-N.Fracs	STATUS		---
DCS-N.Fracs	STATUS		---
DCS-N.Cons	DI-soft		---
DCS-N.Cons	DI-soft		---
DCS-N.Cons	STATUS		---
DCS-N.Cons	STATUS		---
Field		RELIEF VAL	---

Aged data

tblqryIS-1c.Loc	tblqryIS-1c.Type	tblqryIS-1c.Signal
Field		2Pos SW			120VAC
Field		PB			120VAC
Field		PB			120VAC
02129-UC-001	DI			120VAC
02129-UC-001	DO			120VAC
02129-UC-001	DO			120VAC
Field		MOV			480VAC
Field		LIM SW			---
Field		LIM SW			---
02129-UC-001	DI			120VAC
02129-UC-001	DI			120VAC
DCS-N.Fracs	STATUS			---
DCS-N.Fracs	STATUS			---
DCS-N.Cons	STATUS			---
DCS-N.Cons	STATUS			---

Changed data

Loc		Type	Signal
Field		2Pos SW	120VAC
Field		PB	120VAC
Field		PB	120VAC
02129-UC-001	DI	120VAC
02129-UC-001	DO	120VAC
02129-UC-001	DO	120VAC
Field		MOV	120VAC
Field		LIM SW	---
Field		LIM SW	---
02129-UC-001	DI	120VAC
02129-UC-001	DI	120VAC
DCS-N.Fracs	STATUS	---
DCS-N.Fracs	STATUS	---
DCS-N.Cons	STATUS	---
DCS-N.Cons	STATUS	---

Every line is a record for an instrument. So on the sixth row, there is an instrument that is located in the Field, the instrument is an MOV, and the signal level for that instrument is 480VAC.

Note that in the changed data table I reduced the main forms column header to just show the field name (for example “Signal”) and not the complete control name ([qryIS-1].Signal).

The subform data is a query and is shown below:
SELECT [qryIS-1].*, [tblqryIS-1c].*
FROM [qryIS-1] LEFT JOIN [tblqryIS-1c] ON [qryIS-1].Link1 = [tblqryIS-1c].Link1
ORDER BY [qryIS-1].Link0, [qryIS-1].s3;

This way, no mater how many new fields are added to the database, I won’t need to update any of my working queries.
I had to add tabs so that in Preview columns would line up.
Also, I don’t know how to remove the “:AttachMate solutions Links” that show up in Preview.

Thanks.

Bubba002
Technical User

 

[dhookom]

Neither the live data not the aged data have these values however you show this in your changed days. I don't understand.
[pre]Field MOV 120VAC[/pre]
Also shouldn't you provide the primary key values so we know which records represent a pair?

If the fields are continually changing, how do you build your form? Do you build a new form each time?


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

 

[Bubba002]

Sorry, in dealing with the Preview issues I forgot to change the data in my database before I copied over to format it to post.
The aged data has the record as:
Field MOV 480VAC
As the project proceeds, I need to change that MOV signal to 120VAC. I use the comparison form to make the change to the live data to:
Field MOV 120VAC
The text is automatically changed to red.
I've corrected the lists, and, I've added the color to show how it looks on the screen in the Changed data list. All three are shown below.

[u]Aged data[/u]
kniL1		tblqryIS-1c.Loc	tblqryIS-1c.Type	tblqryIS-1c.Signal
02135-0599 11	Field		2Pos SW			120VAC
02135-0599 12	Field		PB			120VAC
02135-0599 13	Field		PB			120VAC
02135-0599 14	02129-UC-001	DI			120VAC
02135-0599 18	02129-UC-001	DO			120VAC
02135-0599 19	02129-UC-001	DO			120VAC
02135-0599 20	Field		MOV			480VAC
02135-0599 21	Field		LIM SW			---
02135-0599 22	Field		LIM SW			---
02135-0599 23	02129-UC-001	DI			120VAC
02135-0599 24	02129-UC-001	DI			120VAC
02135-0599 27	DCS-N.Fracs	STATUS			---
02135-0599 28	DCS-N.Fracs	STATUS			---
02135-0599 31	DCS-N.Cons	STATUS			---
02135-0599 32	DCS-N.Cons	STATUS			---

[u]Live data[/u]
qryIS-1.kniL1	qryIS-1.Loc	qryIS-1.Type	qryIS-1.Signal
02135-0599 11	Field		2Pos SW			120VAC
02135-0599 12	Field		PB			120VAC
02135-0599 13	Field		PB			120VAC
02135-0599 14	02129-UC-001	DI			120VAC
02135-0599 18	02129-UC-001	DO			120VAC
02135-0599 19	02129-UC-001	DO			120VAC
02135-0599 20	Field		MOV			120VAC
02135-0599 21	Field		LIM SW			---
02135-0599 22	Field		LIM SW			---
02135-0599 23	02129-UC-001	DI			120VAC
02135-0599 24	02129-UC-001	DI			120VAC
02135-0599 27	DCS-N.Fracs	STATUS			---
02135-0599 28	DCS-N.Fracs	STATUS			---
02135-0599 31	DCS-N.Cons	STATUS			---
02135-0599 32	DCS-N.Cons	STATUS			---

[u]Changed data[/u]
qryIS-1.kniL1	Loc		Type			Signal
02135-0599 11	Field		2Pos SW			120VAC
02135-0599 12	Field		PB			120VAC
02135-0599 13	Field		PB			120VAC
02135-0599 14	02129-UC-001	DI			120VAC
02135-0599 18	02129-UC-001	DO			120VAC
02135-0599 19	02129-UC-001	DO			120VAC
02135-0599 20	Field		MOV			[COLOR=#EF2929]120VAC[/color]
02135-0599 21	Field		LIM SW			---
02135-0599 22	Field		LIM SW			---
02135-0599 23	02129-UC-001	DI			120VAC
02135-0599 24	02129-UC-001	DI			120VAC
02135-0599 27	DCS-N.Fracs	STATUS			---
02135-0599 28	DCS-N.Fracs	STATUS			---
02135-0599 31	DCS-N.Cons	STATUS			---
02135-0599 32	DCS-N.Cons	STATUS			---

For some reason, the Preview doesn’t like the word L-i-n-k-1. So I spelled it backward on this post. That ‘word’ is the key field. It is created to ‘match’ the parent to the child fields, and be unique.

I use the same form most of the time on engineering projects. If a client wants to see some other information, I can just make a duplicate of my comparison form, and add/delete fields as necessary. The underlying tables contain all of the information. Sometimes clients want the same data but re-arranged a bit as well. I can use just one query for all those situations, and not have to define a unique query for each form.

The same query is also used for internal engineering use, stuff that doesn’t get formally issued to the client. So, basically the same query, just used for different purposes.


Bubba002
Technical User

 

[dhookom]

I created a form by dropping all the fields into the detail section and arranging them horizontally. Then I added this code the requires specifying the table/query names. The control names inherit the column names which are nearly the same as the control sources.

Private Sub Form_Open(Cancel As Integer)
    '? forms("frmConditionalFormatCode").Controls("[tblqryIS-1c.Loc]").FormatConditions(0).expression1
    ' [tblqryIS-1c.Loc]<>[qryIS-1.Loc]
    ' trap error 438 for non-control source
    Dim strPrefix1 As String         'text at beginning of a duplicate control source
    Dim strPrefix2 As String         'text at beginning of a duplicate control source
    Dim strFieldName As String
    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
    
    strPrefix1 = "tblqryIS-1c"       'the name of the first table/query
    strPrefix2 = "qryIS-1"           'the name of the second table/query
    
    intPrefixLen = Len(strPrefix1)
    
    On Error GoTo errFormOpen
    For Each ctrl In Me.Controls
        If Left(ctrl.ControlSource, intPrefixLen) = 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 & "]"
              Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
            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

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

 

[Bubba002]

Thanks dhookom, It looks like you changed the values with the 'tblqryIS-1c' prefix, which I use as the aged (archived) data, which would not change. Only the live data can change. I suppose that I can just change the prefix definitions to get it the way I want.

This got me to thinking, will this code only work to do the Conditional Formatting with the FormOpen event? Or will it persist, and automatically work to CF changes while the form is being used?


Bubba002
Technical User

 

[dhookom]

The conditions will persist until the form is closed. After opening the form, I changed some values and the values changed from black to red as expected. I suppose the code could loop through the text boxes to determine the prefixes of each table/query.

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

 

[Bubba002]

The controls on the subform are only for the live data (qryIS-1). The aged data is not shown, but is only l-i-n-ked to be able to be available in the recordset. To keep the code tight and efficient, could I change the code to only CF the live data controls? (The aged data is archived and will never change.)
I'll show my changes in red.

Private Sub Form_Open(Cancel As Integer)
    '? forms("frmConditionalFormatCode").Controls("[[COLOR=#EF2929]qryIS-1.Loc[/color]]").FormatConditions(0).expression1
    ' [COLOR=#EF2929][qryIS-1.Loc][/color]<>[COLOR=#EF2929][tblqryIS-1c.Loc][/color]
    ' trap error 438 for non-control source
    Dim strPrefix1 As String         'text at beginning of a duplicate control source
    Dim strPrefix2 As String         'text at beginning of a duplicate control source
    Dim strFieldName As String
    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
    
    strPrefix1 = "[COLOR=#EF2929]qryIS-1[/color]"               'the name of the first [COLOR=#EF2929](live)[/color] table/query
    strPrefix2 = "[COLOR=#EF2929]tblqryIS-1c[/color]"           'the name of the second [COLOR=#EF2929](old)[/color] table/query
    
    intPrefixLen = Len(strPrefix1)
    
    On Error GoTo errFormOpen
    For Each ctrl In Me.Controls        [COLOR=#EF2929] 'only live data controls on subform[/color]
        If Left(ctrl.ControlSource, intPrefixLen) = strPrefix1 Then 'find the controls to compare with CF
            strTB1 = ctrl.Name
            strTB2 = Replace(strTB1, [COLOR=#EF2929]strPrefix2)[/color]
            If ctrl.FormatConditions.Count = 1 Then
                ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, "[" & strTB2 & "]"
              Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
            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

Also, does the code account for the aged data being Null? When a project begins, much of the data is unknown and the fields are left blank. They are filled in as the engineering progresses. I recall that I had to add a second rule in the CF Rules definition box to account for that.

Thanks a bunch for your help.

Bubba002
Technical User

 

[dhookom]

The code sets the CF for one set of data only based on the prefix.

I feel like I have been doing all the work here. Can you do some of your own testing and modifications? I’m happy to answer questions if you get into a bind.

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

 

[Bubba002]

Thanks dhookom, yes I will do some testing. I need to study a bit more, but I see how it should work now.

Thanks again for all your help.

Bubba002
Technical User

 

[Bubba002]

Hey dhookom, I've tested several times and still don't get the changed text to turn red. I debug the code below but can't seem to get a breakpoint to work inside the 'If' statement. I addded variable strFieldName and confirmed that the code correctly proceeds thru all controls (live & old). intPrefixLen is always 7. However even with a breakpoint on lines for strTB1 and strTB2, my watch only shows those as string variables with "" as the value. Is there another way debug an If statement where I can see what is going on with strTB1 and strTB2?

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 control source
    Dim strPrefix2 As String         'text at beginning of a duplicate control source
    Dim strFieldName As String
    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
    
    strPrefix1 = "qryIS-1"           'the name of the first (live) table/query
    strPrefix2 = "tblqryIS-1c"       'the name of the second (old) table/query

    intPrefixLen = Len(strPrefix1)

        On Error GoTo errFormOpen
    For Each ctrl In Me.Controls     'live & old data controls on subform
    [COLOR=#EF2929]strFieldName = ctrl.ControlSource[/color]
        If Left(ctrl.ControlSource, intPrefixLen) = strPrefix1 Then  'find the controls to compare with CF
            strTB1 = ctrl.ControlSource
            strTB2 = Replace(strTB1, strPrefix1, strPrefix2)
            If ctrl.FormatConditions.Count = 1 Then
                ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, "[" & strTB2 & "]"
              Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
            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 would add in some debug.print statments.

 For Each ctrl In Me.Controls     'live & old data controls on subform
    debug.print "Name: " &ctrl.name, "ControlSource: " & ctrl.ControlSource

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

 

[Bubba002]

Hey dhookom, I'm getting much closer. I use ControlSource rather than Name because Name includes the Labels, and I'm just looking at the data controls. With that, I had to update my prefixes. In debug I can see all 5 'strFieldName' variables as well as strTB1 and strTB2. Both the .Modify and .Add lines are comparing acFieldValue to a string. I can't seem to get the value of my comparing control into those statements.
My database query has 127 fields. So for live and aged it's 254 controls. Most are text, but some are numbers (Double, Single, & Integer).
How do make my .Modify and .Add lines compare acFieldValue to the comparing control 'value'? My latest code is below:

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 control source
    Dim strPrefix2 As String         'text at beginning of a duplicate control source
    Dim strFieldName1 As String
    Dim strFieldName2 As String
    Dim strFieldName3 As String
    Dim strFieldName4 As String
    Dim strFieldName5 As String
    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
    
    strPrefix1 = "[COLOR=#EF2929][qryIS-1][/color]"           'the name of the first (live) table/query
    strPrefix2 = "[COLOR=#EF2929][tblqryIS-1c][/color]"       'the name of the second (old) table/query

    intPrefixLen = Len(strPrefix1)

        On Error GoTo errFormOpen
    For Each ctrl In Me.Controls     'live & old data controls on subform
[COLOR=#EF2929]     strFieldName1 = ctrl.ControlSource
     strFieldName2 = Left(ctrl.ControlSource, intPrefixLen)
     strFieldName3 = Replace(strFieldName1, strPrefix1, strPrefix2)
     strFieldName4 = strPrefix1
     strFieldName5 = [strFieldName3]
[/color]
        If Left(ctrl.ControlSource, intPrefixLen) = strPrefix1 Then  'find the controls to compare with CF
            strTB1 = ctrl.ControlSource
            strTB2 = Replace(strTB1, strPrefix1, strPrefix2)
           If ctrl.FormatConditions.Count = 1 Then
                ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, [COLOR=#EF2929]"[" & strTB2 & "]"[/color][COLOR=#EF2929][/color]
    [COLOR=#EF2929]Debug.Print [strTB2][/color]
              Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
                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

Again, thanks for your guidance.


Bubba002
Technical User

 

[dhookom]

I'm not sure why you took out the debug line I suggested. Debug.Print everything or put a breakpoint in your code so you can hover over control names or whatever to check their values. I believe the "Value" properties should always be referenced in the code.

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

 

[Bubba002]

I have been using the debug.print line, I just moved it down where the code is working. I can get strTB1 and strTB2 printed correctly. I have tried to get their values to print using the following syntax:
Me.Recordset("fieldname") from this guidance:
Me.Recordset("fieldname") should work. And there is nothing wrong with using a variable rather than a literal fieldname as long as that variable contains the name of a legit field.

Also, tried
me.recordset.fields(strMyFieldName).value
Dim varValue2 As Variant
varValue1 = Me.strTB1.Value
varValue2 = Me.strTB2.Value
varValue1 = Me.[strTB1].Value
varValue2 = Me.[strTB2].Value
varValue1 = Me.["strTB1"].Value
varValue2 = Me.["strTB2"].Value
Dim varValue2 As String
varValue2 = "[" & strTB2 & "]"
Dim varValue2 As Variant
varValue2 = "[" & strTB2 & "]"

Each time I get Run Time Error = 13, Type Mismatch.

I removed all CF from one of my 127 fields, so the Else part of the second If statement should work. I think the If statements are not working because the controls value is not being used. I can't seem to get those values. The acFieldValue is always 0. Is my syntax wrong? I'd like to see the values in a debug.print line and break before I proceed to the if statement.
I moved the debug.print statement down to where the code just stops working as expected.

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 control source
    Dim strPrefix2 As String         'text at beginning of a duplicate control source
    Dim varValue1 As Variant         'value of live field data
    Dim varValue2 As Variant         'value of aged field data
    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
    
    strPrefix1 = "[qryIS-1]"           'the name of the first (live) table/query
    strPrefix2 = "[tblqryIS-1c]"       'the name of the second (old) 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) = strPrefix1 Then  'find the controls to compare with CF
            strTB1 = ctrl.ControlSource
            strTB2 = Replace(strTB1, strPrefix1, strPrefix2)
            varValue1 = Me.["strTB1"].Value
            varValue2 = "[" & strTB2 & "]"
    [COLOR=#EF2929]Debug.Print strTB1, varValue1, strTB2, varValue2[/color]
            If ctrl.FormatConditions.Count = 1 Then
                ctrl.FormatConditions(0).Modify acFieldValue, acNotEqual, "[" & strTB2 & "]"
 
              Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
                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

Thanks again, I do appreciate your guidance.


Bubba002
Technical User

 

[dhookom]

This code is not valid. Please see my code examples on how to reference values/controls based on names stored in strings.

varValue1 = Me.["strTB1"].Value
varValue2 = "[" & strTB2 & "]"

Can you just try what I tried for this? Create a form with this record source(I changed the Link1 to Link_1 after pasting below since:

SELECT [tblqryIS-1c].Link_1, [qryIS-1].Loc, [qryIS-1].Type, [qryIS-1].Signal,
 [tblqryIS-1c].Loc, [tblqryIS-1c].Type, [tblqryIS-1c].Signal 
FROM [qryIS-1] INNER JOIN [tblqryIS-1c] ON [qryIS-1].Link_1 = [tblqryIS-1c].Link_1;

Then drag the three tblqryIS-1C fields into the detail section of the form. Add this code to the On Open event of the form:

Option Explicit

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 = "tblqryIS-1c"       [COLOR=#4E9A06]'the name of the first table/query[/color]
    strPrefix2 = "qryIS-1"           [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) = 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 & "]"
            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

This works for me, how about you?

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

 

[Bubba002]

Thanks for your code examples on how to reference values/controls based on names stored in strings! I have that working now.

I just tried your code exactly on a test database and form, but it is not turning red. I checked the underlying data to be sure. Also, the Me(strTB2) is not bringing back the correct data (which is 480VAC for the MOV record) when printing, but actually brings back 120VAC. That is confusing.

The strTB1 and strTB2 variables are working correctly as I thoroughly checked using Watches during breakpoints. One thing I noticed is that ctrl.ControlSource has brackets, while ctrl.Name does not ("[qryIS-1.Signal]" vs "qryIS-1.Signal"). Because of that, the first If statement is never true. So I changed the statement to be:
If Left(ctrl.ControlSource, intPrefixLen + 2) = "[" & strPrefix1 & "]" Then

to account for the brackets, and that works. I was able to get into the first If statement code and do the Debug.Print statement.

With that working I decided to move to my database and forms. I reversed the strPrefix variables to:
strPrefix1 = "qryIS-1" 'the name of the first (live) table/query
strPrefix2 = "tblqryIS-1c" 'the name of the second (aged) table/query

just to keep my mind straight that I am comparing live data to aged data. So I am good up to defining strTB1 and strTB2. However the Me(strTB2) variable, and the "[" & strTB2 & "]" nomenclature still does not bring in the correct data in the print. However, I was able to get the red working when there was data in the aged, but not when it was blank with the following code! I tested and it was persistent too!

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 varField As Variant
    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 & "]"
               Else
                ctrl.FormatConditions.Add acFieldValue, acNotEqual, "[" & strTB2 & "]"
                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

I’m still a bit rusty. I can’t tell what line of code is working in the second If statement (the Modify line or the Add line).

So now I am going to try to add a line for another CF condition as
IsNull([tblqryIS-1c.”FieldName”])
Probably
IsNull("[" & strTB2 & "]")

If I can get that working, I’ll be close to finalizing the code and updating my comparison forms with it. I will need to remove the hard coding to keep coding as efficient as possible to get the best performance.

Again, thanks so much for your help.


Bubba002
Technical User

 

[dhookom]

If you don't close the form, you can try go to design view to see the conditional formatting information. You can also set a breakpoint in your code to step through line by line using the F8 key. I spent a lot of time on this in the break mode. Stepping through your code will allow you to see what lines are being run.

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

 

[dhookom]

I modified the code a little to add the [] to strPrefix1 when check the ControlSource. If interested, I can email a demo file.

Private Sub Form_Open(Cancel As Integer)
    '? forms("frmConditionalFormatCode").Controls("[tblqryIS-1c.Loc]").FormatConditions(0).expression1
    ' [tblqryIS-1c.Loc]<>[qryIS-1.Loc]
    ' trap error 438 for non-control source
    Dim strPrefix1 As String         'text at beginning of a duplicate control source
    Dim strPrefix2 As String         'text at beginning of a duplicate control source
    Dim strFieldName As String
    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
    
    strPrefix1 = "tblqryIS-1c"       'the name of the first table/query
    strPrefix2 = "qryIS-1"           'the name of the second table/query
    
    intPrefixLen = Len(strPrefix1)
    
    On Error GoTo errFormOpen
    For Each ctrl In Me.Controls
        If Left(ctrl.ControlSource, intPrefixLen[highlight #FCE94F] + 2[/highlight]) = [highlight #FCE94F]"[" &[/highlight] strPrefix1 [highlight #FCE94F]& "]"[/highlight] Then 'find the controls to compare with CF
            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 & "]"
            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

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