set row backcolour dependent on textbox value

[Eric the Viking]

Dear All

Your help would be much appreciated.

I am running Access 2003 and have a database where I have a tab form listing a series of records.

To aid user visualisation I would like to set the row background colour so that it is different depending on the value shown in TxtBox Events_place (there are 3 alternative values).

I have tried various iterations of:

Private Sub Form_Open(Cancel As Integer)
Dim Event_place As String (and Integer etc)
If Me.Event_place = "XXXXXX" Then
Me.Detail.BackColor = 16764057
Else
Me.Detail.BackColor = 16777215
End If
End Sub

Nothing seems to work. I have used this type of code to alter row height elsewhere without a problem.

Anyone any ideas where I am going wrong?

Cheers

Eric

 

[mdlueck]

Nothing seems to work. I have used this type of code to alter row height elsewhere without a problem.

In A2003? I thought that was a new capability with A2007.

 

[Eric the Viking]

Hi

I use:

Me.Detail.Height=Me.ANY CONTROL IN THE ROWS NAME YOU WANT.Height

or a multiple thereof:

Me.Detail.Height=Me.ANY CONTROL IN THE ROWS NAME YOU WANT.Height*x

Any thoughts on the colour issue?

Eric

 

[mdlueck]

I have made use of this tutorial, though I modified / enhanced it a bit.

Highlight Current Record in an Access Continuous Form
http://www.upsizing.co.uk/Art53_Highlight.aspx

Is that the sort of thing you are talking about?

In A2007, such is possible on Multiple Records forms \ Right click a text box in the Detail section of the form \ Conditional Formatting. From there you may define business rules when a particular formatting rule will ring true and thus be applied. You may also specify the formatting rules to be applied, such as foreground / background colors.

This also works on A2007 reports. Example: For some reports I have the date show up in bold red font if the date has passed.

 

[Eric the Viking]

Hi

Thanks for that had never thought of having two controls for the same value in a row doh

I set conditional format to same colour for background and text and sent it to the back and it works a treat.

An example of KISS I guess

Cheers

Eric

 

[mdlueck]

I am pleased to hear that the solution proved useful to you on A2003. I will note that such was possible with that version.