Hi Mary,
Interesting challange! I think I have a method that will work for you. If I
understand you correctly, you are grouping on due date. Try the following in
the Northwind sample database (Northwind.mdb):
1.) Create a new table with the following fields:
pkColorID Number / Long Integer (Primary Key)
ColorDesc Text
Color Number / Long Integer
Remove the default 0 from the numeric fields. Save this table as tblColors.
2.) Add the following records to your new tblColors table:
pkColorID ColorDesc Color
1 Red 255
2 Blue 16711680
3 Black 0
4 Green 32768
5 Purple 8388736
6 Pink 16711935
7 Aqua 8421440
8 Gray 12632256
9 Turquoise 16777088
10 Burgendy 4194432
3.) Create a new query. Dismiss the Show Table dialog box. In query design,
click on View > SQL View. Copy the following SQL (structured query language)
statement and paste it into the SQL window. Save the query as qry10OrderDates.
SELECT Customers.CompanyName, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)
In (SELECT TOP 10 Orders.OrderDate
FROM Orders GROUP BY Orders.OrderDate
ORDER BY Orders.OrderDate
));
The whole point of this query is to simply return orders associated with the
first 10 order dates.
4.) Create a new report based on qry10OrderDates.
Click on View > Sorting and Grouping in report design. Select OrderDate
(Ascending) with group header = Yes. Select CompanyName (Ascending).
Close the sorting and grouping dialog.
Click on View > Field List
Drag the OrderDate field from the Field List, and drop it into the OrderDate
Header section. Drag the CompanyName field and drop it into the Detail
section.
Click on View > Properties
Click on the Other tab. Now select the OrderDate Header. If necessary,
change the name shown on the Other tab to GroupHeader1. (Note: you can use a
different name if you want, but you'll need to know what it is later).
Rename the OrderDate textbox to txtOrderDate
Rename the CompanyName textbox to txtCompanyName
Add a new textbox to the OrderDate header, using the toolbox (View >
Toolbox). Name this new textbox txtColorCode.
Select the Data tab of the Properties view.
Set the Control Source to: =1
Set Running Sum = Over All
If desired, set the visible property for txtColorCode to No. This property
can be found on the Format tab of the properties dialog, with this textbox
selected.
5.) Now click on View > Code in report design view. Copy and paste the
following code into your report's code module:
Option Compare Database
Option Explicit
Private Sub GroupHeader1_Format(Cancel As Integer, _
FormatCount As Integer)
On Error GoTo ProcError
Dim lngColorCode As Long
lngColorCode = Nz(DLookup("Color", "tblColors", "pkColorID = " _
& Me.txtColorCode), 0)
Me.txtOrderDate.ForeColor = lngColorCode
Me.txtCompanyName.ForeColor = lngColorCode
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure GroupHeader1_Format..."
Resume ExitProc
End Sub
6.) Click on Debug > Compile ProjectName in the Visual Basic Editor (VBE).
Hopefully, your new code module will compile properly without any errors.
7.) Close the VBE. Save the report. Try running it to see if you get the
desired results.
You can now create a form, which allows your user to assign various colors.
The Nz function that I used converts any nulls to whatever default value you
want. I used 0 (zero), which corresponds to black font.
I hope this has been helpful.
Happy <colorful> New Year!
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
:
My report contains records with due dates. There can be up to ten due dates
and each due date may have one to ten records. Due dates in the report are in
ascending order. I want to color code the records in each due date group
where all the records with the earliest due date will be one color, all the
records with the next due date will be a second color, all the records with
the next due date will be a third color, etc. I would like the user to be
able to select each of the colors and be able to specify the order of the
colors. I need recommendations on how to set this up.
Thank you!
