Can't save changes made to conditional formatting in textbox

[sampun]

Hi all,
i'm trying to add some conditional formatting to a textbox within a form. The code works fine because I can see the effects of the conditional formats when I open the form in code. However, when I try to save it using:

DoCmd.Close acForm, FormName, acSaveYes

The changes I made in the code are not saved.

The strange thing is that if I save it manually (ie not using VBA code) while the form is still open, the conditional formatting is saved.

Also, if I add a break in the code at the end (before I save and close the form) then right-click on the textbox -> select 'Conditional Formatting...', I can see that the new conditional formats were added via vba. Then I would let the code finish. That also seems to save the changes I made using vba.

So does anyone know why I can't save the conditional formatting if I just let the code do the saving without interupting the code?

Many thanks,

Sam

The code:

Sub UpdateConditionalFormat(FormName As String, rs As DAO.Recordset, val1 As Double, val2 As Double)
Dim j As Integer
Dim i As Integer
Dim foundControl As Boolean
Dim objFrc As FormatCondition
j = 1
rs.MoveFirst
DoCmd.OpenForm FormName, acDesign
For i = 1 To rs.RecordCount
foundControl = False
Do While j <> Forms(FormName).Controls.Count And Not (foundControl) And Not rs.EOF

If Forms(FormName).Controls.Item(j - 1).Name = rs!nm_expiry Then
foundControl = True
If Forms(FormName).Controls.Item(j - 1).FormatConditions.Count = 0 Then
Forms(FormName).Controls.Item(j - 1).FormatConditions.Delete
Set objFrc = Forms(FormName).Controls.Item(j - 1).FormatConditions.Add(acFieldValue, acBetween, -val1, val1)
Set objFrc = Forms(FormName).Controls.Item(j - 1).FormatConditions.Add(acFieldValue, acBetween, -val2, val2)
Set objFrc = Forms(FormName).Controls.Item(j - 1).FormatConditions.Add(acFieldValue, acNotBetween, -val2, val2)

With Forms(FormName).Controls.Item(j - 1).FormatConditions(0)
.BackColor = RGB(189, 252, 201)
.Enabled = True
End With
With Forms(FormName).Controls.Item(j - 1).FormatConditions(1)
.BackColor = RGB(255, 246, 143)
.Enabled = True
End With
With Forms(FormName).Controls.Item(j - 1).FormatConditions(2)
.BackColor = RGB(255, 153, 18)
.Enabled = True
End With

End If
rs.MoveNext
End If

j = j + 1

Loop
Next

DoCmd.Close acForm, FormName, acSaveYes

 

[DJkarl]

Both of the other methods you mention have you saving the object before trying to close it. Have you tried a Docmd.Save before the Close?

 

[sampun]

I've tried :

DoCmd.Save acForm, FormName
DoCmd.Close acForm, FormName

and I've also tried:

Docmd.Save

Both methods give me the following error:

run-time error '29068':
Microsoft access cannot complete this operation. You must stop the code and try again.

 

[DJkarl]

Well if the error is specifically telling you it can't save while code is running I'm guessing that there isn't a way around this, at least not using your existing code.