Text Box ControlSource Property in Conditional Formatting Expression

[Bubba02]

Using Access 2002, I want to compare the data in table1 to the data in table2 as part of a form's conditional formatting expression. These two tables have identical structures. I thought that the "Me" self-reference could help with this, but perhaps my syntax is not right, or this is not do-able with conditional formatting.

My conditional formatting expression for a text box should be something like:
[table1.field1]=[table2.field1]

However, I want this rule to work for many (if not all) fields. Since each text box has a defined ControlSource property, I thought that I could use the same expression for each field I chose to use on the form as follows:
[table1.(Me.ControlSource)]=[table2.(Me.ControlSource)]

Any ideas to make this work?
Thanks in advance.

 

[Eupher]

Hi, Bubba02,

Wow, where to start... First, I've found that the [Me]! or [Me]. nomenclature doesn't seem to work in conditional formatting. [Form]! will work, but is redundant if the field in question is part of the form's recordset. Second, how is Access supposed to evaluate Me.ControlSource? ControlSource is not a property of the form object. Without specifically identifying the control, either by hard-coding it or somehow iterating through the controls collection, ControlSource is meaningless. You might try Screen.ActiveControl, but I have doubts that would work.

But the bigger problem is how are Table1 and Table2 related? If the only thing in common is their structure, how is Access to know which record from Table2 to compare to Table1 to evaluate the conditional formatting statement? If they are related via PK/FK, you'd be better off to build a query that includes both tables and use that as the RowSource for your form, and by extension the fields you need would be available for your conditional formatting.

Maybe if you post with a little more about the tables in question we can patch together a solution.

Ken S.

 

[Bubba02]

Hi, Eupher!

Thanks for the quick response. You cleared up something right away for me stating that ControlSource is not a property of the form object, but the [Me] self-identifier apparently is. I thought that the [Me] self-identifier could also be used for text box control references as part of a conditional formatting expression. This would simplify building my forms.

What I want to do is to show what data has changed over time by changing its color on the screen. I need to compare the ‘live’ data to the ‘aged’ data in order to accomplish this.

My database is basically comprised of two tables with a parent-child relationship. At some point in time, I save the two tables to capture the ‘aged’ data. Now I can build a query of the ‘aged’ data, and compare it to a query of the ‘live’ data. I need to build a third query to compare the two. This third query links the two comparison queries via a unique key (I call it Link2) for each child table.

With my comparison form built on the third query, I have the objects available to perform the comparison I need, namely:

[qryLive.field1]=[qryAged.field1]
[qryLive.field2]=[qryAged.field2]
[qryLive.field3]=[qryAged.field3] … etc.

Since the conditional formatting expression being entered is for a text box control who’s ControSource property is already defined, and I want to have this same comparison for every text box control on my form, I thought a self-reference identifier (of some type) could greatly assist in simplifying and building my forms. I was expecting something like:

[qryLive.Me.ControlSource]=[qryLive.Me.ControlSource]
[qryLive.Me.ControlSource]=[qryLive.Me.ControlSource]
[qryLive.Me.ControlSource]=[qryLive.Me.ControlSource]...etc.

By hard-coding the field names, it appears to work like I want, but it makes building the forms much harder and prone to errors.

On another note, in testing this, the performance of the form is O.K. as long as it is filtered via the parent record. When I attempt to open the child subform alone, it can take several minutes to open. Am I overlooking another way to accomplish my ‘live’ vs. ‘aged’ comparison with better performance?

I appreciate any ideas you have.

Bubba02

 

[Eupher]

Bubba02,

Me! is not a property of the form object, it's a short-cut means of explictly referencing the form object; it's equivalent to Forms!FormName. As far as I know, it only works in VBA code, and only within the form or report module from which it is called.

It sounds like you are looking for a re-usable expression, one that will pass the name of the current control as a variable (rather than hard-coding the name) for purposes of applying conditional formatting. There is a self-reference identifier for controls - I mentioned it in my previous post, Screen.ActiveControl, but I'm almost certain this will NOT work for conditional formatting because it only applies to a control that has focus.

Question: What is the view of the form on which you want to apply conditional formatting? Single? Datasheet? Continuous?

Ken S.

 

[Bubba02]

Hi, Eupher!

Thanks again for the quick response. I want to apply the conditional formatting on a subform with a ‘Continuous Forms’ view. When the subform is set to datasheet view, the formatting doesn't seem to apply.

Hard-coding things every time I build an object is an error prone work activity, which I’d like to avoid if at all possible. This is especially true when there is no ‘builder’ utility available (just the way it is with conditional formatting expressions).

To be sure I’m not overlooking something, I’ll look into your suggestion and try the Screen.ActiveControl reference, and see if that works. If you have any other suggestions, I’m all ears.

Bubba02

 

[Eupher]

Okay, there's a way to do it. You might find it more trouble than it's worth, but here's what I did:

1) Made 2 tables: tblMyTable1 and tblMyTable2. Each has 4 fields: RecID (autonumber), MyField1, MyField2, MyField3 (all text fields)

2) Populated the tables with some sample data, in this case, names of colors (Red, Orange, Yellow, whatever) such that some corresponding fields in the tables hold matching data.

3) Built a query incorporating only the 6 relevant fields from the two tables. Named the fields (this is important!) A1, A2, A3, B1, B2, B3 - i.e. A1: tblMyTable1.MyField1; A2: tblMyTable1.MyField2, etc.

4) Built a continuous form. I believe the order in which you place the fields on the form is significant! As that becomes, I think, the ordinal number which determines the order in which conditional formatting is processed (I tried tab order, didn't work). Named the controls txtA1, txtA2, etc. and bound them to A1, A2, etc.

5) Specified conditional formatting for each field. Expression is (without the quote marks) "MyCondFormat([Form])" - and simply changed the background color for when the condition is met.

6) Created a public variable in a module:

Public fldIndex As Integer

7) Created the following function in a module:

Public Function MyCondFormat(FormObj As Form) As Boolean
If fldIndex = 0 Then
    fldIndex = 1
End If

If FormObj("txtA" & fldIndex) = FormObj("txtB" & fldIndex) Then
    MyCondFormat = True
    Else
        MyCondFormat = False
End If

fldIndex = Choose(fldIndex, 2, 3, 1)

End Function

8) In the form's Close event, put

fldIndex = 0

(if this is the only form that would ever use the conditional formatting, could probably declare your variable and create the function in the form's module)

Okay, some major caveats here: this assumes ALL fields on the form are evaluated for conditional formatting - there may be a way to parse the field's ordinal number and compare it to the field's name, but I didn't want to go there... Also assumes the fields will be processed from left to right, top to bottom, i.e. Record1:Field1, Record1:Field2, etc. IOW, the code assumes the first field to be evaluated is named txtA1 and compares its value to that of txtB1; then it increments the fldIndex value, and goes to txtA2/txtB2, and so forth. This example includes 3 pairs of fields, the Choose statement needs to be altered if there are more/fewer.

This is kind of a kludge, but it works within the parameters I specified. Give it go and see if it works for you...

Ken S.

 

[TheAceMan1]

How are ya Bubba02 . . . . .

Using a function and an [blue]Unmatched Dynamic Query,[/blue] can do.
[ol][li]The [blue]PK[/blue] and [blue]FieldName[/blue] (the one your setting the conditional format for) are passed to the function.[/li]
[li]The query draws out any unmatched records for the field, between the two tables.[/li]
[li]A recordset opens the query.[/li]
[li]If the query result set is empty then the field matches in all records and false is returned.[/li]
[li]If one or more records are returned from the query, a lookup is performed to see if the passed PK is in the recordset. If found the function returns True, false otherwise.[/li][/ol]

To install the method, in a module in the modules window, copy/paste the following function ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Function IsNotMatched(ID As String, FN As String) As Boolean
   [green]'FN is FieldName, ID is PrimaryKey value[/green]
   Dim DB As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim tblOld As String, tblNew As String, PK As String
   
   Set DB = CurrentDb()
   tblOld = "[purple][b]OldTableName[/b][/purple]"
   tblNew = "[purple][b]NewTableName[/b][/purple]"
   PK = "[purple][b]PrimaryKeyName[/b][/purple]"
   
   SQL = "SELECT " & tblNew & "." & PK & ", " & tblNew & "." & FN & " " & _
         "FROM " & tblNew & " " & _
         "LEFT JOIN " & tblOld & " " & _
         "ON " & tblNew & "." & FN & " = " & tblOld & "." & FN & " " & _
         "WHERE (" & tblOld & "." & FN & " Is Null);"
   Set rst = DB.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      rst.FindFirst "[" & PK & "] = " & ID
      If Not rst.NoMatch Then IsNotMatched = True
   End If
   
   Set rst = Nothing
   Set DB = Nothing

End Function[/blue]

Next . . . in the [blue]Conditional Formatting[/blue] window for each field of interest:
[ol][li]Select [purple]Expression Is[/purple] in the combo.[/li]
[li]In the expression field copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]IsNotMatched([[purple][b]PrimaryKeyName[/b][/purple]],"[purple][b]CurrentFieldName[/b][/purple]")=True[/blue]

[/li][/ol]
If you get an error and the debugger stops on [purple]DAO.[/purple] in the function:
The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[blue]Thats it . . . give it a whirl & let me know . . .[/blue]

See Ya! . . . . . .

 

[Eupher]

But AceMan, that's the whole point! Bubba doesn't want to hard code the field name in the conditional formatting expression...

Ken S.

 

[Bubba02]

Hi, Eupher!

Thanks again for the quick response. This is an interesting solution. It will take me some time to test this one out, but I’ll get back with you and let you know how it went.

Bubba02

 

[TheAceMan1]

Howdy Eupher . . . . .

[blue]But AceMan, that's the whole point! Bubba doesn't want to hard code the field name in the conditional formatting expression...[/blue]

I fully understand, however as long as he wants to highlite indivivual controls in a record [purple]he's stuck![/purple]:
[ol][li][blue]If not identified[/blue], [purple]there's no way to know which conditional format for the specific textbox is making the function call[/purple] (and he has to use a function).[/li]
[li][blue]Since the function doesn't know who's calling[/blue], there's no way to tell what to pass back.[/li]
[li]I don't know what his query looks like, but unless its the equivalent of an [blue]UnMatched Query on a single field,[/blue] how's he to know the failing field to start with (which is what he's after)?[/li]
[li]Since he has to write the expression to each conditional format for each control of interest, [blue]whats the difference[/blue] if he identifies the field (he has to in this case anyway)?[/li][/ol]
So I'm saying no matter how we look at it, [purple]he has to identify the field.[/purple] I'd personally get it in there and get on with it! All attempts otherwise will fail . . .

If he just wanted to highlite the failing record, no problem, but this would be ambiguous at best . . .

See Ya! . . . . . .

 

[Eupher]

Howdy, AceMan,

Yup, problems noted - I agree with all your points. The only way my example works: it's based on the assumption that all fields are being processed for conditional format, and in a specific order (ordinal, I presume, tab order didn't work) - so there's no hard reference to field names. But if one deviates from the scenario, it certainly will not work as expected. It's definitely a kludge and not ideal, but was the only thing I could come up with that sorta did what bubba wanted. An interesting challenge, but I think I would pursue a different design or just hard-code it as you suggested.

Ken S.