Conditional Formatting

[livvie]

I have a continuous form with a select button on it, I need to be able to change the colour of the select button or something else in that record, when the select button is clicked. I can change the backcolour on click but it changes it for all records not justthe one chosen. Any help would be great.

 

[scriverb]

Depending upon which version of ACCESS you are using you may or may be able to do this using Conditional Fomatting. A97 does not provide for this capability. A2k does.

Here is a link that can be used in both that helps describe how to do what you ask:

http://www.mvps.org/access/forms/frm0024.htm

Otherwise, try using Conditional Formatting which is available in the Form Design window. But, when you want the entire row to be highlighted I believe the techniques in the link will work for you.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[livvie]

CAnt use this I'm afraid as I already have an autonum field in my table. I might be able to manipulate it a bit. It's a lot of code for a small functionality but I mihgt have to do it.

 

[pbbriggs]

By "select button" do you mean something like an option button or check box (i.e., one button for each record, that pertains only to that record)? Are you, for example, trying to highlight a row when the "select button" for that row is checked?

If that is what you are trying to do (and you are using Access 2k), you can apply conditional formatting to other field(s) in the row. This will work on a continuous form.

Select the other field(s). In Format | Conditional Formatting, set Condition 1 to "Expression is" and then in the criteria box, fill in [NameOfSelectButton]=True. Then, of course, choose whatever formatting you want for the field(s).

 

[livvie]

Thanks pbbriggs that should work.

 

[livvie]

pbbriggs this didn't work. The record I am using is actually read only. The select button is a command button that takes some of the details from the selected record and passes them to another table. So unless I write to the rec when the select button is pressed I cant change the format if I just use a chkbox then all recs get formatted

 

[pbbriggs]

How does your command button know for which record(s) it should be passing details to the other table?

Could you have a checkbox (or some other true/false field) in your original table/query (upon which the form is based), and then just make all fields locked on the form except the checkbox? Then every time you check the checkbox next to one or more items, the entire record(s) - or whatever portion you wish - will be highlighted (by setting each field individually in Conditional Formatting). Then you could still have a command button used to commit portions of the selected record(s) to another table, and afterwards clear the checkboxes (and thus the formatting) if appropriate.

 

[ClydeData]

Try this code on the on current event of your form

To use
1) set the tag property of the contols you wish to change colour to CF
2) change the primary key if the code from YourID to the name of your primary key


Jimmy


=======
Private Sub Form_Current()
Dim ctl As Control, strForm As String
Dim CurrentRecord As Variant
On Error Resume Next

'change YourID here to your primary key

CurrentRecord = Me!YourID
For Each ctl In Me.Controls
With ctl
If .Tag = "CF" Then
With Me.Controls(ctl.Name).FormatConditions _
.Delete
End With
'change YourID here to your primary key
With Me.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[YourID]=" & CurrentRecord)
.BackColor = 16643528

End With
End If
End With
Next ctl
End Sub

 

[pbbriggs]

livvie,

I think it would help if you could explain a little better what you are trying to do. Are you trying to permanently highlight some records? Are you trying to highlight some records temporarily so that when the user clicks a command button, s/he knows which records are going to be committed to another table?

It looks like Jimmy's solution will be helpful in highlighting whatever record is currently selected.

Conditional formatting (tied to a true/false field like a check box or option button) will work if you want to highlight one or more fields in certain selected record(s). Then you could clear the checkmarks/true values whenever you want the highlighting to disappear (if at all).

I have an example of the latter scenario if you want to look at it.

 

[jbento]

Clyde,
I am trying to do something similar in my continuous form, but I want the text in the field to change to red after update.

This is how I modified the code, but it doesn't work. What am I doing wrong?

Private Sub Form_Current()
Dim ctl As Control, strForm As String
Dim CurrentRecord As Variant
On Error Resume Next

'change YourID here to your primary key

CurrentRecord = Me!MainID
For Each ctl In Me.Controls
With ctl
If .Tag = "CF" Then
With Me.Controls(ctl.Name).FormatConditions _
.Delete
End With
'change YourID here to your primary key
With Me.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[MainID]=" & CurrentRecord)
.ForeColor = vbRed

End With
End If
End With
Next ctl
End Sub

I even tried this code in the AfterUpdate event of the particular field that I want the text color to change in.

Can you help me?

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!