Changing field color based on date data for each record

[kevinj]

Greetings,

I am creating a "stoplight" type report that is based on the number of days between dates, the field will be highlighted in red, yellow, or green. I am able to change the background color on the textbox, but each subsequent record is set to the color as determined by the first record. Is there a way to do this for each record? I am using access 97. My code is something like this (I'm going off of memory as I left my code at work):

Dim mydate1 As Date, mydate2 As Date
Dim lngRed As Long, lngGreen As Long

db = DAO.database
rs = DAO.recordset

Set db = connect.database()
Set rs = db.connect("queryname")...can't remember exactly, its been a long day

rs.movefirst
Do until rs.eof
If mydate1 - mydate2 >3 Then
Me.dateofinterest.backcolor = lngRed
Else
Me.dateofinterest.backcolor = lngGreen
End If
rs.movenext
Loop

rs.close
set db = nothing
set rs = nothing

Anyhow, the code seems to work as is and is set to the form open event (I also tried form load), but it sets every text box to the color based on the if statement for the first record. I have also tried creating a bunch of subforms, but each opens in the first record. I realize that since each textbox is named the same thing in the form, that is why it is doing it.

Does anyone know of a different/better/easier way in access 97?

[missinglinq]

If this is a Single View form (where you view one record at a time) you can accomplish your task by placing your code in the Form_Current() event. If your form is Datasheet or Continuous you're not going to be able to do this in Access 97. Later versions of Access have a function called Conditional Formating that would allow you to do this for a continuous form, but not for your version.

If your code was working as far as setting the color right vis a vis the first record then all you should need is

Private Sub Form_Current()
 
Dim mydate1 As Date, mydate2 As Date
Dim lngRed As Long, lngGreen As Long
 
  If mydate1 - mydate2 >3 Then
    Me.dateofinterest.backcolor = lngRed
  Else
   Me.dateofinterest.backcolor = lngGreen
  End If
 
End Sub

If you have any further trouble, let us know.

Good Luck!

Linq

[kevinj]

Linq,

Thanks for the info. I am using a continuous form as the goal is to see many records at the same time. Do you know if there is a way to do this using a report instead of a form maybe?

Thanks!

[ADezii]

Linq,

Thanks for the info. I am using a continuous form as the goal is to see many records at the same time. Do you know if there is a way to do this using a report instead of a form maybe?

Thanks!

Place comparable code in the Format Event (Detail Section) of the Report.