is there a Current Control Property?

[Dennis]

All,

I'm on Access via XP Office Pro w SP3 on XP PRO w SP3.

I want to change the control's border color as I enter control and reset it
when I leave a control. The purpose of this is to draw the user's eye to the
current data entry field.

I know I can change the color using the On Entry and on Exit Events but as I
am still experiementing with this I would like to take it one step further.

I want to place the following code in the Entry and Exit events:

private sub On_CtlName_Entry()
Call SetControlBorderColor
end sub

private sub CtlName_On_Exit()
Call SetControlBorderColor
end sub


Private Sub SetControlBorderColor

plngOrigBorderColor = me.form."CurrentControl".BoderColor
me.form."CurrentControl".BorderColor = plngBorderColorNew

end sub

Privat sub ResetControlBorderColor

me.form."CurrentControl".BorderColor = plngOrigBorderColor

end sub

The variables plng... would be Private variables declared at the top of the
module.

Since I am still experiement with this process, I migth want to change the
backgroup and font color. I might also expand this functionally to change
the contents of the control to red if there is an error. I'm not quire sure
where this experiementing is going to take me.

Is there someway I can:

1. Pass the name of the current control to the subroutine and have the
subroutine change some of the control's properties? (Some of these controls
might be on a subform.)

or

2. Can I have the subroutine determine the name of the current control and
use that name to change the control's properties? I know that
"Screen.ActiveControl.Name" will return the current control's name, but I
don't know how to use that information to do what I want.

Thank you for your assitance.

 

[Dirk Goldgar]

All,

I'm on Access via XP Office Pro w SP3 on XP PRO w SP3.

I want to change the control's border color as I enter control and reset
it
when I leave a control. The purpose of this is to draw the user's eye to
the
current data entry field.

I know I can change the color using the On Entry and on Exit Events but as
I
am still experiementing with this I would like to take it one step
further.

I want to place the following code in the Entry and Exit events:

private sub On_CtlName_Entry()
Call SetControlBorderColor
end sub

private sub CtlName_On_Exit()
Call SetControlBorderColor
end sub


Private Sub SetControlBorderColor

plngOrigBorderColor = me.form."CurrentControl".BoderColor
me.form."CurrentControl".BorderColor = plngBorderColorNew

end sub

Privat sub ResetControlBorderColor

me.form."CurrentControl".BorderColor = plngOrigBorderColor

end sub

The variables plng... would be Private variables declared at the top of
the
module.

Since I am still experiement with this process, I migth want to change the
backgroup and font color. I might also expand this functionally to change
the contents of the control to red if there is an error. I'm not quire
sure
where this experiementing is going to take me.

Is there someway I can:

1. Pass the name of the current control to the subroutine and have the
subroutine change some of the control's properties? (Some of these
controls
might be on a subform.)

or

2. Can I have the subroutine determine the name of the current control and
use that name to change the control's properties? I know that
"Screen.ActiveControl.Name" will return the current control's name, but I
don't know how to use that information to do what I want.

Both Screen.ActiveControl (the current control throughout the application)
and Me.ActiveControl (the current control on this form) return a reference
to a Control *object*, so you don't have to do anything further to
manipulate that control's properties. You could write:

With Me.ActiveControl
plngOrigBorderColor = .BorderColor
.BorderColor = plngBorderColorNew
End With

If your function code is going to be in the module of the form in question,
not in a standard module, I'd use Me.ActiveControl to get the control
reference, rather than Screen.ActiveControl.

I'd recommend making your border-setting procedures into Functions, rather
than Subs. If you do, then you don't even have to write event procedures
for the Enter and Exit events of the controls on your forms. Instead, you
can call the functions directly from the event *properties* of the
controls -- right there on the property sheet! -- by setting the On Enter
property to:

=SetControlBorderColor()

and the On Exit property to:

=ResetControlBorderColor()

That can save you a lot of time, because you can select all the controls at
once in design view, bring up their joint property sheet, and enter the
function expressions on their property lines. But remember, to do this, the
procedures must be defined as Functions, not Subs.

 

[Dennis]

Dirk,

Thank you very much. It is a very elegant solution. Especially, selecting
all of the controls at once and entering the On Entry and On Exit. Thank you
for making this very simple.

 

[Dennis]

Dirk,

It all worked great except for one thing.

I had to put the functions in the form's VBA code. I want to put the
function in my BaseUtil modules so I can call that function from any form.

I originally put it in BaseUtil, but Access told me that no such function
existed, so I moved it to my form's module and it worked.

Here is the original code:


Public Function SetControlColors() As Boolean

With Me.ActiveControl ' Change the control's
border color and save orig color
glngBorderColorOrig = .BorderColor
.BorderColor = glngBorderColorNew
End With
SetControlColors = True

End Function

Public Function ResetControlColors() As Boolean

With Me.ActiveControl
.BorderColor = glngBorderColorOrig ' Reset the control's
border color to original value
End With
ResetControlColors = True
End Function


As I said I originally put it in BaseUtil and set the On Enter and On Exit
events to
"= BaseUtil.SetControlColors()"

and Access set it to "=[BaseUtil].[SetControlColors()]

Any suggestion on how I can make this a public function running out of
BaseUtil?

Thanks,

 

[Dirk Goldgar]

Dirk,

It all worked great except for one thing.

I had to put the functions in the form's VBA code. I want to put the
function in my BaseUtil modules so I can call that function from any form.

I originally put it in BaseUtil, but Access told me that no such function
existed, so I moved it to my form's module and it worked.

Here is the original code:


Public Function SetControlColors() As Boolean

With Me.ActiveControl ' Change the control's
border color and save orig color
glngBorderColorOrig = .BorderColor
.BorderColor = glngBorderColorNew
End With
SetControlColors = True

End Function

Public Function ResetControlColors() As Boolean

With Me.ActiveControl
.BorderColor = glngBorderColorOrig ' Reset the control's
border color to original value
End With
ResetControlColors = True
End Function


As I said I originally put it in BaseUtil and set the On Enter and On Exit
events to
"= BaseUtil.SetControlColors()"

and Access set it to "=[BaseUtil].[SetControlColors()]

Any suggestion on how I can make this a public function running out of
BaseUtil?

I imagine the module BaseUtil wouldn't compile, due to the use of the
keyword Me, which only applies in a class module -- and "Me.ActiveControl"
is meaningless in a standard module.

In the standard-module versions, use Screen.ActiveControl instead of
Me.ActiveControl, and don't include any reference to the module name when
calling it. Just use event properties like this:

=SetControlColors()

Note: as an alternative, you could rewrite the functions to receive a
reference to the form, and pass that when you call them. But for your
purposes with these functions, you probably don't need to do that. Just be
aware that your highlighting would get messed if the user had two different
forms open at the same time, both using this technique for highlighting, and
was switching between them.

 

[Dennis]

Dirk,

The user could well be running mutiple forms at a time.

So how would I "Note: as an alternative, you could rewrite the functions to
receive a reference to the form, and pass that when you call them."

Thanks,

 

[Dirk Goldgar]

The user could well be running mutiple forms at a time.

So how would I "Note: as an alternative, you could rewrite the functions
to
receive a reference to the form, and pass that when you call them."

Because each control on each form could potentially have a different orginal
border color, we can't just store a single "original border color" for them
all. Instead, we need to keep track of the original border color on a
form-by form-basis. Also, it might be a good idea to pass the desired new
color to the function as an argument, as you might want to vary it from form
to form, for better visibility if you use different background colors.

I've put together a couple of functions to do this, using a module-level
collection indexed by the form name. Here's the code:

'----- start of code for the module -----
Option Compare Database
Option Explicit

' We'll store original colors in this collection,
' indexed by the form name.
Dim mcolColors As New Collection

Function SetControlColors( _
frm As Access.Form, _
pBorderColor As Long) _
As Boolean

' Change the border color of the active control on form <frm>,
' saving the original color so that it can be restored later.
'
' Arguments:
' frm - the form to be handled
' pBorderColor - the new border color to be set

' Remove existing border-color entry for this form,
' if it exists.
On Error Resume Next ' Handle errors inline
mcolColors.Remove frm.Name
On Error GoTo 0 ' Restore normal error-handling

With frm.ActiveControl

' Save the control's current border color.
mcolColors.Add .BorderColor, frm.Name

' Set the new border color for the control.
.BorderColor = pBorderColor

End With

SetControlColors = True

End Function

Public Function ResetControlColors(frm As Access.Form) As Boolean

' Reset the border color of the active control on form <frm>
' to the original color that we previously saved.
'
' Arguments:
' frm - the form to be handled

Dim lngBorderColor As Long

' Get the original border color from the collection where
' we ought to have stored it. If it's not in the collection,
' we can't restore it, so just exit.

On Error Resume Next ' Handle errors inline
lngBorderColor = mcolColors(frm.Name)
If Err.Number <> 0 Then
Exit Function
End If
On Error GoTo 0 ' Restore normal error-handling

' Here, we have retrieved the original color, so
' set the border color of the form's active control
' to that value.

With frm.ActiveControl
' Reset the control's border color to original value
.BorderColor = lngBorderColor
End With

ResetControlColors = True

End Function
'----- end of code for the module -----

Here's an example of event property settings to use these functions:

On Enter: =SetControlColors([Form],255)
On Exit: =ResetControlColors([Form])

Note: in the above, "[Form]" is *not* supposed to be replaced by the name
of your form, or anything like that. That is the actual keyword that must
be used in the function expression.

I've tested these functions lightly, and they seem to work. I don't know if
there are any drawbacks to the constant adding and removing items from the
collection. If there are, it would be possible to use an array instead,
with a size that allows for a sufficient number of entries.

 

[Dennis]

Dirk,

I'm sorry for taking so long to respond. I've been pulled of on other things.

I'll be using your code in the next day or so. I'll post how it went.
Thank you again for your assitance. You have not idea how much I appreciate
it.

 

[Dennis]

Dirk,

I put the code you wrote in a module called BaseUtil. I declared them as
Public Functions.

When I set the:
The On Enter event =SetControlColors([Form],255)
Then On Exit event =ResetControlColors([Form])

I receive the error:
The expression On Exit you entered as the event property produced the
following error: The expression you entered has a funtion containing the
wrong number of arguments.

When I edit the function by clicking on the ... button on the On Entry and
On Exit lines, the VB editor takes me to the appropriate funtions in
BaseUtil. So I believe that I have the function names correctly.

If I change the On Enter and On Exit events to "Event Procedures" and then put

blnSetColors = SetControlColors([Form],255)

and

blnResetColors = ReSetControlColors([Form])

in the respective procedures in the form's code, I receive the same error.
If I change the VB code to

blnSetColors = BaseUtil.SetControlColors([Form],255)

and

blnResetColors = BaseUtil.ReSetControlColors([Form])

Everything works fine. But when I put the above code directly in the On
Enter and On Exit events
=[BaseUtil].[SetControlColors]([Form],255)

and

= [BaseUtil].[ReSetControlColors]([Form])

respectively, I receive the error:

The expression On Exit you entered as the event property setting produced
the following error: The object doesn't contain the Automation Object
"BaseUtil.'

What am I doing wrong? I have verified the the functions exist and are
spelled correctly. They have the right number of parms.

Below is the code as it exist in BaseUtil, an independent module where I
store public sub and public functions.



At the top of BaseUtil I put

Option Compare Database
Option Explicit

' We'll store original colors in this collection, indexed by the form name.
Dim mcolColors As New Collection


Below is the code as I entered it in BaseUtil:

Public Function SetControlColors(frm As Access.Form, pBorderColor As Long) _
As Boolean
'
' Written: Dirk Goldgar, MS Access MVP
' Date: April 16, 2009
'
' Change the border color of the active control on form <frm>,
' saving the original color so that it can be restored later.
'
' Arguments:
' frm - the form to be handled
' pBorderColor - the new border color to be set
'
' Remove existing border-color entry for this form, if it exists.
'
On Error Resume Next ' Handle errors inline
mcolColors.Remove frm.Name
On Error GoTo 0 ' Restore normal
error-handling

With frm.ActiveControl
mcolColors.Add .BorderColor, frm.Name ' Save the ctl's cur brdr
color.
.BorderColor = pBorderColor ' Set the new border
color
End With

SetControlColors = True

End Function

Public Function ResetControlColors(frm As Access.Form) As Boolean
'
' Written: Dirk Goldgar, MS Access MVP
' Date: April 16, 2009
'
' Reset the border color of the active control on form <frm>
' to the original color that we previously saved.
'
' Arguments:
' frm - the form to be handled

Dim lngBorderColor As Long

' Get the original border color from the collection where we stored it.
' If it's not in the collection, we can't restore it, we will set it to a
default color

On Error Resume Next ' Handle errors inline
lngBorderColor = mcolColors(frm.Name)
If Err.Number <> 0 Then
lngBorderColor = 0 ' Set to default border color
End If
On Error GoTo 0 ' Restore normal error-handling
'
' Here, we have retrieved the original color, so set the border color of
' the form's active control to that value.
'
With frm.ActiveControl
.BorderColor = lngBorderColor 'Reset ctl's border color to
original val
End With

ResetControlColors = True

End Function

 

[Dennis]

Dirk,

I figured out the problem. I had multiple routines with the same name.