I have a continuous form containing about 100 records. The background color is grey. Each record has 10 fields. I just wonder if I can do something to the form properties or in VBA so that when I click a record, say record #25, or clicking any field in this record, then the whole record #25 will have its background color changed to something different to the rest of the records in the list?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Back ground color change when a particular record is selected 4
- Thread starter xinyin
- Start date
Xin: Somewhere out there you might find code that can handle a "section" of the detail depending on the ID of the current record. I'm not sure how you could do it.
One alternative, if you fail to find a solution, is to add a "header" to the form, and give it one color, the detail section another color. In the header you can put the field names, and the values will show up in the detail section. In this was the appearence of the alternating records changes with each record and therefore makes the indidividual record you are viewing distinct from neighboring records. Good question. Good Luck.
One alternative, if you fail to find a solution, is to add a "header" to the form, and give it one color, the detail section another color. In the header you can put the field names, and the values will show up in the detail section. In this was the appearence of the alternating records changes with each record and therefore makes the indidividual record you are viewing distinct from neighboring records. Good question. Good Luck.
-
4
- #4
Here is a routine I did years ago, there are 2 versions, version 1 is if the current record you want to highlight is on the Main Form, version 2, if it is on a Sub Form.
Version 1, paste this into your Main Form's "Current" event:
Dim ctl As Control, strForm As String
Dim varCondition As Variant
On Error Resume Next
varCondition = Me!RecordID 'Change this to your Control Name with the Record# (Ideally a Unique No.)
For Each ctl In Me.Controls
With ctl
If .Tag = "ConditionalFormat" Then
With Me.Controls(ctl.Name).FormatConditions _
.Delete 'Delete any other Conditional Formatting, limit is 3
End With
With Me.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[RecordID]=" & varCondition)
.BackColor = 14024661 'Light Green
.ForeColor = 0 'Black
End With
End If
End With
Next ctl
Version 2, paste this into your Sub Form's "Current" event:
Dim ctl As Control, strForm As String
Dim varCondition As Variant
On Error Resume Next
varCondition = Me!RecordID 'Change this to your Control Name with the Record# (Ideally a Unique No.)
For Each ctl In Forms!YourFormName.YourSubFormName.Form.Controls
With ctl
If .Tag = "ConditionalFormat" Then
With Forms!YourFormName.YourSubFormName.Form.Controls(ctl.Name).FormatConditions _
.Delete 'Delete any other Conditional Formatting, limit is 3
End With
With Forms!YourFormName.YourSubFormName.Form.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[RecordID]=" & varCondition)
.BackColor = 14024661 'Light Green
.ForeColor = 0 'Black
End With
End If
End With
Next ctl
In version 2, obviously, change YourFormName to yours and YourSubFormName to the name as it appears in the Main Form.
Let me know if you have any problems with it.
Version 1, paste this into your Main Form's "Current" event:
Dim ctl As Control, strForm As String
Dim varCondition As Variant
On Error Resume Next
varCondition = Me!RecordID 'Change this to your Control Name with the Record# (Ideally a Unique No.)
For Each ctl In Me.Controls
With ctl
If .Tag = "ConditionalFormat" Then
With Me.Controls(ctl.Name).FormatConditions _
.Delete 'Delete any other Conditional Formatting, limit is 3
End With
With Me.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[RecordID]=" & varCondition)
.BackColor = 14024661 'Light Green
.ForeColor = 0 'Black
End With
End If
End With
Next ctl
Version 2, paste this into your Sub Form's "Current" event:
Dim ctl As Control, strForm As String
Dim varCondition As Variant
On Error Resume Next
varCondition = Me!RecordID 'Change this to your Control Name with the Record# (Ideally a Unique No.)
For Each ctl In Forms!YourFormName.YourSubFormName.Form.Controls
With ctl
If .Tag = "ConditionalFormat" Then
With Forms!YourFormName.YourSubFormName.Form.Controls(ctl.Name).FormatConditions _
.Delete 'Delete any other Conditional Formatting, limit is 3
End With
With Forms!YourFormName.YourSubFormName.Form.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[RecordID]=" & varCondition)
.BackColor = 14024661 'Light Green
.ForeColor = 0 'Black
End With
End If
End With
Next ctl
In version 2, obviously, change YourFormName to yours and YourSubFormName to the name as it appears in the Main Form.
Let me know if you have any problems with it.
I missed out something vital to the above routine working at all.
In design view of the form that has the controls that you want to highlight/color, set the Tag property to ConditionalFormat, just type in as one word ConditionalFormat, this can be any tag you want, but if you change it, remember to also change it in the code above.
In design view of the form that has the controls that you want to highlight/color, set the Tag property to ConditionalFormat, just type in as one word ConditionalFormat, this can be any tag you want, but if you change it, remember to also change it in the code above.
Hi Bill,
I tried using your code in a Main form and it did not work. I assume that in the .Add line right before the colours get changed that I change [RecordID] to my appropriate field.
I also made sure to change the Tag property of the fields I wanted to change to ConditionalFormat.
I am using Access 97. Does that make a difference? If so, do you know how I would be able to modify the code to work on my platform.
Thanks,
Haf
I tried using your code in a Main form and it did not work. I assume that in the .Add line right before the colours get changed that I change [RecordID] to my appropriate field.
I also made sure to change the Tag property of the fields I wanted to change to ConditionalFormat.
I am using Access 97. Does that make a difference? If so, do you know how I would be able to modify the code to work on my platform.
Thanks,
Haf
Hi hafeez12,
Yes, Access 97 doesn't support Conditional Formatting, but there is a way around that.
I have posted a working example of this at
The link to look at is Change Background Colour of the Selected Record
Let me know if you have any questions or problems with the method I've used.
Bill
Yes, Access 97 doesn't support Conditional Formatting, but there is a way around that.
I have posted a working example of this at
The link to look at is Change Background Colour of the Selected Record
Let me know if you have any questions or problems with the method I've used.
Bill
Hi TR6,
Link might have been misleading, it is actually a zip file that you can download and adapt to your own app.
Just click on Change Background Colour of the Selected Record and the file should download for you.
Hi AccessAce,
Please post the statement you are using in Conditional Formatting if Access 2000 +, please elaborate, post code.
Bill
Link might have been misleading, it is actually a zip file that you can download and adapt to your own app.
Just click on Change Background Colour of the Selected Record and the file should download for you.
Hi AccessAce,
Please post the statement you are using in Conditional Formatting if Access 2000 +, please elaborate, post code.
Bill
- Status
Similar threads
- Locked
- Question
- Locked
- Question
