Formatting text color of a single record (row) of a subform 2

[dallison]

I'm creating a database to track immunizations. I have a main form with names and other pertinent info, and I have a subform with the immunizations listed. The subform shows the following and is a continuous form:

ImmunizationName DateCompleted DateNextDue

I want to change the text color of the DateNextDue field based on todays date and if the immunization is overdue. I used the following code placed in the Form_Current event of the subform:

Private Sub Form_Current()
If txtImmunizationNextDue < Now() Then
txtImmunizationNextDue.ForeColor = vbRed
Else
txtImmunizationNextDue.ForeColor = vbBlack
End If
End Sub

This has the following effect; if a record in the subform is selected with an overdue shot ALL of the NextDue fields in the subform change to red. Is there a way to reference the txtImmunizationNextDue control for only one record in a continuous subform and change it's text to red and leave the others as black?

 

[dallison]

Sorry, forgot to mention that I'm using Access 97. I understand there's a &quot;Conditional Formatting&quot; option in the Format section of a text box in Access 2000. Is there a way to accomplish the same thing in Access 97?

 

[MichaelRed]

dallison,

You logic is misplaced. When you place code in the Form_Current event it applies to the FORM, not the record.

You need to have the code to do this in the detail section. In general, the code needs to check the condition for the single record but set the forcolor of EACH control you want to 'Highlight'.

I BELIEVE that Access includes an &quot;OnCurrent&quot; event in the sections of the forms. This would be the appropiate place to enter the code. Access 2K does not include this event, so I think the 'conditional formatting' is the replacement.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[pavla]

Hallo dallison,

There is only a very limited form on the new conditional formatting (Access 2000) technique available in Access 97:
1) Write a function which converts the data in the control you wish to colour to a number which is positive, negative, equal to zero or is null. For example, if there were only four immunizations given, then you could write:
Private Function ColourImmunizations()
Select Case Immunizations
Case &quot;Immunization1&quot;
ColourImmunizations = 1
Case &quot;Immunization2&quot;
ColourImmunizations = -1
Case &quot;Immunization3&quot;
ColourImmunizations = 0
Case &quot;Immunization4&quot;
ColourImmunizations = Null
End Select
Then in the control where the name of the Immunizations was displayed you would put instead in the Control Source property =ColourImmunizations(). In the Format property of the control you would write, for example, &quot;Immunization1&quot;[Red]; &quot;Immunization2&quot;[Black]; &quot;Immunization3&quot;[Green]; &quot;Immunization4&quot;[Blue].

2) In your case, however, I think you would still like to see the actual date when the immunization is next due, so the only coloured highlight would have to be in a separate control. I would write a function whose result is positive if the date of the immunization next due is greater than today and negative otherwise. Insert this function in the Control Source property of the new control. Then in the Format property of this extra control I would write &quot;NOW DUE&quot;[Red];[Black]. which would put the red note next to the dates which are later than today.

Regards, Pavla.

 

[dallison]

To: MichaelRed
Thanks, but there isn't a OnCurrent event for the details section of the form, only mouse move and click events. That's why I tried the Form_OnCurrent event. You're right though, it would make more sense to put the routine into the detail section. Gotta wonder why old Bill didn't include it, eh?

To: Pavla
Your tip works, but ideally I'd like to get the date itself color coded black, yellow or red. Black for not due, yellow for due within 120 days and red for overdue. I'm still toying around with it. I've learned something, since I've never tried putting a function into a control like that before!

Thanks for the replies. This has been a neat site to stumble upon and I'm looking forward to making some new cyber-friends!

 

[MichaelRed]

one other thought on the topic. Reports DO have the on current event for the sections. If your situation could use a report in preview mode, then the formatting of the control can be done in the detail. This woiuld - visually - give you the results desired, but it would be in a static view.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over