Public funtion to change backcolor after update of field

[gykiang]

Hi,

My form contains several text fields with backcolor of beige. My wish is to change the beige color of the field to white after update of the field. The below sample code works well in event procedure for every field (AltINVCL is one of the fields) :

Private Sub AltINVCL_AfterUpdate()
If Not IsNull(Me.AltINVCL) Then
Me.AltINVCL.BackColor = RGB(255, 255, 255)
Else
Me.AltINVCL.BackColor = RGB(252, 230, 212)
End If
End Sub



However, this is tedious as the code needs to be modified & repeats for too many fields in VBA. Hence, I'm trying create a short cut by writing a universal code in the Public Function and run the function from the event procedure of each field.

Can some help and advise how the code in the public function to be written ?

And also, how do I run this function in desired field with backcolor of beige ?

Many thanks for any help render.


Regards
Kiang

[June7]

This will not work for form in Continuous or Datasheet view.

Have you considered textbox Conditional Formatting?

[gykiang]

It is a single form.

[June7]

For the generic code, I was going to suggest using ActiveControl but that doesn't have the BackColor property.

I am not seeing a way to construct a generic procedure.

Building the code may be tedious but once done, it's done.

Me.AltINVCL.BackColor = IIf(IsNull(Me.AltNVCL), RGB(252, 230, 212), RGB(255, 255, 255))

Copy/paste and edit for control names.

[gykiang]

Thanks.

They are bounded fields, so the expression will not work. Guess I have to repeat the code in the event procedure of each field.

[gykiang]

Thanks June7.

This code works in event procedure & it more or less simply the long code that I have done previously.

I didn't know that IIF works in VBA as I thought it is a control used in query.

[gykiang]

New problem found with code :

Me.AltINVCL.BackColor = IIf(IsNull(Me.AltNVCL), RGB(252, 230, 212), RGB(255, 255, 255))

After update of current record with data, the backcolor is changed to white, but the next or new record which is null did not turn back to beige unless I leave the current record blank or null.

I have inserted the IIf code only in current form because if I repeat the same in the event procedure of After Update , but ambiguous errors appeared after record update.

[gykiang]

My apology... I got it. Error was caused by replicated code in copy & paste.