Coloring records..?

[kenjoswe]

Hi all,

I have records in a listbox with a field representing different status.

It would be nice to have different colors depending on the value of the "status" field.

How can I do this?

/Kent J.

 

[smeadows]

Hi

You can use conditional formating, go to Format, conditional formating on tool bar, this will work fine on forms

Stephen

 

[kenjoswe]

Smeadows,

Don't have that function.
I have Access-97.

/Kent J.

 

[Birddog]

Hi,

I'm sort of new at this but I think the following would work. Of course, you already know the values which can be in the status field or you couldn't pick what colors to change to, right?

I set up a Select Case statement on the lost focus event of the list box which changes the ForeColor based on the list box's value.

You should be able to do the same thing with the BackColor or the label or whatever form object you want to change the color of.

Hope this helps, like I said I'm usually on tek-tips looking for answers and not giving them!

birddog

Private Sub lstStatus_LostFocus()
On Error GoTo err_this
    
    Select Case lstStatus.Value
        Case "Apples"
            lstStatus.ForeColor = vbRed
        Case "Bananas"
            lstStatus.ForeColor = vbYellow
        Case "Grapes"
            lstStatus.ForeColor = vbGreen
    End Select
    
exit_this:
    Exit Sub
    
    
err_this:
    MsgBox Err.Description & " Error#: " & Err.Number
    Resume exit_this
    

End Sub
[code]

 

[kenjoswe]

Birdog,

Thanks for your reply!

If I understand your solution correct the status of a single record in a listbox affects all records.

What I want is to have different color on different records
if the records have different status. I'm afraid that this is not possible in Access.

/Kent J.

 

[Birddog]

Anything's possible!

I'm probably misunderstanding still but this works on the test form I set up. I moved the code to the On Current event of the form. It now makes the background of the form change colors based on the value of your list box.

I think this is what you want since On Current will "fire" when the focus moves from one record to another.

Thanks for posting the question anyway. I can think of a bunch of uses for something like this in a project I'm currently working on.

Private Sub Form_Current()
On Error GoTo err_Form_Current
    
    Select Case lstStatus.Value
        
        Case "Apples"
            Detail.BackColor = vbRed
            
        Case "Bananas"
            Detail.BackColor = vbYellow
            
        Case "Grapes"
            Detail.BackColor = vbGreen
            
    End Select
    
exit_Form_Current:
    Exit Sub
    
    
err_Form_Current:
    MsgBox Err.Description & " Error#: " & Err.Number
    Resume exit_Form_Current
    


End Sub

[code]

 

[Frink]

Hallo,

In Access '97 there is no way of changing colours in the way you require, as far as I'm aware.
What you can do is to include a number of overlapping fields of colour and use the appropriate one as a background. You can't do this in a list box, but you could do it on a subform which you make look like a list box.
To do it you need to find a character in your character set which is a block of solid colour. That's the hard part.
For the moment, I'll use the underscore character to provide a coloured underline.
What you need to do is create a number of transparent text fields the width of the record, and set their forecolour to the colours required.
Then put the following code in each control source:
=IIf([intStatus]=1,string$(20,"_&quot,"&quot
changing the status comparison for each value/colour of status

Does that make sense? I'm a bit rushed today. I'm sure there was a FAQ about this somewhere....

Give it a go and see what happens

- Frink

 

[kenjoswe]

Frink,

Can you send a sample to me?


/Kent J.

 

[Frink]

Here's the text of a FAQ I'm submitting:

If you have a field [strStatus] in a table which can have values of "On", "Off" or blank, and you want "Off" records to have a Red background and "On" records to have a Green background, this is the FAQ for you.

Make a tabular form based on the table, and make all the backgrounds to the fields in the detail section transparent.
Now create a text field which covers the whole of the detail section. Make it font WebDings, Foreground colour Red, Disabled and Locked. Make it's background transparent and select 'Move To Back'
In its Control Source put:

=IIf([strStatus]="Off",String$(20,"g"),"")

This field will be empty unless strStatus="Off", in which case it would be a string of 20 WebDings g characters, which happen to be a solid block.
Create another identical field, but this time make it Green and its Control Source should be:

=IIf([strStatus]="On",String$(20,"g"),"")

Now open the form. All records with a status of Off will have a Red background, and all records with a status of On will have a Green background.

If the coloured bar is not high enough then increase the font size.
If the coloured bar is too short then increase the '20' in the Control Sources.
If the coloured bar does not look like a coloured bar then replace the 'g' in the control sources with an alternative letter and change the font to something more appropriate. A good way to find the character to use is by using Insert Symbol in Word.

Hope that makes your forms prettier,


- Frink

 

[Frink]

Hallo again,

Another way of colouring text (but it would not be editable)
would be to use multiple text controls for each field, one for each required colour.

In the previous example, make a field called StatusRed.
Set it's control source to
=IIf([Status]="Off",[Status],"&quot
and its forecolor to red
Create another in exactly the same place, call it StatusGreen and set its control source to:
=IIf([Status]="On",[Status],"&quot
and its forecolor to green

Maybe that's more what you were looking for,

- Frink

 

[kenjoswe]

Frink,

Thanks a lot!

It really worked.

/Kent J.