Create a Subform with Alternating Color Rows

by ZapDudeSoftware

Requirement: Access 2000 or above
Knowledge: Beginner to Intermediate

When you create an Access Report with a subreport control, you can use VBA to format the Detail section to create alternating colored rows. Unfortunately, subforms do not share the same versatility as subreports. However, you can create rows of alternating colors by adding a field to the query providing for the underlying source object for the subform, and using this field to create an expression to provide the Conditional Formatting function for subform controls. Added bonus: No VBA is necessary! All formatting calculations are provided at design time.

Here is a subform with data sorted by salary in descending order. Alternating rows of data are formatted with different back colors, providing a pleasing alternative to the plain background or lines that separate data in other forms.

All the tables, forms, and queries are provided in the database attachment.

Here is the table that the form queries are based on:

SELECT
FirstName & " " & LastName AS FullName,
Salary,
Position,
DateStart,

(SELECT Count(*)
FROM tblEmployee
WHERE tblEmployee.Salary > tblEmployee_1.Salary
OR
(tblEmployee.Salary = tblEmployee_1.Salary
AND
tblEmployee.EmployeeID >= tblEmployee_1.EmployeeID)
) AS RunningCount

FROM
tblEmployee AS tblEmployee_1

ORDER BY
Salary DESC,
EmployeeID;

Open a new query in SQL design view. Copy and paste the query into the SQL box. Notice the subquery that creates the RunningCount field. Since the entire query is sorted by salary, the subquery uses the Count function to create the number of records that contain salary amounts greater than or equivalent to the alias table value. The result is a field containg sequential amounts, or in effect, a counting field.

Let us analyze the query. First of all, note there are two sort fields. The main sort field is Salary, and is sorted in descending order. The second is the "catch-all" sort field, which is the unique field EmployeeID field. This will allow the Running Count field to always have an incremental, non-repeating or non-skipping sequence.

Now, keep in mind, that any query that creates a Running Count field must be based on unique recordsets. Going back to the subquery, notice that there is an additional, or secondary, sort field references the EmployeeID field, which has no duplicates. The reason you MUST include a field (or unique field combination) set in a Running Count query is that duplicate query results can alter the Running Count field. In this case, if we had two employees with duplicate salaries, and there was no secondary sort, then the Running Count would include duplicate Running Count values for all records which share the same salary amount.

Having said that, in the example given all Salary value amounts are different, so the secondary sort option is not triggered, and the Running Count remains consistant throughout the recordset.

Notice how the records are sorted by salary in descending order, with the RunningCount field appropriately numbered. This is the field on which we will create our Conditional formatting formula trigger.

Once you have confirmed the query to look like the query created above, save it and name it qrySalaryDesc1.

For the trigger, we use a simple method of determining numbers by their divisibility by 2. If an even number is divided by 2, it must be a whole number. For instance, 4/2 = 2, 8/2 = 4, etc. Conversely, if an odd number is divided by 2, it cannot be a whole number. For instance, 3/2 = 1.5, 7/2 = 3.5, etc. Based with this knowledge, we can create an algorithym where we take the integral factor of a divided calculation and compare it with the calculation itself. If the integral factor of the divided calculation matches the actual divided calculation, we know the number is even. If the integral factor of the divided calculation does NOT match the divided calculation, then the number is odd.

Here is the formula:

If x / 2 = Int (x / 2) then the number is even.
If x / 2 <> Int (x / 2) then the number is odd.

Microsoft Access allows the backslash to be used in place of the Int function to provide the same result. Therefore ,

If x / 2 = x \ 2 then the number is even.
If x / 2 <> x \ 2 then the number is odd.

Armed with this formula, we can now create the subform with the alternating formatting colors. You can open up the subform ("fsubSalaryDescColor") to see the finished form. If you don't wish to create the new form, you can open this form in design view and look at the properties as they will be described below.

First, open up a new form in Design view. Set the following form properties as follows:

Format Tab:
Default View: Continuous Forms
Scroll Bars: Vertical Only
Record Selectors: No
Navigation Buttons: No

Data Tab:
Record Source: qrySalaryDesc
Allow Filters: No
Allow Edits: No
Allow Additions: No
Allow Deletions: No

Next, paste 5 text boxes onto the form , and delete the labels attached to the text boxes.

Next, on the first text box enter the following properties:

Format Tab:
Left: 0.0007"
Top: 0.0007"
Width: 0.0007"
Height: 0.0007"

(Note: 0.0007 is equivalent to 1 pixel, and is impossible for a user to see this text box. However, we aren't going to use this text box except to house the current RunningCount value. Due to a glitch involved with the Conditional Formatting function, this text box must remain visible and enabled, hence the size adjustment.)

Data Tab:
Control Source: RunningCount

Save and name the text box control txtRunningCount.

On the second text box, enter the following properties:

Format Tab:
Left: 0"
Top: 0"
Width: 1.25"
Height: 0.1667"
Back Style: Normal
Special Effect: Flat
Border Style: Transparent

Data Tab:
Control Source: FullName
Enabled: No
Locked: Yes

Save and name the text box control txtName

On the third text box, enter the following properties:

Format Tab:
Left: 1.25 "
Top: 0"
Width: 0.9583 "
Height: 0.1667"
Back Style: Normal
Special Effect: Flat
Border Style: Transparent

Data Tab:
Control Source: Position
Enabled: No
Locked: Yes

Save and name the text box control txtPosition

On the fourth text box, enter the following properties:

Format Tab:
Left: 2.2083"
Top: 0"
Width: 1.125"
Height: 0.1667"
Back Style: Normal
Special Effect: Flat
Border Style: Transparent

Data Tab:
Control Source: Salary
Enabled: No
Locked: Yes

Save and name the text box control txtSalary

On the fifth and last text box, enter the following properties:

Format Tab:
Left: 3.3333 "
Top: 0"
Width: 1.2917"
Height: 0.1667"
Back Style: Normal
Special Effect: Flat
Border Style: Transparent

Data Tab:
Control Source: DateStart
Enabled: No
Locked: Yes

Save and name the text box control txtDateStart

Once you have formatted the five text boxes, move the form edges to match the bottom and right side of the text boxes. Your completed form design should look like this:

Save and name the form fsubSalaryDesc1. Use the suffix "1" to differentiate from the finished example already in the database.

If you notice, the entire area of the form is covered by four text boxes. This means that the background color for any particular record will be the same color as the background of the four text boxes. With the border property set to Transparent, the display background of the entire record appears seamless.

Open fsubSalaryDesc1. The following window will appear:

You can see the continuous form displays the records in the same order as the query. What you don't see is the Running Count text box which contains the trigger for the alternate color format. Let's create the conditional formatting necessary to show alternate colors for the new form.

First, open fsubSalaryDesc1 in design view. Select the first visible textbox, txtName.

Next, click the menu item Format, then Conditional Formatting. The Conditional Formatting window will open. Perform the following steps:

1. Click "Add > >" at the bottom of the window. Two conditions will appear.

2. Under Condition 1, change the combo box from "Field Value Is" to "Expression Is." The text boxes to the right of the combo box will change to a single text box.

3. Enter the following statement into the textbox:

txtRunningCount.Value/2=txtRunningCount.Value\2

4. Select the background color icon (the one with the spilling paint bucket). Click an appropriate background color. In the example, I used light lime green.

5. Under Condition 2, change the combo box from "Field Value Is" to "Expression Is." The text boxes to the right of the combo box will change to a single text box.

6. Enter the following statement into the textbox:

txtRunningCount.Value/2<>txtRunningCount.Value\2

7. For the background color, select a different color from Condition 1. In the example, I used white.

The window should look as follows:

This shows that all records with an odd numbered Running Count field will show a white background, while all records with an even numbered Running Count field will show a green background.

Click "OK" to close out the Conditional Formatting.

Finally, repeat the conditional formatting for the three remaining visible text boxes.

Once you have formatted the text boxes, click "Save As" and save as fsubSalaryDescColor1. Open the form in runtime view. It should look like this:

Now that you have seen the finished subform, close this form and create the main form. Open a new form in design view. Create a subform control. In the subform label, enter "Alternate Color Formatted." For the source object, select fsubSalaryDescColor1. Size the control to fit the subform formatting as you like. Once this is finished, activate the form.

You now have created a subform with alternating color. Congratulations!

In the attached example, two other queries were created. One query sorted on the Start date for each employee in ascending (oldest first) order. The underlying query for that sort, qryDateStartAsc, is similar in complexity to the salary sort query, as it uses only one field to set the Running Count data field (in addition to using the EmployeeID field as the "catch-all" field).

Finally, this query sorts data by the Last Name, with a secondary sort on the First Name. The query is as follows:

SELECT
[FirstName] & " " & [LastName] AS FullName,
Salary,
Position,
DateStart,

(SELECT Count(*)
FROM tblEmployee
WHERE
tblEmployee.LastName < tblEmployee_1.LastName
OR
(tblEmployee.LastName = tblEmployee_1.LastName
AND
tblEmployee.FirstName <tblEmployee_1.FirstName)
OR
(tblEmployee.LastName = tblEmployee_1.LastName
AND
tblEmployee.FirstName = tblEmployee_1.FirstName
AND
tblEmployee.EmployeeID <= tblEmployee_1.EmployeeID)
) AS RunningCount

FROM
tblEmployee AS tblEmployee_1

ORDER BY
LastName,
FirstName,
EmployeeID;

Note that the subquery has three conditions under the WHERE clause. The first condition will sort the query by last name. When the last name of the alias matches that of the main table, the second condition checks the FirstName field and subsorts by that value. Finally, in the event that two individuals share the same first and last names, the third condition sorts by the EmployeeID field as the "catchall" field sort to guarantee that the RunningCount field will provide a non-repeating, non-skipping sequence. At the end of the main query, notice the ORDER BY clause corresponds with the LastName as the main sort field, with the FirstName field as the secondary sort field, while the EmployeeID field is the last to be sorted.

Anyway, there you have it. You can see the three examples of the different sorts on frmMain. In addition, you should disect the queries to make sure you can emulate similar functionality in your projects.

There are some "glitches" that need mentioning. Here are a couple of them:

1. Even though the property settings set the text box controls for Enabled to False, and Locked to True, which should normally disable the controls from being accessed with the mouse and the text from being highlighted, the Conditional Formatting seems to negate the property settings. While the selected data cannot be altered or deleted, this behavior is a definite implication of the Conditional Formatting function, and there doesn't appear to be a quick fix.Currently, I'm working through the API editor to see if I can arrange a more permanent solution.

2. For some inexplicable reason, on rare occasions records seem to be missing from the subform. I haven't run across it in the past couple of days, but I'm still trying to find out why it happens. I can't replicate the problem, so I'll keep looking on my end.

Copyright ©2005 Michael Resann ZapDude Software, Long Beach, CA