Solved

Conditional formatting based on previous record detail

Posted on 2009-05-08
19
410 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now