Conditional Format Based on Previous and Current Record

[KevinM]

I'm having trouble with a tricky conditional format in a continuous subform.

The criteria is based on field [GroupNo] when it changes (increases) which are always listed in numerical order.

Basically all I want to is for the text box colour to alterante (eg blue & yellow)when GroupNo changes value

So it would look something like this....

Group NoID (highlight colour)

1 (yellow)
1 (yellow)
1 (yellow)
2 (blue)
2 (blue)
2 (blue)
3 (yellow)
3 (yellow)
etc etc

btw, although values will always be in order they may not all be output, so it's not just case of odd, even, odd, even (that would be too easy!)

I can retrieve the Previous Record's GroupNo value (via a function) in another text box and compare current GroupNoID with this in the Format Expressions and manage to change the colour, but it won't sustain this new colour for the subsequent same GroupNos, obviously as the values have changed.

So how can I....

Change the colour when the value changes.
Keep this new colour until it the value changes (increases) again.

Many Thanks

 

[WayneRyan]

Kevin,

Somewhat similar (I hope) ...

http://www.access-programmers.co.uk/forums/showthread.php?t=68837&highlight=conditional+wayne

Wayne

 

[KevinM]

Thanks Wayne but I have looked at that post and downloaded and tried your demo before posting.

It's not quite what I'm looking for (almost!).

I need the colour to alternate per numbers.

So as my numbers will always increase yet also even out out for 3 or 4 records then it must change colour when the number changes (increases), remain in this colour until the number increases again and then change colour again.

On your demo the colour stays red only if the neumber increases which is not what I want.

I think I have to look at my original 'GetPreviousRecord' code and somehow create a toggled flag value that alternates when the field value in question changes.

thanks

 

[MarkK]

In reality there is only one control, and though a separate instance is displayed for each record, when you modify attributes of this object all instances of it reflect the change.

 

[WayneRyan]

Kevin,

New Demo ...

Wayne

 

[MarkK]

Nice work!

 

[KevinM]

Wayne, you make it look so simple.

Thanks, good work.

Now why didn't I think of simply using the 'toggle colour' in code instead of trying some complex code looking up previous records etc.

Cheers

 

[WayneRyan]

Kevin & LagBolt,

Thanks for the compliments. My initial reaction was to clone and traverse a
recordset, but using a global had a very simple feel to it.

However, with this solution, you can generate a report, change no data, regenerate
the report, and it will be different! Although subtle, it would probably really
drive someone nuts wondering out why the color "bands" kept changing between report
runs.

Therefore, I retract the previous solution and am going to seek employment at
Microsoft!

Seriously, One of the difficulties of using VBA functions in queries is that there
is no place to put Initialization logic. In this example, the field color's starting
value is a guess. The query will just use the previous values from the last invocation.
What if it was important for the first band to be red? That model can't handle that.

If it REALLY mattered, I guess that you set CurrentNumber & CurrentColor prior to
opening the form.

BUT, that also brings up the question - Can a query's function call know when
it is the FIRST invocation? What if your function had to perform some action
on ONLY its first time called?

Any ideas?
Wayne

 

[MarkK]

"Can a query's function call know when it is the FIRST invocation? What if your function had to perform some action on ONLY its first time called?"

This seems do-able though. In my downloaded version of your Conditional.mdb, my frmTest.Form_Open() event handler sets the public variables in Module1, and gets the colour sequence to always start red.
So, if you have a function call from a query, and the function increments a counter each time it is run, and said counter is Public in a standard module, and prior to running the query aforementioned counter is set to zero, isn't Bob your uncle? Doesn't this implement a basic query init?

What this all suggests though, is the possibility of coercing Access to perform alternating background colours on a continuous form. Now that would be a neat trick.

 

[MarkK]

Modifying WayneRyan's Toggle.zip...
Alternating background.

 

[WayneRyan]

Lag,

Nice job. Never used: "Module.Variable = Value"

I did experiment with a Date/Time stamp. Worked OK too.

See ya,
Wayne

 

[MarkK]

Cheers man.

 

[Verda]

bumped....
i want to alternate colours, red for female patients and blue for male patients for each time their sex is determined. i'd like the whole forms background colour to change.
what is the best way of doing this using Access 2003.
thanks a lot guys.

 

[stopher]

Do you mean continuous forms like in the example? The example gives the impression that the background does change (but really it's just a text box that fills the entire background). So if you are using continuous forms like this and want colour coding according to sex, then just change the formatting condition so that expression is [PatientSex]="m" and the colour is set to blue. Assuming that you only ever have male and female patients then the default format can be set to be red.

If you are using a single form then you should probably take a different approach. In the forms On Current event you can check the sex of the current record and set the background colour of the form accordingly. The code will be something like this:

If Me.Sex = "m" Then
    Me.Detail.BackColor = vbBlue
Else
    Me.Detail.BackColor = vbRed
End If

hth
Chris

 

[Verda]

hey thanks for your reply Chris. I tried that peice of code, but i wasn't too sure exactly where to place it. i'm pretty new to this whole thing. i've attached the database. any further advice on where i can attach this piece of code would be highly appreciated. thanks.

 

[stopher]

You have to add the code at form level.

Go into the form properties (right-click in the little black square box in the top left of the form in design view and select properties).

Click on the OnCurrent event property, then click on the three dots that appear at the end of that line.

The code window will appear showing an empty procedure (as well as other procedures for the buttons on your form. Paste the code here (changing "m" to "male" because I see you use the word rather than the letter.

I've done it in your sample so you can follow the above and see what I've done (see the attached file)

The code only changes the backcolour for the main section 'cos I can't remember exactly how to reference the other sections without looking it up. But a couple of lines of extra code with do the whole form.

hth
Chris

 

[Verda]

now THAT is what i call COOL!

 

[sanmisds]

Form Control does not return values

I am modifying one of the MS Template to fit for my need. I have added few more controls and labels and added those new controls in the related Tables as well. When I go to 'View' mode, I can see my selections but I can not select them. Also, for some other controls, I get '#Name?' inside those boxes. I went to the table and manually entered some sample data but not all of them exists, especially to all of the new controls I added. I am new to all these stuff when it comes to creating it. Can you please help? Thanks in advance. If you would like to see sample, please let me know I will send it to you.

 

[stopher]

now THAT is what i call COOL!

Glad to help.

 

[stopher]

I am modifying one of the MS Template to fit for my need. I have added few more controls and labels and added those new controls in the related Tables as well. When I go to 'View' mode, I can see my selections but I can not select them. Also, for some other controls, I get '#Name?' inside those boxes. I went to the table and manually entered some sample data but not all of them exists, especially to all of the new controls I added. I am new to all these stuff when it comes to creating it. Can you please help? Thanks in advance. If you would like to see sample, please let me know I will send it to you.

Might I suggest you start a new thread with this question as it does not appear to have anything to do with this thread as far as I can see. You might get better response then.
Chris