Solved

conditional formatting, Access 2010

Posted on 2014-03-12
5
424 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
  • 3
5 Comments
 
LVL 35

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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 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