Solved

conditional formatting, Access 2010

Posted on 2014-03-12
5
416 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 34

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 33

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 33

Author Comment

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

Thanks

Mike
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 39929624
Beautiful.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now