Mike Eghtebas
asked on
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.
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.
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
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
' 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It looks pretty good. I will try it soon.
Thanks
Mike
Thanks
Mike
ASKER
Beautiful.
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.