Changing Colors of fields on a report - Microsoft Access / VBA

bill mahoney

I have access 2k and I have 2 tables. One is a 7X6 table representing
a month with zeroes padding the fields where no day exist. For example
February 2003

0 0 0 0 0 0 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 0
0 0 0 0 0 0 0

Denotes Sunday thru Saturday.

Then I have a separate table that breaks down the month with fields
representing Start Date, End Date, Pay Date, Due Date, and Holiday as
follows

MthNum Month DateNum Year DayNum Day SDate Edate PayDate
Duedate Hol
2 February 1 2003 7 Saturday N N N N Y
N

What I am looking to do is on Dates marked with a "Y" i want to color
and bold--so for start dates I want red and bold, end dates blue and
bold, holidays underlined and so forth. The report is the first table
and I want the number to be formated as stated. Being that the columns
are shown in the detail section of the report and not the individual
fields is ther a way to selectively choose what gets colored and what
doesn't. I have the below code in my detail section

------Start Code----------
Private Sub Detail_Format(C ancel As Integer, FormatCount As Integer)
Dim mOn As DAO.Recordset
Dim mOn2 As DAO.Recordset
Dim bOO As Boolean
Dim dNum(10) As Integer
Dim dNum2(10) As Integer
Dim dNum3(10) As Integer
Dim dNum4(10) As Integer
Dim dNum5(10) As Integer
Dim dNum9(15) As Integer
Dim cOunt As Integer
Dim xxx As String
Dim lngRed As Long, lngYellow As Long, lngWhite As Long
Dim lngBlue As Long, lngGreen As Long, lngMagneta As Long

lngRed = RGB(255, 0, 0)
lngblack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
lngGreen = RGB(0, 150, 0)
lngBlue = RGB(0, 0, 255)
lngMagneta = RGB(180, 140, 65)

Set mOn = CurrentDb.OpenR ecordset("Febru ary")
bOO = False
For i = 1 To 7
If Me("day" & i) = 0 Then
Me("day" & i).Visible = False
Else
Me("day" & i).Visible = True
End If
Next i

'then do 8 minus count and fill in with zeroes the do a large if
statement
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields (6)) = "Y" Then
dNum(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields (7)) = "Y" Then
dNum2(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum2(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields (8)) = "Y" Then
dNum3(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum3(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields (9)) = "Y" Then
dNum4(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum4(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields (10)) = "Y" Then
dNum5(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum5(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields (6)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields (7)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields (8)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields (9)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields (10)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 14
dNum9(cOunt) = 99
Next cOunt

For i = 1 To 7
If (Me("day" & i) = dNum(0) Or Me("day" & i) = dNum(1) Or Me("day"
& i) = dNum(2) Or Me("day" & i) = dNum(3) Or Me("day" & i) = dNum(4)
Or Me("day" & i) = dNum(5) Or Me("day" & i) = dNum(6) Or Me("day" & i)
= dNum(7) Or Me("day" & i) = dNum(8) Or Me("day" & i) = dNum(9)) Then
Me("day" & i).FontUnderlin e = True
'Me("day" & i).FontBold = False
'Me("day" & i).ForeColor = lngblack

ElseIf Me("day" & i) = dNum2(0) Or Me("day" & i) = dNum2(1) Or
Me("day" & i) = dNum2(2) Or Me("day" & i) = dNum2(3) Or Me("day" & i)
= dNum2(4) Or Me("day" & i) = dNum2(5) Or Me("day" & i) = dNum2(6) Or
Me("day" & i) = dNum2(7) Or Me("day" & i) = dNum2(8) Or Me("day" & i)
= dNum2(9) Then
Me("day" & i).ForeColor = lngRed
Me("day" & i).FontBold = True
Me("day" & i).FontUnderlin e = False

ElseIf Me("day" & i) = dNum3(0) Or Me("day" & i) = dNum3(1) Or
Me("day" & i) = dNum3(2) Or Me("day" & i) = dNum3(3) Or Me("day" & i)
= dNum3(4) Or Me("day" & i) = dNum3(5) Or Me("day" & i) = dNum3(6) Or
Me("day" & i) = dNum3(7) Or Me("day" & i) = dNum3(8) Or Me("day" & i)
= dNum3(9) Then
Me("day" & i).ForeColor = lngGreen
Me("day" & i).FontBold = True

ElseIf Me("day" & i) = dNum4(0) Or Me("day" & i) = dNum4(1) Or
Me("day" & i) = dNum4(2) Or Me("day" & i) = dNum4(3) Or Me("day" & i)
= dNum4(4) Or Me("day" & i) = dNum4(5) Or Me("day" & i) = dNum4(6) Or
Me("day" & i) = dNum4(7) Or Me("day" & i) = dNum4(8) Or Me("day" & i)
= dNum4(9) Then
Me("day" & i).ForeColor = lngBlue
Me("day" & i).FontBold = True

ElseIf Me("day" & i) = dNum5(0) Or Me("day" & i) = dNum5(1) Or
Me("day" & i) = dNum5(2) Or Me("day" & i) = dNum5(3) Or Me("day" & i)
= dNum5(4) Or Me("day" & i) = dNum5(5) Or Me("day" & i) = dNum5(6) Or
Me("day" & i) = dNum5(7) Or Me("day" & i) = dNum5(8) Or Me("day" & i)
= dNum5(9) Then
Me("day" & i).ForeColor = lngMagneta
Me("day" & i).FontBold = True

'cc
ElseIf Me("day" & i) <> dNum9(0) And Me("day" & i) <> dNum9(1) And
Me("day" & i) <> dNum9(2) And Me("day" & i) <> dNum9(3) And Me("day" &
i) <> dNum9(4) And Me("day" & i) <> dNum9(5) And Me("day" & i) <>
dNum9(6) And Me("day" & i) <> dNum9(7) And Me("day" & i) <> dNum9(8)
And Me("day" & i) <> dNum9(9) And Me("day" & i) <> dNum9(10) And
Me("day" & i) <> dNum9(11) And Me("day" & i) <> dNum9(12) And Me("day"
& i) <> dNum9(13) And Me("day" & i) <> dNum9(14) Then
Me("day" & i).FontUnderlin e = False
Me("day" & i).FontBold = False
Me("day" & i).ForeColor = lngblack

'ElseIf Me("day" & i) <> dNum2(0) And Me("day" & i) <> dNum2(1)
And Me("day" & i) <> dNum2(2) And Me("day" & i) <> dNum2(3) And
Me("day" & i) <> dNum2(4) And Me("day" & i) <> dNum2(5) And Me("day" &
i) <> dNum2(6) And Me("day" & i) <> dNum2(7) And Me("day" & i) <>
dNum2(8) And Me("day" & i) <> dNum2(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderlin e = False

'ElseIf Me("day" & i) <> dNum3(0) And Me("day" & i) <> dNum3(1)
And Me("day" & i) <> dNum3(2) And Me("day" & i) <> dNum3(3) And
Me("day" & i) <> dNum3(4) And Me("day" & i) <> dNum3(5) And Me("day" &
i) <> dNum3(6) And Me("day" & i) <> dNum3(7) And Me("day" & i) <>
dNum3(8) And Me("day" & i) <> dNum3(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderlin e = False

'ElseIf Me("day" & i) <> dNum4(0) And Me("day" & i) <> dNum4(1)
And Me("day" & i) <> dNum4(2) And Me("day" & i) <> dNum4(3) And
Me("day" & i) <> dNum4(4) And Me("day" & i) <> dNum4(5) And Me("day" &
i) <> dNum4(6) And Me("day" & i) <> dNum4(7) And Me("day" & i) <>
dNum4(8) And Me("day" & i) <> dNum4(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderlin e = False

'ElseIf Me("day" & i) <> dNum5(0) And Me("day" & i) <> dNum5(1)
And Me("day" & i) <> dNum5(2) And Me("day" & i) <> dNum5(3) And
Me("day" & i) <> dNum5(4) And Me("day" & i) <> dNum5(5) And Me("day" &
i) <> dNum5(6) And Me("day" & i) <> dNum5(7) And Me("day" & i) <>
dNum5(8) And Me("day" & i) <> dNum5(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderlin e = False

End If
Next i
mOn.Close: Set mOn = Nothing
End Sub
-------End Code-------

It seems to work OK for the most part but there seems to be a conflict
in that the 9th and 23rd in column 1 of my first table are sart dates
and should be red abd the 16th is a holiday and should be underlined
and black--but what happens is that it is red and underlined. Is there
a way to specifically choose a row and column number in the report to
access a particular field. Thanks for any help and sorry about my
spaghetti code--i am just learning.

Regards,
Bill Mahoney
The Alcott Group

Nov 12 '05 #1

Subscribe Reply

1532

Similar topics

19152

Crystal Report unbound fields

by: Stephan | last post by:

Hi, I'm using Visual Studio 2003 (C#) with the integrated Crystal Report software and have the following question: How can I assign a value (string) to an unbound (string) field in Crystal Report at runtime? Example: private void button1_Click(object sender,

.NET Framework

2103

Changing onkeypress behavior for field element

by: Rick Measham | last post by:

I can't find this anywhere, so I hope it can be done: Here's what I can do so far: 1. Certain fields have a class 'textfield' while others are 'textfieldreq'. 2. OnSubmit checks all elements and makes sure that those with 'req' have some value

Javascript

3697

Dynamic number of fields in an Access 2000 Report

by: CSDunn | last post by:

Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what a spreadsheet might show, the column names will actually be dynamic, based on data from a SQL Server 2000 database. The row data will also come...

Microsoft Access / VBA

1175

changing colors of numeric fields

by: jhickers | last post by:

In MS 2003 or 2002 can anyone tell me the simplist way to change color of text in a form or report? I want the values of currency fields changed to red if the amount is >= to $1,000. I am not a programmer! Thanks

Microsoft Access / VBA

1149

Changing query for MS Graph

by: TD | last post by:

I have a report that has the MS Graph control on it. I have a query setup for the control. I have a form with 6 check boxes where the user can select which fields they want to appear in the query and thus appear in the report graph. I want to know the best approach to modify the query on the fly when the report is run, based on which...

Microsoft Access / VBA

4381

changing data source in Crystal Reports deletes all bound fields

by: John Dalberg | last post by:

When I change the data source for a report say like from SQL to a stored procedure or if the table fields have changed, all the bound fields on the report vanish. It's time consuming to recreate all these fields and set the format in each. Is there any way to keep the fields and tell CR the new mapping? To map each field from the old source...

ASP.NET

3727

changing crystal reports print date in code

by: tony | last post by:

Is there a way to change the "print date" field in vb/vb.net code? I have a report that uses keywords like "MonthToDate" and "YearToDate" to genereate the report, I want to be able to change the relative date to those fields. Basically exactly like the "set print date and time" dialog box under the reports menu in crystal reports designer. ...

Visual Basic .NET

3261

HELP on Changing Crystal Report sql query at run time via vs.net.

by: Phin | last post by:

I need your HELP! I've seen all the posts on using Crystal Reports within vs.net (vb.net) and changing a SQL query at runtime. When I tried to pass in a dataset into the crystal report at runtime, the report still showed the results from the default query (from within the Crystal Report). Then I tried the XSD solution where you define a...

Visual Basic .NET

1320

changing textboxes in a report at run time

by: tricard | last post by:

Hello all, I have a report that displays four pieces of data by default, but two of these fields are not neccessary for all people to see. I have made a form that gives the user the choice to select what data to display when the report is being run. After the selection has been made on the form, the report is opened in design mode, saved,...

Microsoft Access / VBA

7811

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

8314

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...

Online Marketing

8185

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...

General

5689

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...

Microsoft Access / VBA

3811

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...

Networking - Hardware / Configuration

3836

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2317

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1416

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1147

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

General