Conditional formatting in MS Access is not working for certain fields on my form. I've searched for answers, tried the answers I've found and am still stuck.

I have a form with approximately 30 controls - some comboboxes and some text boxes (displaying dates). I have applied conditional formatting the the controls so that when the value has been edited it is shown as bold, italic, and with a yellow background. For most of the controls this works as desired. For a small few, which seem to have nothing in common, it does not work.

A possible clue is this - the formula of the condition is:

controlName.value <> controlName.oldvalue

For the controls where the conditional formatting works the formula is displayed in the conditional formatting dialog box exactly as above For the controls where it doesn't work the conditional formatting dialog box translates the formula to:

[controlName].[value] <> [controlName].[oldvalue]

I have tried creating new controls, using the default control name (thinking something may be corrupted with the original) but this doesn't solve the problem. I see the same behavior.

I have tried importing all of my objects into a new database (thinking there may be something corrupted with the database). No joy.

I've also run the debugger on my code and compacted and repaired the database. No change.

One of the fields where conditional formatting does not work is a textbox which displays a date field. The rest are comboboxes. I have other textbox date fields and comboboxes on the same form which work fine.

This is an MS Access 2010 .accdb file with linked tables to a MS SQL server 2016 server. The recordsource for the form is linked table which is a SQL Server view. The linked table has a primary key index.

I don't know what more I can tell you. Has anyone seen this problem?

  • Are these controls all bound? (They must be bound to have an oldvalue) Are there any subforms? (Subforms will require different syntax) – HackSlash Jan 16 at 17:58
  • Yes, these controls are all bound. These particular controls are on the main form (not a subform). – CanuckBuck Jan 17 at 18:33

After experimenting further I discovered the answer.

My rule formula did not account for the condition where the control's oldvalue could be NULL. When I changed the formula to;

controlName.value <> nz(controlName.oldvalue,0)

the formula works as desired.

I can't confirm this, because I don't know of a way to test the response of the formula as it's used in the conditional formatting dialog, but I suspect that the result of the formula evaluates to null when oldvalue is null.

P.S. I don't understand why the conditional formatting dialog box puts square brackets around some statements and not others but as it turns out, this is apparently not a factor.

  • The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a table\field. Where controls do not need the brackets. – HackSlash Jan 16 at 18:01
  • If this question is answered, please mark this answer with the green check mark so that this issue is "closed". You get points and people will stop trying to help you. – HackSlash Jan 16 at 18:02
  • Thanks for responding. Yes, my original question has been resolved. I tried to mark it as closed but it says I can't accept my own answer for 20 more hours. – CanuckBuck Jan 17 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.