Conditional format detail

  • Thread starter Thread starter Jake F
  • Start date Start date
J

Jake F

I have a sub form that displays an employee and lists their supervisor. Is
there a way to have a line show up when the supervisor changes to split them
up a little bit and make it easier to read? Thanks.
 
The sub is in datasheet view so it looks like a table or spreadsheet. It
might list three supervisors and 15 different people they supervise. I want
to be able to put a line or color or do something to help distinguish when
the supervisor switches. In excel you can conditionally format when
supervisor doesn't equal the one listed above or in a report you can group on
supervisor and have a line on the group footer. I'm wondering if there is
anything in forms similar to that so visually the list is easier to read.
Hope that helps.
 
Jake,

Try using a form (Supervisor)/subform (Employee) which you can format to
*look like* a spreadsheet. However, you cannot do that in the format you
have now. Excel <> Access (on steriods). The tables are just to hold the
data and using the datasheet view limits how you can display your data.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Jake,

It seems to me that you have two options :-

1. You could replace the Sub-Form with a Flex Grid control which would
allow you to display the data in pretty much any format you like. You
could merge the Supervisor field and show each group of employees in a
different colour or whatever. The downside is that you would need to
install the Flex Grid control and write a fair bit of code to do this.
If you are interested in this option then have a look at the Flex Grid
Demo at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

2. You could use Conditional Formatting to change the colour of each
group of Supervisor records. However, since CF allows a maximum of
only three colours, the best you could do is change the back-colour of
each record when it changes to a different supervisor record. In other
words, the first group of supervisor records could show in yellow, the
next group in green, the next group in yellow, the next group in green
and so on.

If you want to try this you will need to change the display format for
the sub-form from Datasheet mode to Continuous mode (although you
could still use Datasheet mode if you want) and create a query which
returns all your records, sorted on the Supervisor field (or whatever
you called it).

Then create a new standard Module and paste the following code into it
(or you could use an existing Module if you have one).

'---------------------------------------------------------------------------
Public Function ColorSwitch(vName As String) As Boolean

Static vText As String, vFlag As Boolean

If vName <> vText Then
vText = vName
vFlag = Not vFlag
End If
ColorSwitch = vFlag

End Function
'---------------------------------------------------------------------------

and save the Module. It might be a good idea to Compile the code at
this stage to ensure that there are no duplicate function names, etc.

Now in the query add a new column and in the Field: box (at the top of
the column) enter this :-
ColorCode: ColorSwitch([Supervisor])

where Supervisor is the name of the field that holds the supervisor
name.

On the Continuous form, select the Supervisor field and choose
Conditional Formatting from the Format menu. Change the first field to
Expression Is and then enter this into the second field :-

[ColorCode]=0

Choose a back-ground colour for this condition.

Now add another condition with the Add>> button and enter the same
except that instead of 0 use -1 like this :-

[ColorCode]=-1

and choose a different back-ground colour for this condition.

If you want all your fields to show the same colours then just do the
same CF on those fields as well.

Now open the form and see what happens.

HTH

Peter Hibbs.
 
Back
Top