Use the value of a control to effectively act like a variable in CF Rule expressions.

[Bubba002]

I currently have my Conditional Formatting expressions hard-coded on a subform. An example is as follows:
ControlName Rule 1 Rule 2
qryIS-1.Loc Value <> [tblqryIS-1c.Loc ] Expression is IsNull([tblqryIS-1c.Loc])
qryIS-1.Type Value <> [tblqryIS-1c.Type] Expression is IsNull([tblqryIS-1c.Type])
qryIS-1.Signal Value <> [tblqryIS-1c.Signal] Expression is IsNull([tblqryIS-1c.Signal])

I have an unbound control ‘txtCFV’ on the main form that correctly returns the subform control names with my prefix as they are entered. Example:
tblqryIS-1c.Loc
tblqryIS-1c.Type
tblqryIS-1c.Signal

My aim is to use the value of txtCFV as a variable in the CF Rule expressions. This will allow my code to be universal. I wouldn’t have to hard-code every form, every modification, etc. Using txtCFV, I modified the CF Rule expressions to:
qryIS-1.Loc Value <> Forms![frmIS-1c]![txtCFV] Expression is IsNull(Forms![frmIS-1c]![txtCFV])
qryIS-1.Type Value <> Forms![frmIS-1c]![txtCFV] Expression is IsNull(Forms![frmIS-1c]![txtCFV])
qryIS-1.Signal Value <> Forms![frmIS-1c]![txtCFV] Expression is IsNull(Forms![frmIS-1c]![txtCFV])

However, this is not working. There are no popup errors.

I read where Access conditional formatting expression cannot directly contain a variable in the traditional sense, but you can achieve a similar effect by using the value of a control on the form within the expression, effectively acting like a variable in the context of that specific form instance. This is what I am trying to achieve.
I have had many attempts at using the Me! self-identifier for this purpose, but none have worked. When Access goes to execute the CF expression, somehow it knows what control it is on. I just don’t know how to capture that.

Any ideas on how to go about this?

 

[theDBguy]

Can't test it right now but maybe try using TempVars too just to see if it makes any difference.

Sent from phone...

 

[MajP]

Me! self-identifier for this purpose, but none have worked.

Yes, Me will not work in any expression. Only within a vba class module (a form and report module is a class module)

You should be able to add all of those conditions in code since your naming convention is exact. See my example from the other post.

 

[MajP]

So this worked for me since your naming is exact. I just add the conditions dynamically on load. Just tag all controls with CF that get formatting.

If any control has different formatting do not tag it and do it manually.

Private Sub Form_Load()
Dim ctrl As Access.Control
Dim FC As FormatCondition
Dim fieldName As String

For Each ctrl In Me.Controls
 
  If ctrl.Tag = "CF" Then
   ctrl.FormatConditions.Delete
   fieldName = Split(ctrl.Name, ".")(1)
   Set FC = ctrl.FormatConditions.Add(acFieldValue, acNotEqual, "[tblqryIS-1c." & fieldName & "]")
   FC.ForeColor = vbBlue
   Set FC = ctrl.FormatConditions.Add(acExpression, , "isnull([tblqryIS-1c." & fieldName & "])")
   FC.ForeColor = vbBlue
End If

Next ctrl
'[tblqryIS-1c.Loc]
'IsNull ([tblqryIS-1c.Loc])


End Sub

 

[Bubba002]

Yes, my database application uses different prefixes to distinguish 'live' data from 'aged' data.
This looks like a very good solution for making the CF Rule universal. No more Conditional Formatting one control at a time!
I'll review it and give it a try tomorrow.
Thanks again for all you help.

 

[Bubba002]

It looked straightforward, so I gave it a try. Not working.

I removed the old CF rules and added your code above, but it didn't work. I'm not familiar with the Split() function. From what I just read, it looks like it will give two strings.
If my ControlName is qryIS-1.Loc, for the line fieldName = Split(ctrl.Name, ".")(1), the result would be {"qryIS-1", "Loc"}. But I just need string #2.

All I need in the Set FC line is just "Loc". I believe I'm close.

I tried fieldName = Mid(ctrl.Name, 9, 20) which would give me just "Loc", but that didn't work either. My main form has [Event Procedure] for the On Load event, which I believe is correct. I also changed the text color to vbRed.

Is my syntax incorrect, for either the Split() function, or the Mid() function? I appreciate any help.

 

[MajP]

I will send you my version it works for me. Also no need to remove the CF, the code dynamically removes old CF and adds in new CF. It is purely dynamic so they are not saved.

 

[MajP]

I added a bunch of random formatting to show what was possible via code.

 

[MajP]

There are many reasons that your original approach could not work.

1. Imagine in a control you have the name of another control
Lets say txtCFV had the value in it "tblqryIS-1c.Loc", but you really want the value of [tblqryIS-1c.Loc]
So
IsNull(Forms![frmIS-1c]![txtCFV]) is false because the value of txtCFV is the string "tblqryIS-1c.Loc"
but I think what you are hoping to return is the value of [tblqryIS-1c.Loc]

In vba you can do this, but only in Dot notation not bang notation.
Expressions unfortunately only support Bang notation.
example

Dim x as string
x = "[tblqryIS-1c.Loc]"
if isnull(Forms![frmIS-1c].controls(x)) then

that would evaluate [tblqryIS-1c.Loc]

There is no way to do that in pure Bang notation so no way to do it in an expression
This does on work

Dim x as string
x = "[tblqryIS-1c.Loc]"
if isnull(Forms![frmIS-1c]!(x)) then

The workaround could be to make a custom function in the form. Expression can use functions.

Public Function GetValueFromCFV() as
  GetValueFromCFV = Forms![frmIS-1c].controls(Forms![frmIS-1c].txtCFV.value).value
  'you can remove the value only added to show the default property
 end Function

Then in your CF you could do
IsNull(GetValueFromCFV())

This will check if the value of the control named in CFV is null.

2. That is still not going to work. CF paints when the row paints. So every control would check against whatever value is in txtCFV. So it would be correct for one field and all other fields would be comparing against the wrong field. This would change as each field gets focus, but again it would at best only be correct for the field with the focus and all other controls are comparing against the wrong field. They need to compare against the corresponding field in tblqryIS-IC.

 

[Bubba002]

Thanks for the input. This is turning out to be a great learning experience for me.

Embarrassing. Last night I copied my subformIS-1a to subformIS-1c to keep subformIS-1a as it was for use on other existing forms. I forgot to copy the new code to new subformIS-1c. Now that my code in on the correct subform, when I opened it this morning I get
Run-time error "9": Subscript out of range.
Debug points to the line fieldName = Split(ctrl.Name, ".")(1)

Your code worked perfectly on the file you sent. I copied it exactly to my subform. Why would it work on your file and not mine? This is a real puzzle for me.

 

[Gasman]

Learn to walk your code with F8 and breakpoints.
Does ctrl.name have a full stop in it?

 

[MajP]

The split command takes a string and spilts it at a specified substring
If your control name is "qryIS-1c.Loc" and I want to get bak "Loc"
Split(ctrl.Name, ".")(1) splits on each period and second item in the array is "Loc".
Split(ctrl.Name, ".")(0) returns the first item in the array "qryIS-1c"

I do this to get Loc and then append to the beginning tblqryIS-1c to get the matching control name.

If the control name does not have a period then there is only one item (0) in the array and this code is out of range

All your control names are of the form qryName.FieldName except Link1, 2, 3
You tagged the wrong contro check the LINK ones I did the same. Only tag the ones that have a matching field in tblqryIS-1c.

 

[MajP]

You could also error check this by making sure the name has a period using Instr and only split if it does.

 

[Bubba002]

Gasman,
Yes, I have used F8 to step through my code before. I'm not sure why, but I can't seem to get it to work on my form code now. Even when I go to an executable line and Run To Cursor, I just get the chime sound. This with the form closed, open, and in design mode.

MajP,
You are correct, and always know what to look for. I selected all the controls that were in a column, without considering that some were not to be shown on the form, and therefore did not need to be tagged. It's all working now, red text for changes, and the comparison tab showing current and previous data for all fields that changed on all records.

Thanks a whole bunch for all your help.

 

[Gasman]

You have to have the form open in form mode to test form code. Set breakpoints before wherever you want to test, then activate the form.

 

[Bubba002]

Just to follow up with y'all on my progress.

Gasman, I was able to step-in and use the Debug feature.

MajP, I noticed that it took ~4-5 seconds to load my form. The VBA has to apply two (2) rules to all the controls I had tagged (~115). When I had hard-coded all the CF Rules, my form would load in 1/2 second. So I set out to see if I could make the VBA-applied CF Rules permanent. It's possible! What I found is that after the form was opened, and going to design mode, the controls showed the CF Rules just as if they were done manually. So, added a line to my On Load event to just jump to the End Sub line, and then saved the form. I closed and then opened the form, and (delightfully) it opened in 1/2 second. I tested many of the controls and they are formatting correctly. Now I can apply any of my CF Rules without ever having to do them manually, one control at a time. In my application once the CF Rules are applied, there is no need to change them. Therefore, there is no need to run the code every time the form is used.

Thanks again, I really appreciate all your help guys.