MS Access Conditional formatting not working
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?
microsoft-access
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
add a comment |
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?
microsoft-access
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
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
add a comment |
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?
microsoft-access
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
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?
microsoft-access
microsoft-access
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
edited Jan 16 at 15:14
CanuckBuck
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
asked Jan 16 at 14:59
CanuckBuckCanuckBuck
167
167
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
add a comment |
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
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
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
Yes, these controls are all bound. These particular controls are on the main form (not a subform).
– CanuckBuck
Jan 17 at 18:33
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a tablefield. 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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1394979%2fms-access-conditional-formatting-not-working%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a tablefield. 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
add a comment |
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.
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a tablefield. 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
add a comment |
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.
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
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.
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
answered Jan 16 at 16:35
CanuckBuckCanuckBuck
167
167
The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a tablefield. 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
add a comment |
The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a tablefield. 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
The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a tablefield. Where controls do not need the brackets.
– HackSlash
Jan 16 at 18:01
The brackest are confusing because they can mean different things in different context. It usually denotes a reference to a tablefield. 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
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
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
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1394979%2fms-access-conditional-formatting-not-working%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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