Different background on a particular field in a form

Topic

New Reply

Hi to you all,

I have a field for vehicle licence expiry dates, I would like to be pre warned 30 days or so BEFORE the persons driving licence expires by making the field background turn red…

is this possible..

kind regards

Steve Gulliver
Southampton UK

Reply | Quote

Replies

I have a field for vehicle licence expiry dates, I would like to be pre warned 30 days or so BEFORE the persons driving licence expires by making the field background turn red…

is this possible..

Yes, assuming that we’re talking about a form as opposed to looking at the raw table.

One way to do this is to use Access VBA’s OnCurrent event, which will trigger whenever a new record is loaded into the form.

(air code)
[codebox]If [LicenceExpiry]-30 <= Date Then
LicenceExpiry.BackStyle = 1
LicenceExpiry.BackColor = rgb (255,0,0)
Else
LicenceExpiry.BackStyle = 0 ' sets it to transparent. change it to white (or whatever else) if you prefer by chaning the BackColor property instead
End If[/codebox]

But probably the easier way is to use conditional formatting. The expression to use is similar to the above: [LicenceExpiry]-30<=Date.

Reply | Quote

Conditional Formatting is another situation where the Date function needs its brackets.

[LicenceExpiry]]<Date()+30

Reply | Quote

Hi Guys

thanks very much for your help, I have inserted the routine you suggested, I think it’s almost there, however I think I must be doing something slightly wrong? When I put in this command it changes ALL my existing drivers on the database some 400 or so… Wonder if you chaps have any other ideas…

kind regards

Steve Gulliver
Southampton UK

Reply | Quote

Do you have a continuous form? A continuous form essentially contains a single set of controls – if you change the background color of a control, it will change in all displayed records.
Conditional formatting, however, works at the individual record level, so it’s more suitable for your purpose.

Reply | Quote

However, conditional formatting will change only the record effected in a continuous form, which is probably what is wanted.

Reply | Quote

Thanks for the confirmation.

Reply | Quote

Thank you very much indeed gentleman, I have used the formatting option. And it works fine..

kind regards

Steve Gulliver
Southampton UK

Reply | Quote