Conditional formatting based on previous record detail

Why not just format the color of the CutSze textbox based on the Value?
This way there is no chance to error.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.txtCutSizeinMM = 1000 Then
        Me.txtCutSizeinMM.ForeColor = vbRed
    ElseIf Me.txtCutSizeinMM = 1100 Then
        Me.txtCutSizeinMM.ForeColor = vbBlue
    ElseIf Me.txtCutSizeinMM = 1200 Then
        Me.txtCutSizeinMM.ForeColor = vbMagenta
    ElseIf Me.txtCutSizeinMM = 1300 Then
        Me.txtCutSizeinMM.ForeColor = vbYellow
    ElseIf Me.txtCutSizeinMM = 1400 Then
        Me.txtCutSizeinMM.ForeColor = vbGreen
    ElseIf Me.txtCutSizeinMM = 1500 Then
        Me.txtCutSizeinMM.ForeColor = vbCyan
    End If
    '...ect
End Sub


You would end up with something like this:
(see attached screenshot)

You can use the color picker to create thousands of different colors.
;-)

Here is a sample as well:
;-)

JeffCoachman

untitled.JPG
db12.mdb

Try this.
Todd

Option Compare Database
Option Explicit
Dim lngcount As Long
Dim intWidthI As Integer
Dim intWidthII As Integer
Dim intWidthIII As Integer
   
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   ' Assumes width info is in textbox: txtWidth
   ' txtWidth font is set to bold if it differs from the previous value
   ' which was the same as the value before that
   
   ' increment the record counter
   ' formatting logic is applied after the 3rd record
   lngcount = lngcount + 1
   
   ' Always reset the font for the textbox first
   txtwidth.FontBold = False
   
   intWidthIII = intWidthII
   intWidthII = intWidthI
   intWidthI = txtWidth
   
   If lngcount > 2 Then
      If (intWidthIII = intWidthII) And _
            (intWidthI <> intWidthII) Then
         txtWidth.FontBold = True
      End If
   End If
End Sub

Select allOpen in new window

boag2000 - I print the worksheet and it's only a black and white printer so that won't work. But ... I like your thinking.

shambalad- Ok, I'll try this

stanbond,

Also note that your condition "seems" to only specified if there are more than two 1500's above an 1800.

What if there are two 1800's above a 1500?

In other words, this statement is not clear:
<To make it more obvious I want to make a conditional format that makes the 1800mm Bold if the two previous records are the same.>


Presuming that you really want to format the "Current" Value as bold if the two values above it were the same, and different from the current value, ...this may still result in confusion.
This is because "bold" could either mean 1500 or 1800 depending on the two values above.

If you give every Value a unique color, there is no chance for confusion.

You can even add in some funky logic for Italic, Underline, BackColor, BorderStyle, BorderColor. ...ect.

Or even change the color of the entire Detail section based on the value.

(play around with all of these options and I am sure you will come up with something that you and your operators will agree is ("nearly") fail-safe.

;-)

JeffCoachman

shambalad: It works great but I'm having problem with it always affecting the first record on the report.

Lets say the drops are  -
1500 - This is bold
1500
1500
1700 - This is bold

Why is the first record coming up bold?? I can't work it out?

It doesn't do it when they are all the same - only on orders where there is at least one different drop

stanbond,

I tried th same approach as shambalad at first.

There may not be an easy way to avoid the "First Record" anomoly.

Are you saying that this is a deal breaker?

Not sure, it seems intermittent.

Examples -
1800 - Bold
1800
2400
2400
____
1800 - Bold
1800
2400
2400
2400
_______
1800
1800
2400
1800 - Bold
_____
1800
1800
2400
2400
1800
(None Bold)
______
1800
2400 Bold
2400
2400
1800

Any thoughts of what could be the logic?

boag2000: Do you know what causes it?

Partially.

1. The issue is that since the first record does not have any "Previous" records to be compared with, so it is seen as a "Change".
2. When the Page number and number of pages are displayed on the Report, Acces will institute "Two Pass Printing".
This may cancel out any "first record" detection in the code.
So if you deleted the "Page x of y" textbox, it might be OK.

But let's not jump ahaead of ourselves.
I brought up  some points in my previous post that you seemed to have ignored.
So let's review...

Again, I am not quite sure of your logic.
You have still not made it clear if you want the current record to be bold ONLY it is 1800 following two consecutive 1500's

OR, if you want the current record to be bold if the previous two records were not the same as the current record and were both the same value.
Or if you will only be using two toolsizes.
Or if the only tools you will be using are 1800 and 1500
None of this is clear.
Please clarify.

In other words, is the only scenario you are concerned with a 1800 preceded by two consecutive 1500's

Why not make the current toolsize Bold if it is different that the previous toolsize?

This way they are alerted *whenever* there is a toolsize change.

If the first record being bold will really keep you up at night, delete the "Page x of y" textbox

The last scenario is what I am basing my new sample on.
View the report and let me know if this is acceptable.

JeffCoachan

untitled.JPG
Access-EEQ-24393624-FormatCurren.mdb

boag2000: Thank you for your long reply. I appreciate your time and follow your logic.

Unfortunately I have to rush off to an appointment so I'll more thoroughly look at it on monday.

To answer part of your question (if I understand it) -

1500 and 1800 were just random example numbers. We could use x & y.

When there are two or more consecutive x's the cutter often misses that y is different. So i wanted to highlight that y was different by applying a font property.  

What you have recommended above is also quite good. Many production notes have many different drops on them, generally we don't have problems with these, only the ones with a large number of one drop and one different one in the middle.

However, if I use an appropriate font property, (e.g Bold Or Underlined) it won't make the sheet too messy if we apply it to every different drop. I think that will be a good solution. I'll look at it further later.

Thanks,

Andrew

OK

<When there are two or more consecutive x's the cutter often misses that y is different. So i wanted to highlight that y was different by applying a font property. >

So they "always" make the msitake when the two previous sizes were the same and different from the current size...
...and they *Never* make a mistake when the sizes changes are consecutive?

This is why I proposed alerting them on "Every" size change.
This way the chance for error is minimized even further.

Since values like 1500 and 1800 can look similar from a distance, this seems like a good idea.
I can't see how it would hurt.

;-)

JeffCoachman

Hello -
Been away for a while. Sorry to have been out of touch for so long. I've not been on a computer for more than 4 days (can't recall the last time I've done that).
It is important to note that you must reset the font to a normal weight every time you begin the subroutine. Otherwise, if you do not change the font weight within the subroutine, it will simply remain the same weight as the last time you modified it.
Attached please find a sample database illustrating my approach.
Todd

FormatReportControls.mdb

With conditional formatting, if no conditions are met, the system default to no formatting.  Are you saying my approach would not work?

GrayL -
Sorry - You are correct. With conditional formatting, the reset is not necessary. You must do the reset if you change the formatting in the VBA.
Todd

Just curious GrayL :
Is there a reason you load intPrev and intPrev2 in the Detail_Print sub as opposed to the Detail_Format sub?
Todd

Seeing's as how it was a machine shop, I thought the only interest would be in the 'printed' result.  However, to see the result on screen, you would need it in the Detail_Format event - not really what's implied at http:#a24340222 though.