asked on 3/12/2014

conditional formatting, Access 2010

In a subform, there are varying number of records for CustID:

CustID ContactName Address etc.
------- ---------------- --------- ------
1 a
1 b
2 h
3 k
3 l
3 m
4 n

I want to apply alternating shade to the entire row from one CustID to the next. This way, the user can easily locate the information because the subform has many columns and fills the screen lengthwise.

Question1: How can I do this? Is there a built-in feature in the property sheet I can apply the required setting?

I supose, we can have fnRecordShade([CustID]) returing True or false when CustID changes if no other easier solution. If question 1 not possible then how should this function to look like:

Function fnRecordShade(lngCustID) as boolean
' the body of function to return true or false
' when CustID canges

fnRecordShade = True/False

End function

Thank you.

Microsoft Access

Last Comment

Mike Eghtebas

3/14/2014

PatHartman

3/12/2014

This wouldn't be too difficult to do with a report but I don't have any good ideas for a form. There are obviously too many customers to assign a color to each customer. The reason that it is easy enough in a form is because forms are essentially sequential processes. You can set a background color and until the ClientID changes, keep using the same color. Forms may look like reports but since they are a live representation of data, their behind the scenes structure is different and you don't have the necessary events to tie everything together. Not to mention the fact that you can right-click in any column and sort on it which would cause everything to have to be recolorized.

I think the best solution is to use a second subform for the contacts. When you click on a customer in subformA to set focus to it, subformB will show the contacts for that company.

You can do this with the master/child links but you need to include the subform name in the reference. So the Master link would be sfrmA.CustID and the Child would be CustID.

Mike Eghtebas

3/12/2014

ASKER

Hi Pat,

re:> You can set a background color and until the ClientID changes, keep using the same color.
Exactly.

Unfortunately, having another subform is not an acceptable option for my client. But:

If we include the following alias field in the recordsource query or better yet call from expression is box in conditional format interface:

ShadeCust: fnRecordShade(lngCustID)

and...

Public lngCost_ID as long '<-- all in a module
Public bolYN as long

Function fnRecordShade(lngCustID) as boolean

' strFilter is the where string the data is filtered accordingly
' strOrder is set fields Asc or Desc
Dim rs dao.recordset

set rs=currentdb.openrecordset ("Select * From tCustomer Where " & strFilter & " Order By " & strOrder)

' here we are making sure it has at least on record and only CustID is being sorted
if rs.recordcount> 0 and instr(strOrder, "CustID")>0 then
rs.movefirst
lngCost_ID=rs!CustID
bolYN = True 'first cust to be shaded
do until rs.eof
if lngCost_ID<>rs!CustID then
bolYN = not(bolYN )
lngCost_ID=rs!CustID
end if
rs.movenext
loop
fnRecordShade = bolYN
End function

But of course I have to try and possibly debug.

Mike

ASKER CERTIFIED SOLUTION