[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Conditional formatting based on previous record detail

Posted on 2009-05-08
19
Medium Priority
?
502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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