469,645 Members | 1,971 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

Highlighting Datasheet Rows in Subform

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:


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.
Sep 24 '08 #1
5 8976
Stewart Ross
2,545 Expert Mod 2GB
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).

Attached Images
File Type: jpg ScreenHunter_67.jpg (15.1 KB, 769 views)
Sep 24 '08 #2

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,

Sep 24 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
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.

Sep 24 '08 #4
378 Expert 256MB
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.
Sep 24 '08 #5

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 //////////////////// End //////////////////////////
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. :)
Sep 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by s_wadhwa | last post: by
3 posts views Thread by Danny J. Lesandrini | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.