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

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?

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.

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.