Highlighting Datasheet Rows in Subform

[Breezwell]

I have done some searching on this one and I have yet to find any information that sheds light on what I am trying to do. At lease from what I can understand.

Basically, I have a main form which has a single subform. The subform presents a datasheet view of a a query. The query results present information in such a manner that a single column, call it GroupCode, can have numerous duplicate entries. This is actually desired as a normalized database is not what I need. So, I could have entries in the GroupCode column like the following:

GroupCode
*************
96445
96445
96445
87343
87343
87787
87787


What I would like to do is highlight each set of rows with the same GroupCode in a similar background color. In other words, I would like all records with GroupCode 96445 to be highlighted a certain color. I would like to skip highlighting on 87343 GroupCodes, then apply higlighting again to 87787 GroupCodes. Basically, alternate highlighting such as is possible in Excel.

Is this even possible in Access? If so, are ADO Recordsets where I should be looking?

Thanks for any guidance on this issue.

[Stewart Ross]

Hi. It is not possible to highlight alternate rows. It is possible to highlight duplicates using conditional formatting, but it would be very slow in operation. Reason for the slowness is that you would need to include a count of the number of rows matching your group code number, using the DCount function to do so (any other approach would prevent your table data from being updatable), and DCount coupled with conditional formatting would slow your form down considerably.

To highlight the rows concerned in a different background colour you would place a rectangle on the form as a background for your controls, and set its background colour property using conditional formatting whenever the control containing your DCount had a value > 1. The control with the count does not have to be visible; it can be hidden so as not to interfere with the existing content of your rows.

There is a short HowTo article by our highly-experienced site administrator NeoPa which provides a much fuller background on why the alternating formatting is not possible. The article is called Why Values in Unbound Form Controls Don't Persist - and it applies just as much to the formatting of continuous form backgrounds as to unbound controls.

I attach an example of the use of conditional formatting in one of my forms (a student payment system, with red background used to indicate withdrawn students).

-Stewart

[Breezwell]

Stewart,

Thank you for the detailed reply.

The process of highlighting the records according to the GroupCode really is just to provide some 'visual breakout' of the records in datasheet view for quick inspection. The color method is not mandatory, but I figured it would be the easiest.

Do you happen to know if there is simply a way to add a blank record between each group of records with the same GroupCode in the datasheet view only and not in my actual table?

I am thinking I could export my data to Excel, run a Subtotal against the data, remove the data in the Totals row, then export the file back into Access, utilizing the Totals row as a sort of divider in the Access datasheet view. This seems like an ugly hack that will create an ugly master table,

Thanks

[Stewart Ross]

Hi Breezwell. In databases there is no way that you can add blank rows in the way you describe; a database table or query has no inherent concept of record position, so any attempt to present data that relies on implicit row sequencing - record, blank, record, blank for example - cannot be achieved using normal database techniques. Excel is strongly influencing you here!

I should mention that it is possible to do things in Access reports - hiding duplicate values, programming detail sections to alternate shading - which cannot be done in tables, queries or forms. However, alternate row shading in report detail sections is similar in approach to the conditional formatting of forms, except that it is the detail section itself which is shaded alternately instead of a background box overlaid onto the data, and it is done from VBA code within the report.

-Stewart

[Megalog]

Which version of Access are you using? Alternate row shading is a built-in feature now in Access 2007. I use it along with groupings in reports all the time.

[Breezwell]

Stewart,

I really appreciate the insight. You replies kept me from going mad trying to figure out why I could not figure this out.

I am currently stuck with Access 2002. There is no way for me to upgrade on my work machine unfortunately. We are just now rolling out Access 2003.

I did manage to accomplish, albeit hideously, what I needed to do. Just for laughs, here is what I ended up doing. I will put these in steps since it seems so comical to me.

1. I exported my table into Excel.
2. I did a subtotal of all records and specified a summary for each change in GroupCode using the Count Num function on the GroupCode column. This put a 0 in the summary section for all GroupCodes for thier given grouping, providing me with the breakout visual I needed.
3. I then put some dummy text 'foo' in a blank cell (D3).
4. I then did an Auto Filter and selected all the cells with the 0.
5. I then put the following code in the first cell with the deleted 0: =IF($D$3 = "Foo", $D6 & " //////// End //////////")
6. I copied this if statement to all cells that had a 0 and this updated each grouping.
6. I then exported the Excel spreadsheet back into Access with the correct table name.

So, whenever I now do a search based on the queries I have set up, all GroupCodes are listed and at the end of every group there is an entry such as:

98875
98875
98875
98875 //////////////////// End //////////////////////////
8776
8776
8776 /////////////////// End ///////////////////////////

in my datasheet view.


Like I said, it us ugly and crude, but it does what my users need. I am almost embarrased to show how I did this, but my users think it is amazing that they can look at so much data and be able eyeball the groupings. I guess that is what really matters.

Thanks again for your help and I hope to contribute some 'professional' level stuff one day myself. :)