Conditional formatting using code (VBA?) 2

[rmork]

I'm trying to help a friend with his database containing ships, persons, projects, dockyards a.s.f..
He wants to format all the ship names in i subform that has active projects (the boat is in dock) formatted in red and bold

Is it possible to make tekst in a continues sub form red and bold if this code is met:

Function funcVesselsHasActiveProject(VesselID As Long) As Boolean

funcVesselsHasActiveProject = DCount("AVD01ProjectID", "AVD01Projects", "VesselID = " + CStr(VesselID) + " AND " + _
"ClosedDate IS NULL")

End Function

I am aware of the option "conditional formatting" under the format menu, but I'm not a programmer, and my knowledge is limited. Have tryed everything, but no luck so far...
Anyone??

 

[Remou]

Choose the textbox you wish to format (design view) and select Conditonal Formatting from the Format menu. Choose Expression Is and paste in the following:

[tt]Not IsNull(DLookUp("AVD01ProjectID","AVD01Projects","VesselID=" & [VesselID] & " And IsNull([ClosedDate])"))[/tt]

Select the font colour and so forth that you wish to use with this condition. The above assumes that VesselID is numeric, you will need single quotes if it is a text field.

 

[rmork]

Thank You Remou...
Will try this when I get home

 

[rmork]

Did not work. Got an error message when hitting "ok". something was wrong with the expression. (my Access is in norwegian, so difficult for me to translate the message, but something about an Operator or illegal use of characters etc.)
Hope that helps...

 

[Remou]

Have you included a space between the quote (") and 'And'? I tested in my version of Access, (2000, English) and it worked for me. If you wish to test the line, paste it into the immediate window (code window), prefixing the line with a question mark (?) and changing [VesselID] in '& [VesselID] &' to a real number. Another way to test would be to use the line in a query based on the table AVD01Projects.

 

[RoyVidar]

One thing to try, is using semicolon in stead of comma.

Short explanation - since Norwegian locale means comma is used as decimalseparator, comma can't be used in expressions in queries, controlsources or expressions like this, same goes for Excel formulas - we use semicolon in stead.

So, try substituting the commas in Remou's suggestion with semicolon.

Roy-Vidar (no)

 

[rmork]

Thanks to both of you. The semicolon did the trick with cond. format but It is still not formating the way it should... I really had my hopes up now...
Any other ideas?

 

[Remou]

What did you get and what were you hoping for?

 

[rmork]

Yohooo...
I had to add a hidden Control in the sub form with [VesselID]. It works like a charm!!!.
Thank you guys. you're awsome...