Continuous Forms - Change BackColor/AlternateBackColor per Record ? (1 Viewer)

[liamfitz]

I have a continuous from, which displays a recordset based on a query, in the usual way, with alternate 'BackColors' set for every other record. Is it possible to set these background colors, programatically, and IMPORTANTLY, have more than 2 colors ? The idea is to set the background color of the row according to a field criteria, in a given record, e.g.
If myDate < 1/1/2000 Then
Backcolor = RED
ElseIf myDate BETWEEN 1/1/2000 AND 31/12/2005 Then
Backcolor = BLUE
Else
Backcolor = GREEN
End If

( I know it's pseudocode, but you get the idea .... ) Many thanks, if you can help.

 

[VilaRestal]

Yes, although it's a fudge:

If you put a locked, disabled, unbound textbox that fills the form area (fills the row) and is sent to back behind all the other controls.

You can then use conditional formatting on that textbox to set its back colour to one of four possible colours (the default and the three conditions).

(Needless to say, this is one of the areas Access is, sadly, very poor at. Compare its conditional formatting to Excel's for example...)

 

[liamfitz]

That's ingenious of you sir ! I like your resourcefulness. Thank you.

 

[liamfitz]

Erm... thanks, but when you say ensure that the text box is Locked and Disabled, do you mean set the properties of 'Locked' to 'Yes', and Enabled to 'No', ( which I've done, to no avail ) I've also set the 'Conditional Formatting' for the Background TextBox as you suggested ( maybe the syntax isn't correct. I'll keep trying. ) but again it's having no effect.

 

[VilaRestal]

Locked Yes, Enabled No: That's correct, so it can't be interacted with.

Of course make sure it isn't transparent either.

And the conditional formatting needs to be an expression based on another field (of course not its own value - it never has a value)

It will be either an error in the conditional formatting formula or that its currently transparent.

It is something I've done a few times before.

To see an example of it working:

 

[VilaRestal]

Locked Yes, Enabled No: That's correct, so it can't be interacted with.

Although building the demo reminded me that for those records where the conditional formatting takes place (the not default format) the user can put the cursor in the textbox (the conditional format enables it).

To get round that put a single line of code in the textbox's GotFocus event that AnotherControl.SetFocus.

Other textboxes should almost completely cover it but there will always be a bit of space that they could click in.

 

[satamazar]

I haven't tried this but this would be the route I would explore:

Create a variable like

Dim Mydate1 as Date
Mydate1() = recordsourcename.mydate

and then a select statement like

Select Case MyDate1()
Case is < "1/1/2000"

'change back color

Case is > "1/10/2000"

'change back color

End Select

This would basically declare a variable, set the variable equal to the value you're passing but not storing, then create a select statement that runs on load using conditional formatting with the value you are passing.

granted it might take some tweeking though and I don't know how well conditionals work within a select statement but I would assume things could work with that and just put it on the On Load action.

Just a suggestion, others can flag it if the logic is bad.

 

[VilaRestal]

Indeed, you can refine the conditional formatting in code on the load event (especially used for picking better colours then the muddy/garish selection it gives you in the designer).

However, I'd suggest get it working without code first. The formula shouldn't need VBA:

First condition would be [myDate] < #1/1/2000#

 

[liamfitz]

That's very helpful of you, and some very good ideas. I've got it working now, as I want it, so many thanks. I'm now using the 'Expression Is' option, which allows me to reference another field bound to another text box( the field variable I use to test for my preconditions ), rather than outputting that value in my 'txtBGColor' object as well, just to get the background covering the whole of the 'record'. i.e several fields/Text Boxes.