Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

conditional formatting, Access 2010

Posted on 2014-03-12
5
Medium Priority
?
437 Views
Last Modified: 2014-03-14
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.
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 39

Expert Comment

by:PatHartman
ID: 39924221
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.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39924583
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
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 2000 total points
ID: 39924841
Hi Mike

Try this function:
Public Function ToggleSection( _
    strSource As String, _
    strField As String, _
    varValue As Variant _
  ) As Boolean
Dim rs As DAO.Recordset
Dim strSQL As String
On Error GoTo ProcErr
  strSQL = "SELECT Count(*) FROM (SELECT DISTINCT [" & strField _
      & "] FROM [" & strSource & "] WHERE [" & strField & "]<" _
      & varValue & ");"
  Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
  ToggleSection = (rs(0) Mod 2) = 0
ProcErr:
  If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
  End If
End Function

Open in new window


You can then use it for conditional formatting:

Expression Is:   ToggleSection([RecordSource],"CustID",[CustID])

Unfortunately you cannot put conditional formatting on a form section, but you can give the appearance by using a textbox in the background with CF.  Create a textbox named txtBackground and use Position > Sent to Back to ensure it is behind everything else.  Set Locked=Yes and Enabled=No.  Put CF on it, using the function as described, then set its Top and Left to 0 and Height and Width to 0.1 (this is to stop it from being annoyingly selected when you are dealing with other controls on your form).  In the Form_Load event procedure, resize it like this:
  With txtBackground
    .Left = 0
    .Top = 0
    .Width = Me.Width
    .Height = Detail.Height
  End With

Open in new window


Good luck!

Graham Mandeno [Access MVP 1996-2014]
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39924919
It looks pretty good. I will try it soon.

Thanks

Mike
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 39929624
Beautiful.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question