Conditional Formatting on a Continuous Form 1

[markronz]

Hello, I have looked all of the internet and read about conditional formatting on a continuous form, but I still have some questions, as I am not able to get it to work.

Basically I have a continuous form that lists data from two tables. These two tables are identical, it's just that one has the "old" information in it, and the other has the "new" information in it. They are linked together by a key named corpID. On my continuous form, I have a series of boxes listing all of the Old information. For the control source of these textboxes, I use the DLOOKUP function. The reason I do this is because I want the people to be able to alter the "New" info table still. So I needed the form to reference only one table.

Then I have the exact same number of text boxes who's control source is linked to the "New" table.

So for reference, my form looks something like this...

corpID: [corpID from New Table, read only]
Current--
[=DLookUp for First Name on CorpID, read only] [=DLookUp for Last Name on CorpID, read only]
New--
[Control Source linked to FirstName in New table, editable] [Control Source linked to LastName in New table, editable]

Hopefully you are with me so far. What I want to do is bold the "New" textboxes when they are different than the "Old" information. So pseudo code, something like this:

if NewFirstName not equal to OldFirstName Then
    bold the NewFirstName
end if

I've tried going into conditional formatting and do exactly this:

1. Highlight the NewFirstName field
2. click FORMAT, CONDITIONAL FORMATTING
3. Set condition one so that 'Field Value Is' 'Not Equal to' '[OldFirstName].Text'

This does not work.

Does anyone have any idea why this does not work? Is it because of the DLookup as the control source? Or what? Or is there any other way to bold the different fields?

Thanks in advance!

 

[jamaarneen]

Hi,

try have a look at this thread, it might help.

thread702-1491577

 

[markronz]

Thank you kindly. I still cannot get it to work, although that does some answer questions because I wasn't using the 'Expression is' operation before.

I highlighted the NewFirstName field and then did 'Expression Is' and then I put this in there:

StrComp([NewFirstName].Text,[oldFirstName].Text)=False

Then Bold it.

This does not work.

I also tried:

[NewFirstName].Text <> [oldFirstName].Text

Then Bold it.

Neither of these bold the items. Does it have to do with the fact that the oldFirstName is not directly bound to a table? It is populated using the DLookup function.

Do you know if that would make it not work? If so, any idea what to do to get around this?

Thanks!

 

[jamaarneen]

Hi

in the thread i pointed, have a look at the second reply from TheAceMan1, where he writes that the control used in the expression should be bound.

so maybe this is the problem.

to be sure, you can experiment, by triyng it with tow bound controls.

or, try to put the whole dlookup syntax in the expression.

 

[dhookom]

You would rarely if ever use the "Text" property in Access. It is only valid when the control has the function. Try something like:
[tt][blue] <> [OldFirstName][/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

Duane,
you meant of course "when the control has the [blue]focus[/blue]"

 

[dhookom]

My bad. I did mean "focus". Did you try the expression?

Duane
Hook'D on Access
MS Access MVP

 

[markronz]

I did try it today. Neither option helps. Apparently it must be a bound field, otherwise it will not work.

I've changed the form to display the old and new information. the user will just have to compare the two themselves and see what's changed I guess...

 

[jamaarneen]

Hi markronz, never give up to fast

I'm not really sure, why cannot you use for the NEW a bound control, so you'll be able to work with conditional formatting?

Ja

 

[dhookom]

I was able to conditionally format a text box on a report based on a calculated text box.

In one case, I formatted a text box that was not the calculated text box formatting based on the calculated text box. In another case, I conditionally formatted the calculated text box based on its value.

Duane
Hook'D on Access
MS Access MVP

 

[markronz]

Ok, I apologize for my somewhat short response yesterday. I was just busy. I did give it another shot today.

First off, you were correct about the whole .Text thing. I removed that.

When I set up conditional formatting, if I choose 'Expression is' and then enter in "<> [OldName]" it gives me the error: "The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

However, if I change the condition to:
'Field Value Is' 'Not Equal to' '[oldName]'

This works! I know I tried this before, but I believe that I had .Text on the end of it. So that is most excellent! Thank you all very much for the help on that!

However, I have (hopefully) one last question about this. I noticed that the conditional formatting does not work if one of the fields is blank. So for instance if the OldName is showing up as 'Mark' and the NewName is blank, then the conditional formatting does nothing. I've changed it so rather than bolding the text (since you can't bold something that's blank!), that it will change the background color of the field to a different color. So does anyone have any idea how to make conditional formatting realize that a blank is different than a non-blank value?

 

[markronz]

Scratch that! I added another condition, something like this...

((IsNull([NewName])=True) And (IsNull([OldName])=False))

Then it works with blanks too!

Thanks again for the help everyone!

 

[markronz]

One last note. I had to add a condition to check the opposite of the one above. So there were three conditions for each field...

1. to check to see if they are equal
2. to check to see if the New was blank and the Old was not
3. to check to see if the Old was blank and the New was not

So that covers all the scenarios. Just thought I'd post it in case this happens to someone else some day!