highlight fields when form loads

[Lloyd]

Before we had controls in place, through VBA code, some data didnt get
entered into some fields (text boxes and combo boxes). What I would like to
do is when the user loads a form, the form will highlight (change the back
color) of key fields that have null values. This will alert the user that
some key fields are needed, but I dont want to display any dialog boxes to
annoy them, just a "friendly reminder".

I'm not sure how to start the code to identify the 6 or 7 fields (text box
and combo box) I want to have checked when the form loads and also how to
then change the back color. I have tried a few ways but everything has
failed.

Can anyone help?

thanks

 

[Ken Snell \(MVP\)]

Use the form's Load event to test the controls for Null value and to set the
BackColor accordingly:

Private Sub Form_Load()
If Len(Me.NameOfControl1.Value & "") = 0 Then _
Me.NameOfControl1.BackColor = 255 ' Red color
If Len(Me.NameOfControl2.Value & "") = 0 Then _
Me.NameOfControl2.BackColor = 255 ' Red color
If Len(Me.NameOfControl3.Value & "") = 0 Then _
Me.NameOfControl3.BackColor = 255 ' Red color
If Len(Me.NameOfControl4.Value & "") = 0 Then _
Me.NameOfControl4.BackColor = 255 ' Red color
End Sub

 

[tina]

in the *form's* Current event procedure, you can run code to loop through a
collection of the specified controls, changing the BackColor property as
needed:

Dim colCtls As New Collection

colCtls.Add "ControlName1"
colCtls.Add "ControlName2"
colCtls.Add "ControlName3"

Dim var As Variant

For Each var In colTabs
If IsNull(Me(var)) Then
Me(var).BackColor = 11468799 ' light yellow
Else
Me(var).BackColor = 16777215 ' white
End If
Next

replace ControlName1, etc, with the correct names of the controls you wish
to check, one line for each control name. and replace the color codes with
whatever colors you want to use, of course.

suggest you also run a function on the AfterUpdate event of each of those
controls, to change the back color where appropriate, as

Private Function isCheckControl()

Dim str As String

str = Screen.ActiveControl.Name

If IsNull(Me(str)) Then
Me(str).BackColor = 11468799 ' light yellow
Else
Me(str).BackColor = 16777215 ' white
End If

Exit Function

you can add this function to all the controls at one time, as follows: open
the form in Design view. select all the controls you want to manipulate. in
the Properties box, click the Event tab, go to the AfterUpdate line, and
type the following, as

=isCheckControl()

hth

 

[Lloyd]

Tina,

I tried your code in the Current Event but it ran into an error ont his line

For Each var In colTabs

The error is: Runtime Error 13, Type Mismatch

is this becuase the fields are text boxes and combo boxes?

 

[Lloyd]

Ken,

I tested yours and it would not work on the form load event. Nothing
happened, no errors. But when I moved it to the form current event, it work.
Except it never turned off. When I opened the next record, the field the was
red in the previous record stayed red in every record. Would I need to reset
the background color of all the controls before the code is run to clear the
code?

 

[tina]

sorry, what a dumb mistake. i took this code from a module in a working
database, and adapted it - but i missed a name change. here's the corrected
code:

Dim colCtls As New Collection

colCtls.Add "ControlName1"
colCtls.Add "ControlName2"
colCtls.Add "ControlName3"

Dim var As Variant

For Each var In colCtls
If IsNull(Me(var)) Then
Me(var).BackColor = 11468799 ' light yellow
Else
Me(var).BackColor = 16777215 ' white
End If
Next

again, sorry about that! hth

 

[Lloyd]

That did the trick, thank you!!! One last quesiton,

Re the after update event:

I noticed I cant add that directly to VBA such as

Private Sub City_AfterUpdate()

=isCheckControl()
End Sub

I have to enter it directly into the after update event on the properties
screen.

However, just a quesiton if I had other stuff I wanted to run in an after
update event for the same field, is it possible to put the =isCheckControl()
code into such an event and if so how would I do it?

 

[tina]

yes, you can. making that sort of procedure a function gives it the
versatility to be called directly from the event line in the Properties box,
which can save a lot of time when you're coding multiple controls - but when
you need to include the procedure call in with other code running from an
event, just call it like you would any sub or function from within another
procedure, as

Private Sub City_AfterUpdate()

' whatever other code you need
isCheckControl
' and/or whatever other code you need

End Sub

notice the omission of the equal sign and closed parens. this is no
different than calling a built-in function from inside or outside a VBA
module. for instance, if you want to set the DefaultValue property of a
textbox control in a form to the system date, you would enter

Date()
or
=Date()

on the DefaultValue line in the Properties box. but if you refer to the
function within a module, you use

Date

without an equal sign, or a closed parens, because that is the VBA syntax
(which only uses the parens when actual arguments are being passed to the
called procedure AND when that procedure is part of an expression or is
preceded by the Call keyword).

hth

 

[Lloyd]

Tina,

that explains a lot and clears up the mystery. Thanks for taking the time
to explain it.

 

[tina]

you're very welcome

Tina,

that explains a lot and clears up the mystery. Thanks for taking the time
to explain it.

 

[Vincent Verheul]

Try using Conditional Formatting: In the Form design mode goto the Access
menu bar (A2003) and use Format => Conditional Formatting for each control
that you want a different back-color based on the field value. Use
'Expression Is' and enter: IsNull([ControlName]) or Nz([ControlName])=""