Change bg colour of different records in continuous forms

[webgirl]

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

[MMcCarthy]

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

[ADezii]

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.

[webgirl]

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

[Denburt]

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...

[ADezii]

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:

'To change the Background Color of the LastName Field for all Records 
'on a Continuous Form to Red
Me.Section(acDetail).Controls("LastName").BackColor = QBColor(4)

'To change the Background Color of all Fields for all Records for all Records
'on a Continuous Form to Yellow:
 
On Error Resume Next
Dim ctl As Control
 
For Each ctl In Me.Section(acDetail).Controls
  ctl.BackColor = QBColor(14)
Next

[missinglinq]

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

[webgirl]

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

[webgirl]

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

[missinglinq]

Don't you just hate it when life intrudes? ;0)>

[webgirl]

Haha! Nice one, missinglinq. And yep indeed. ;-)