Change the fonts after comparing the records 1

[Tamrak]

Good morning,

Is there anyway we can change the fonts on the report when we see any different after comparing the previous record versus the current record?

I am uncertain whether the conditional format will be working.

Example, on my tblProject.

I would like the report to look like the following:

Project,ActivityDate,Score1,Score2,Score3

1234, 12/05/2006, 1.5, 2.5, 3.5
1234, 12/08/2006, 1.5, 2.7 , 3.5
1234, 12/10/2006, 1.7 , 2.7, 3.5
1800, 12/1/2006, 2.5, 3.5, 3.8
1800, 12/3/2006, 2.5, 3.7 , 3.4

1. After comparing the previous record, under the same project number , if there are any changes, the font will be changed to red italic.
2. If the project number changes, for example, from 1234 to 1800 (line # 3 and # 4), there will be no comparison.

I do appreciate all suggestions that you might have. Thanks.

 

[Remou]

A suggestion:

Option Compare Database
Dim lngProject As Long
Dim dblScore1 As Double
Dim dblScore2 As Double
Dim dblScore3 As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsEmpty(lngProject) Or lngProject <> [Project] Then
    Me.Score1.FontItalic = False
    Me.Score1.ForeColor = vbBlack
    Me.Score2.FontItalic = False
    Me.Score2.ForeColor = vbBlack
    Me.Score3.FontItalic = False
    Me.Score3.ForeColor = vbBlack
Else
    If dblScore1 <> [Score1] Then
        Me.Score1.FontItalic = True
        Me.Score1.ForeColor = vbRed
    Else
        Me.Score1.FontItalic = False
        Me.Score1.ForeColor = vbBlack
    End If
    
    If dblScore2 <> [Score2] Then
        Me.Score2.FontItalic = True
        Me.Score2.ForeColor = vbRed
    Else
        Me.Score2.FontItalic = False
        Me.Score2.ForeColor = vbBlack
    End If

    If dblScore3 <> [Score3] Then
        Me.Score3.FontItalic = True
        Me.Score3.ForeColor = vbRed
    Else
        Me.Score3.FontItalic = False
        Me.Score3.ForeColor = vbBlack
    End If
End If
    
lngProject = [Project]
dblScore1 = [Score1]
dblScore2 = [Score2]
dblScore3 = [Score3]

End Sub

 

[Tamrak]

Thank you so much for your input. I will try it and let you know.

By the way, I plan to insert the code in the detail band of the report. Do you recommend to be in the "on Format" or "on Print"? Please advise. Thanks.

 

[Remou]

Well ... I posted On Format ...

 

[Tamrak]

Hi Remou,

Thank you for the codes. It was running 90% a OK. I encountered some of the problems.

1. Some of the fields were originally blanked (null) and they had been filled with information on the later date. This needs to be captured as well.

2. When running the report, I received this error,

" Run-Time error '94':
Invalid use of Null"

Can you assist? We are almost there. Thank you for your time.

 

[Tamrak]

Hello,

Thank you. I am almost there.

Let's say that my data looks like the following:

1234, 12/05/2006, 1.5, 2.5, 3.5
1234, 12/08/2006, 1.5, , 3.5
1234, 12/10/2006, 1.7 , 2.7, 3.5
1800, 12/1/2006, 2.5, 3.5, 3.8
1800, 12/3/2006, , 3.7 , 3.4
1800, 12/6/2006, 2.7,3.7,

If the value changed from null to the number, the red italic fonts should be there as well. Can you assist? I still have the run time error '94' regarding invalid use of null.

Another thing is, if I add another text field and Dim that textfield as String, I should be able to continue using the same logic, am I correct?

For example:
Project,ActivityDate,Score1,Score2,Score3,changed_by


1234, 12/05/2006, 1.5, 2.5, 3.5, Paul
1234, 12/08/2006, 1.5, , 3.5, Paul
1234, 12/10/2006, 1.7 , 2.7, 3.5, Peter
1800, 12/1/2006, 2.5, 3.5, 3.8, Mary
1800, 12/3/2006, , 3.7 , 3.4, Meredith
1800, 12/6/2006, 2.7,3.7,Meredith

Thanks.

 

[Remou]

You can use Nz to avoid nulls:

[tt]If dblScore1 <> Nz([Score1], 0) Then
<...>
dblScore1 = Nz([Score1], 0)[/tt]

You should be able to apply the same logic to a text field.

 

[Tamrak]

Hi Ramou,

Thank you for your time. I believe we are 98% completed. There are couple of glitches

* Look like the fonts change to red italic when the project number changes. (it reads the previous record, instead of skipping it.)

* Some of the fields turn red italic on the First Record.

lngProject will never be emptied because it is a required field.

The data now looks like this:

1234, 12/05/2006, 1.5, 2.5 , 3.5 , Paul
1234, 12/08/2006, 1.5, , 3.5, Paul
1234, 12/10/2006, 1.7 , 2.7, 3.5, Peter
1800, 12/1/2006, 2.5 , 3.5 , 3.8 , Mary
1800, 12/3/2006, , 3.7 , 3.4, Meredith
1800, 12/6/2006, 2.7,3.7, 3.4,Meredith

Regarding the NZ function, I used the following on the text:

DIM txtChanged_by as String

If txtChanged_by <> NZ([Changed_by],"-") Then

Me.Changed_by.FontItalic = True
Me.Changed_by.ForeColor = vbRed
Else
Me.Changed_by.FontItalic = False
Me.Changed_by.ForeColor = vbBlack
End if


<...>

txtChanged_by = Nz([Changed_by],"-")

I do appreciate your suggestions that you may have. If you feel that something does not make sense, feel free to correct. Thanks and happy holidays.

 

[Remou]

Please post all the code, with any modifications you have made.

 

[Tamrak]

Hi Ramou,

I am uncertain whether I would do with comparing the blank text strings. So, I use the Nz function like a dash. If you have any other ideas, please feel free to edit.

The modified codes are as followed:

Option Compare Database
Dim lngProject As Long
Dim dblScore1 As Double
Dim dblScore2 As Double
Dim dblScore3 As Double
Dim txtChanged_by as String ' Changed_by field has 15 characters long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsEmpty(lngProject) Or lngProject <> [Project] Then
Me.Score1.FontItalic = False
Me.Score1.ForeColor = vbBlack
Me.Score2.FontItalic = False
Me.Score2.ForeColor = vbBlack
Me.Score3.FontItalic = False
Me.Score3.ForeColor = vbBlack
Me.Changed_by.FontItalic = False
Me.Changed_by.ForeColor = vbBlack
Else
If dblScore1 <> Nz([Score1],0) Then
Me.Score1.FontItalic = True
Me.Score1.ForeColor = vbRed
Else
Me.Score1.FontItalic = False
Me.Score1.ForeColor = vbBlack
End If

If dblScore2 <> Nz([Score2],0) Then
Me.Score2.FontItalic = True
Me.Score2.ForeColor = vbRed
Else
Me.Score2.FontItalic = False
Me.Score2.ForeColor = vbBlack
End If

If dblScore3 <> Nz([Score3],0) Then
Me.Score3.FontItalic = True
Me.Score3.ForeColor = vbRed
Else
Me.Score3.FontItalic = False
Me.Score3.ForeColor = vbBlack
End If

If txtChanged_by <> Nz([Changed_by],"-") Then
Me.Score3.FontItalic = True
Me.Score3.ForeColor = vbRed
Else
Me.Score3.FontItalic = False
Me.Score3.ForeColor = vbBlack
End If


End If

lngProject = [Project]
dblScore1 = Nz([Score1],0)
dblScore2 = Nz([Score2],0)
dblScore3 = Nz([Score3],0)
txtChanged_by = Nz([Changed_by],"-")

End Sub

I think we can eliminate ISEMPTY for the project # because it will never be a blank one.

** The first record has the fonts labeled with color. I am uncertain where it compared the record.

Thank you so much for your tenacity.

 

[Remou]

You will find that lngProject will be zero when the report is first open, because the variable has been initialised, however, you can omit Is Empty.
When copying the code for the new text field, you failed to change the name of the control from Score3 to Changed_by.

Option Compare Database
Dim lngProject As Long
Dim dblScore1 As Double
Dim dblScore2 As Double
Dim dblScore3 As Double
Dim txtChanged_by As String   ' Changed_by field has 15 characters long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If lngProject <> [Project] Then
    Me.Score1.FontItalic = False
    Me.Score1.ForeColor = vbBlack
    Me.Score2.FontItalic = False
    Me.Score2.ForeColor = vbBlack
    Me.Score3.FontItalic = False
    Me.Score3.ForeColor = vbBlack
    Me.Changed_by.FontItalic = False
    Me.Changed_by.ForeColor = vbBlack
Else
    If dblScore1 <> Nz([Score1], 0) Then
        Me.Score1.FontItalic = True
        Me.Score1.ForeColor = vbRed
    Else
        Me.Score1.FontItalic = False
        Me.Score1.ForeColor = vbBlack
    End If
    
    If dblScore2 <> Nz([Score2], 0) Then
        Me.Score2.FontItalic = True
        Me.Score2.ForeColor = vbRed
    Else
        Me.Score2.FontItalic = False
        Me.Score2.ForeColor = vbBlack
    End If

    If dblScore3 <> Nz([Score3], 0) Then
        Me.Score3.FontItalic = True
        Me.Score3.ForeColor = vbRed
    Else
        Me.Score3.FontItalic = False
        Me.Score3.ForeColor = vbBlack
    End If

    If Trim(txtChanged_by) <> Trim([Changed_by] & "") Then
        Me.Changed_by.FontItalic = True
        Me.Changed_by.ForeColor = vbRed
    Else
        Me.Changed_by.FontItalic = False
        Me.Changed_by.ForeColor = vbBlack
    End If


End If
    
lngProject = [Project]
dblScore1 = Nz([Score1], 0)
dblScore2 = Nz([Score2], 0)
dblScore3 = Nz([Score3], 0)
txtChanged_by = [Changed_by] & ""

End Sub

 

[TheAceMan1]

How are ya Tamrak . . .

Couldn't resist.

In the code module of the report copy/paste the following:

[blue]Public Sub fmtColor(ctl As Control, Color As String)

   If Color = "Red" Then
      ctl.FontItalic = True
      ctl.ForeColor = vbRed
   Else
       ctl.FontItalic = False
       ctl.ForeColor = vbBlack
   End If

End Sub[/blue]

Then change the format routine to:

[blue]   Dim Nam As String, x As Integer, oldVal
   Static oldPj, oldS1, oldS2, oldS3, oldCBy
   
   
   For x = 1 To 4
      Nam = Choose(x, "Score1", "Score2", "Score3", "Changed_By")
      oldVal = Choose(x, oldS1, oldS2, oldS3, oldCBy)
      
      If Me!Project <> oldPj Then
         Call fmtColor(Me(Nam), "Black")
      ElseIf Me(Nam) = oldVal Then
         Call fmtColor(Me(Nam), "Black")
      Else
         Call fmtColor(Me(Nam), "Red")
      End If
   Next
      
   oldPj = Nz(Me!Project)
   oldS1 = Nz(Me!Score1)
   oldS2 = Nz(Me!Score2)
   oldS3 = Nz(Me!Score3)
   oldCBy = Nz(Me!changed_by, "")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Tamrak]

Good morning,

Thank you so much for correcting the codes. Both of the codes are working excellently. I gave you all the stars and thanks again for providing and having expertise on this board. You all are valuable assets for the community.

I do apologize that I have not responding promptly. Thanks again.

 

[Tamrak]

Hello there again,

This is involving the same topic. I promise to be a short one.

I am mainly using Remou's codes. The records compare with each other nicely. I also replicated and expanded Remou's codes into a few more areas. It worked fine until I encountered two things.

First issue:

Sometimes, the table has no data and nothing to compare. (The actual application is on a daily basis. Sometimes, we have data to compare. Sometimes, the table is blank.)

When the codes are executed and the table is blank (no data), it generated this error,

"Run-time error '2427':
You entered an expression that has no value."

The debugging highlighted the following (in red):


....
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If lngProject <> [Project] Then
.....


Can you provide a simple IF / ENDIF codes, such as: If the project is blank or the table (tblProject) contains no data, skip the entire procedure, MsgBox = "There is no data in the current table." ?

I believe it should be written on the first line of the Sub Detail_format. If not, please kindly guide me toward the right direction.

*******************************************************

Second issue:

When there are more than two (2) records under the same project number. For example, I have 4 records under the same project number.

The code is comparing record # 1 and # 2, and highlights the fonts correctly. However, it does not compare # 2 and # 3 or # 3 and # 4.

I do not know what to do on this one and still stumpped.

****************

Anyhow, if you can assist on the first one first, I will be very much appreciated. I think the codes should be much of challenging for the knowledgable people like you all. Thanks again.

 

[Remou]

Have a look at the No Data event of the report.

 

[Tamrak]

Hello Remou,

Thanks again for the tips. I did a simple one by running the macros, instead. The VBA will be something like:

**********************

Msgbox "xxxxxxxxxxxxxxxxxxxxxxxxxx"
end ' stop all activities from continuing execution.
DoCmd.Close acReport, "rptProject"
***************

If you have other suggestions, please kindly post it. I use the end command because the report will continue to execute and I will receive an error again. If this seems to be incorrect, let me know.

********************

What do you think about the second issue? Sometimes, I end up more than 2 records with the same project number, around 15% of the time. The codes seems to compare #1 and # 2, but not # 2 and # 3 or # 3 and # 4, and so on...

Do you have suggestions? Thank you.