Microsoft Access Office VB VBA Help and Examples
 HOME     MS ACCESS     MS OUTLOOK     MS EXCEL     VB and VBA     FREE PROGRAMS     iPAQ     PERSONAL 
 ACCESS     GENERAL     QUERIES     FORMS     REPORTS     MODULES     RANTS     TUTORIALS 

Color Alternate Lines in an Access Report

This sample shows how to color records in a report according to a field in the report or just alternately, without reference to fields in the report.

You can apply this in your own report or download the example database and use that for working through this example. If you use your own fields then remember to replace field names accordingly.

Example database hams.mdb or zipped hams.zip

Create a new report using the report wizard and base it on the FullDetails query, choosing all fields and accepting all the wiard defaults.

Once the wizard has finished go into design mode for the report and add a new unbound text box in the detail section. Right click on the box and choose properties. On the format tab set the visible property to 'No'. On the Data tab set control source to =1 and the running sum property to 'Over All'. On the Other Tab set the name property to RunSum and close the property box.

We have just created a running sum text box. This will basically be a counter in the detail section of our report, starting on 1 and incrementing for each detail. We can use this calculated control to set the formatting of our report. If you don't believe me then make the box visible and run the report.

From the menu choose View -> Code

This is the code window now type the following in.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If [RunSum] Mod 2 = 1 Then
      Me.Section(0).BackColor = vbRed
   Else
      Me.Section(0).BackColor = vbWhite
   End If
End Sub

What this piece of code does is every time the detail section is being formatted it checks the value of the runsum box. If it is not divisible by 2 then the background colour of the report is set to red. If it is divisible by 2 it is set to white. Note we have to supply a response for both cases otherwise after the first setting of the background colour to red the report will stay red for all future values. Close the code window and run the report. The alternate records should be coloured red and white.

Alternatively you could change the code to color a particular field in the report to make it stand out based on some criteria

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.