Conditional formatting based on another value

[Robert C]

Hi

I have searched the forum but can't quite find what I am looking for.

I have a continuous form and want to change the background colour of the fields for each record based on the settings derived from an option group on another form.

Any help would be gratefully recieved.

Cheers

Rob

 

[WayneRyan]

Rob,

I'd suggest that you make a Public Function which will return True/False.

Pass it whatever of your form's field values that are required along with
the other form's Option Group.

Then use the function call in your Conditional Formatting ...

"Expression Is" --> fnGetHighlight([Field1], [Field2])

Then select the colors for the True/False conditions.

Public Function fnGetHighlight(strField1 As String, lngField2 As Long) As Boolean
'
Select Case Forms!ThatOtherForm!OptionGroup.Value
   Case 0
       If strField1 = "SomeThing" And lngField2 > 1000 Then
           fnGetHighlight = True
       Else
           fnGetHighlight = False
       End If
       Exit Function
   Case 1
       ...

End Function

hth,
Wayne

 

[Robert C]

Wow Wayne

Thanks, that looks great! Trouble is my knowledge of VBA is not too great.

Could you please explain exactly where I put the public function - for example, which event on the form requiring the formatting does this piece of code reside in? Or am I missing something more basic?

If it is only looking for a numeric value from the option group (in this case 1, 2, 3 or 4) why does it refer to a string (sorry to be dense).

The form that requires the formating is frmProjectList which features fields like ClientName, ProjectName, etc. and the form that has the Option Group is frmProjects and the Option group is called FrameProjectStatus and has a control source of Project Status

Sorry that I really need an idiot's guide.

Cheers

Rob

 

[WayneRyan]

Rob,

The function goes in a Module.

On your Database Window you have tabs for Tables, Queries, Forms, Reports, Macros
and Modules.

Make a new module and put the function in it.

Your form is fed by a table or query. Obviously the table or query does not
have sufficient information to determine the Conditional Formatting.

Any field that you want to conditionally format should call your function.
The function is smart enough to take values from YOUR form ... and then look
for the Other form for its values.

I don't know the nature of your logic, but if you can explain more, or post a sample
it would help.

Wayne

 

[boblarson]

...Trouble is my knowledge of VBA is not too great.
...
Sorry that I really need an idiot's guide.

I haven't seen this one, but the few reviews seem okay.
http://www.amazon.com/Access-Progra...6356656?ie=UTF8&s=books&qid=1178653500&sr=1-4