How to modify the Expression1 or 2 of an existing FormatCondition?

[Alfred Trietsch]

Hi,

I try to programmatically propagate the change of a name of a table field
through all my forms and reports, but got stuck with modifying conditional
formatting where the name change is to be applied to the condition
expressions themselves. So, the specified colors etcetera must not be
changed, only the conditions.

I tried

For Each obj In db.Containers("Forms").Documents
DoCmd.OpenForm obj.Name, acDesign
Set frm = Forms(obj.Name)
For Each ctl In frm.Controls
For i = 0 To ctl.FormatConditions.Count -1
Set fcd = ctl.FormatConditions.Item(i)
varOperator = fcd.Operator
varType = fcd.Type
varExpression1 = fcd.Expression1
varExpression2 = fcd.Expression2
varExpression1 = Replace(varExpression1, oldString, newString)
varExpression2 = Replace(varExpression2, oldString, newString)
ctl.FormatConditions.Item(i).Modify varType, varOperator,
varExpression1, varExpression2
Next i
Next ctl
DoCmd.Close acForm, obj.Name
Next obj

Other changes to the controls do get saved with this approach of looping
through the forms, but while the changes to the conditions seem to work in
debugging mode, they apparently are not saved to the forms (or reports
likewise).

Am I missing something, needs VBA to be told somehow that the form or report
has to be recompiled or whatever is wrong in the code?

Please help.

 

[Marshall Barton]

I try to programmatically propagate the change of a name of a table field
through all my forms and reports, but got stuck with modifying conditional
formatting where the name change is to be applied to the condition
expressions themselves. So, the specified colors etcetera must not be
changed, only the conditions.

For Each obj In db.Containers("Forms").Documents
DoCmd.OpenForm obj.Name, acDesign
Set frm = Forms(obj.Name)
For Each ctl In frm.Controls
For i = 0 To ctl.FormatConditions.Count -1
Set fcd = ctl.FormatConditions.Item(i)
varOperator = fcd.Operator
varType = fcd.Type
varExpression1 = fcd.Expression1
varExpression2 = fcd.Expression2
varExpression1 = Replace(varExpression1, oldString, newString)
varExpression2 = Replace(varExpression2, oldString, newString)
ctl.FormatConditions.Item(i).Modify varType, varOperator,
varExpression1, varExpression2
Next i
Next ctl
DoCmd.Close acForm, obj.Name
Next obj

Other changes to the controls do get saved with this approach of looping
through the forms, but while the changes to the conditions seem to work in
debugging mode, they apparently are not saved to the forms (or reports
likewise).

If I remember correctly, changes to CF items in code do not
mark the form as changed so Close thinks there is nothing
new to save.

I think I got around it by setting something else in the
form. Eg:
Me.Controls(0).Visible = Me.Controls(0).Visible

I also think you should explictly specify Close's Save
argument:
DoCmd.Close acForm, obj.Name, acSaveYes

 

[Alfred Trietsch]

If I remember correctly, changes to CF items in code do not
mark the form as changed so Close thinks there is nothing
new to save.

I think I got around it by setting something else in the
form. Eg:
Me.Controls(0).Visible = Me.Controls(0).Visible

I also think you should explictly specify Close's Save
argument:
DoCmd.Close acForm, obj.Name, acSaveYes

That did the trick.
Thanks a lot, you made my day!