Solved

Conditional formatting based on previous record detail

Posted on 2009-05-08
19
424 Views
Last Modified: 2013-11-28
I have a report which serves as a  manufacturing worksheet in our factory and it shows the cutting sizes for pieces of material.

Our cutters keep making a mistake when there is multiple cuts at one size (e.g 1500mm) and then one at another size (e.g. 1800mm). For some reason they get in a groove and accidentally cut the 1800 at 1500.

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.

Anyone got any idea what expression I could use to say -

If currentrecord - 1 = currentrecord - 2 but <> currentrecord

Thanks,

Andrew
0
Comment
Question by:stanbond
  • 6
  • 6
  • 4
  • +1
19 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24340192
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
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24340194
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

Open in new window

0
 

Author Comment

by:stanbond
ID: 24340222
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
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24340294
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
0
 

Author Comment

by:stanbond
ID: 24340864
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?
0
 

Author Comment

by:stanbond
ID: 24340877
It doesn't do it when they are all the same - only on orders where there is at least one different drop
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24340953
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?
0
 

Author Comment

by:stanbond
ID: 24341006
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?
0
 

Author Comment

by:stanbond
ID: 24341007
boag2000: Do you know what causes it?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24341547
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
0
 

Author Comment

by:stanbond
ID: 24341609
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24341747
OK
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24344041
<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
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 24349078
Before any Subs or Functions in the code module behind the report, created two public variables.

Public intPrev as Integer, intPrev2 as Integer

In the On Print event of the Detail section of the report, assuming a textbox named tbxSize bound to the field Size:

Private Sub Detail_Print()
 
  intPrev2 = intPrev
  intPrev = Me!tbxSize

End Sub

Now set the Conditional Formatting for the Size control  to the Expression

(intPrev) = intPrev2) AND (Size <> intPrev)

selecting Bold if true

In pseudo code every time the report prints a Size, it executes the Print() event code.  Then when the individual control containing Size is formatted, you will get a BOLD format if the two previous records were equal but are not equal to the current record - at least that is the intent and logic behind my suggestion.
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24374352
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24377116
With conditional formatting, if no conditions are met, the system default to no formatting.  Are you saying my approach would not work?
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24378034
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
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24378350
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24378501
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.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Ms Access VBA Variables 6 28
A Function to parse a text string 4 36
Access Changing Number to Date with Seperator 5 21
Help with DoEvents 8 28
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question