By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Change bg colour of different records in continuous forms

P: 8
Hi there,

I've been searching the net & the forums here over the last few days for help with my problem & am getting myself really confused.. hoping someone may be able to help me here.

I've got a continuous form which has 6 textboxes in each row, one of which contains the name of an Assessor, who (in another table) will have a particular colour assigned for him/her.

What I'm trying to do is loop through the records of the continuous form, get the colour value for the Assessor for that record, & then change the background colour of all textboxes in that record to that colour. (I hope that makes sense)

I've been messing around trying to do this with Recordset clones, then trying Me.Controls instead.. but so far no luck. (I am very foggy on whether I should be using a Recordset or directly working with the controls on the form..?)

Here's what I've got at the moment (but I keep changing it, so this is kinda muddled):

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim lngColour As Long
Dim ctl As Control

Set rs = Me.RecordsetClone
rs.MoveFirst

Do While Not rs.EOF

If Not IsNull(rs.Fields("AssessorName")) Then

lngColour = DLookup("[AssessorColour]", "Assessors", "[AssessorName]='" & rs.Fields("AssessorName").Value & "'")

' the following doesn't work...
'
For Each fld In rs.Fields
fld.Properties("BackColor").Value = lngColor
Next

' following doesn't work either -- it changes all textboxes on
' the whole form to lngColour! (permanently, for some weird reason?!)
' How do I just loop through the controls in 1 record?
'
' For Each ctl In Me.Controls
' If ctl.ControlType = acTextBox Then
' ctl.BackColor = lngColour
' End If
' Next ctl

End If

rs.MoveNext

Loop

So there are 2 kinda separate ideas there, neither right, & I really don't know if I'm even on the right track.. any help appreciated!

Thanks in advance,
Karen
Mar 21 '07 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Karen

The simple answer is you can't do conditional formatting in code. Anything you do will change all records and not just the current record.

I would have a hidden textbox on the form set to the Assessor colour and try to manipulate the Conditional Formatting accordingly.

Mary
Mar 21 '07 #2

ADezii
Expert 5K+
P: 8,619
Hi there,

I've been searching the net & the forums here over the last few days for help with my problem & am getting myself really confused.. hoping someone may be able to help me here.

I've got a continuous form which has 6 textboxes in each row, one of which contains the name of an Assessor, who (in another table) will have a particular colour assigned for him/her.

What I'm trying to do is loop through the records of the continuous form, get the colour value for the Assessor for that record, & then change the background colour of all textboxes in that record to that colour. (I hope that makes sense)

I've been messing around trying to do this with Recordset clones, then trying Me.Controls instead.. but so far no luck. (I am very foggy on whether I should be using a Recordset or directly working with the controls on the form..?)

Here's what I've got at the moment (but I keep changing it, so this is kinda muddled):

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim lngColour As Long
Dim ctl As Control

Set rs = Me.RecordsetClone
rs.MoveFirst

Do While Not rs.EOF

If Not IsNull(rs.Fields("AssessorName")) Then

lngColour = DLookup("[AssessorColour]", "Assessors", "[AssessorName]='" & rs.Fields("AssessorName").Value & "'")

' the following doesn't work...
'
For Each fld In rs.Fields
fld.Properties("BackColor").Value = lngColor
Next

' following doesn't work either -- it changes all textboxes on
' the whole form to lngColour! (permanently, for some weird reason?!)
' How do I just loop through the controls in 1 record?
'
' For Each ctl In Me.Controls
' If ctl.ControlType = acTextBox Then
' ctl.BackColor = lngColour
' End If
' Next ctl

End If

rs.MoveNext

Loop

So there are 2 kinda separate ideas there, neither right, & I really don't know if I'm even on the right track.. any help appreciated!

Thanks in advance,
Karen
You can do what you request in the OnCurrent() Event of the Form but only for the Current Record.
Mar 21 '07 #3

P: 8
Thanks heaps for your replies Mary & ADezii! I was hoping that wouldn't be the answer, tho.. will have to figure out a different way to achieve this.. arrgh!

Will investigate Conditional Formatting, thanks for the tip, Mary.

Unfortunately ADezii I need to do it for all records, not just the current record, so that the user can quickly see - by scanning the list - which records need X action (from one colour), which records need Y action (from another colour), ... etc.

Obviously, filtering the form to only show one particular type of record at a time would do the trick, however the client is used to using a spreadsheet in which they coloured the relevant lines appropriately, & they want to continue being able to do that.

Any more tips appreciated -- but thanks heaps for your responses anyway, really appreciate them, saved me wasting any more time on that code! :)

Thanks again,
Karen
Mar 24 '07 #4

Denburt
Expert 100+
P: 1,356
My suggestion would be to use a report to show what records need what action, I can easily format a particular row in a report depending on criteria, forms are handled diferently though. Use the form to filter the actions needed and then take the appropriate action for those roecords...
Mar 24 '07 #5

ADezii
Expert 5K+
P: 8,619
Hi there,

I've been searching the net & the forums here over the last few days for help with my problem & am getting myself really confused.. hoping someone may be able to help me here.

I've got a continuous form which has 6 textboxes in each row, one of which contains the name of an Assessor, who (in another table) will have a particular colour assigned for him/her.

What I'm trying to do is loop through the records of the continuous form, get the colour value for the Assessor for that record, & then change the background colour of all textboxes in that record to that colour. (I hope that makes sense)

I've been messing around trying to do this with Recordset clones, then trying Me.Controls instead.. but so far no luck. (I am very foggy on whether I should be using a Recordset or directly working with the controls on the form..?)

Here's what I've got at the moment (but I keep changing it, so this is kinda muddled):

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim lngColour As Long
Dim ctl As Control

Set rs = Me.RecordsetClone
rs.MoveFirst

Do While Not rs.EOF

If Not IsNull(rs.Fields("AssessorName")) Then

lngColour = DLookup("[AssessorColour]", "Assessors", "[AssessorName]='" & rs.Fields("AssessorName").Value & "'")

' the following doesn't work...
'
For Each fld In rs.Fields
fld.Properties("BackColor").Value = lngColor
Next

' following doesn't work either -- it changes all textboxes on
' the whole form to lngColour! (permanently, for some weird reason?!)
' How do I just loop through the controls in 1 record?
'
' For Each ctl In Me.Controls
' If ctl.ControlType = acTextBox Then
' ctl.BackColor = lngColour
' End If
' Next ctl

End If

rs.MoveNext

Loop

So there are 2 kinda separate ideas there, neither right, & I really don't know if I'm even on the right track.. any help appreciated!

Thanks in advance,
Karen
Good News, webgirl. After further investigation, there is a method by which you can change the Background Color of either 'ALL' or 'SPECIFIC' Text Boxes on a Continuous Form for 'ALL' Records. Let me know if this is what you are looking for:
Expand|Select|Wrap|Line Numbers
  1. 'To change the Background Color of the LastName Field for all Records 
  2. 'on a Continuous Form to Red
  3. Me.Section(acDetail).Controls("LastName").BackColor = QBColor(4)
Expand|Select|Wrap|Line Numbers
  1. 'To change the Background Color of all Fields for all Records for all Records
  2. 'on a Continuous Form to Yellow:
  3.  
  4. On Error Resume Next
  5. Dim ctl As Control
  6.  
  7. For Each ctl In Me.Section(acDetail).Controls
  8.   ctl.BackColor = QBColor(14)
  9. Next
Mar 24 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
Stephen Lebans has a couple of sample DBs that formats the color of rows on continuous forms. He shows how to color the current row, alternate rows and rows by criteria. I've never really taken a close look under the hood (or the bonnet, as the guys and gals across the Pond say) to see how it works, so don't ask!

http://www.lebans.com/formatbycriteria.htm
Mar 24 '07 #7

P: 8
Hey wow, thanks so much everyone! I won't get to try this till Tuesday.. or maybe tomorrow night, but will post back with the results.

Just some comments tho:

Denburt - yes, I thought (after previous suggestions) that a report must be the way, however when I tried that, I couldn't actually add a button to do something with a particular record (want to have "View Details" buttons for each record, or something similar).. was able to add a button to the form, but not able to add event code to it. Unless I'm missing something, I don't think that's gonna work for what I need.. (but thanks anyway!)

ADezii - thanks again for your help, but I think (looking at the 1st code) that will only change all LastName fields in all records to one colour (?), while I need to change all fields of one record to one colour, & all fields of another record to a different colour.. etc. (So, for e.g., FirstName, LastName, & Suburb for one record need to be blue, while FirstName, LastName, & Suburb for another record may be green.. etc) I haven't actually tried the code yet though, so will check it out anyway & let you know, thank you!

missinglinq - now that looks great, so I've just downloaded the 3 sample db's... and oh man, there's some seriously heavy code in there! It does do exactly what I need, but it's doing it by creating a bitmap which is then coloured accordingly.. and it uses memory allocation, buffering, twips & a whole lotta other stuff I'm not really up with.. yet..! I will see if I can work it out more in a couple of days when I'll have more time. Thanks heaps for pointing me to it tho, really appreciate it!

So thanks so much again to everyone, & I will let you know how I go!

Cheers
Karen
Mar 25 '07 #8

P: 8
I've had to move this one way down the priority list, so won't be giving this any further attention for a couple of weeks.. just thought I'd let you all know, in case you're wondering how I went :)

Cheers (& thanks again!)
Karen
Mar 28 '07 #9

missinglinq
Expert 2.5K+
P: 3,532
Don't you just hate it when life intrudes? ;0)>
Mar 28 '07 #10

P: 8
Haha! Nice one, missinglinq. And yep indeed. ;-)
Apr 4 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.