By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Error looping through controls on form in Access 2003

P: n/a
TD
This is the code under a command button -

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
Next ctl

The error says that the 'Object doesn't support this property or metho,
then it highlights the line above ctlBackColor.

How can I loop through the controls on a form to check the backcolor
and then set it?

TD

Nov 13 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
Some controls (such as command buttons) don't have a BackColor property, so
you receive error 438 telling you that the property is not supported by that
control.

To avoid that, you might trap the error and resume, or use a Select Case
based on the Type of the control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"TD" <dl**@adsi-sc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
This is the code under a command button -

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
Next ctl

The error says that the 'Object doesn't support this property or metho,
then it highlights the line above ctlBackColor.

How can I loop through the controls on a form to check the backcolor
and then set it?

TD

Nov 13 '05 #2

P: n/a
TD wrote:
This is the code under a command button -

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
Next ctl

The error says that the 'Object doesn't support this property or metho,
then it highlights the line above ctlBackColor.

How can I loop through the controls on a form to check the backcolor
and then set it?

TD


Taking from what Allen said, you could test the type

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox or ctl.ControlType = acLabel
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next ctl
Nov 13 '05 #3

P: n/a
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.com> wrote:
TD wrote:
This is the code under a command button -

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
Next ctl

The error says that the 'Object doesn't support this property or metho,
then it highlights the line above ctlBackColor.

How can I loop through the controls on a form to check the backcolor
and then set it?

TD


Taking from what Allen said, you could test the type

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox or ctl.ControlType = acLabel
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next ctl


Checking the error makes the code less couples to what exactly supports a
background color. Your example, for instance, doesn't support box controls,
and new versions of Access may add controls that may or may not support a
background color. The fewer details about something else your code needs to
consider, the better.
Nov 13 '05 #4

P: n/a
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.com> wrote:

TD wrote:

This is the code under a command button -

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
Next ctl

The error says that the 'Object doesn't support this property or metho,
then it highlights the line above ctlBackColor.

How can I loop through the controls on a form to check the backcolor
and then set it?

TD


Taking from what Allen said, you could test the type

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox or ctl.ControlType = acLabel
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next ctl

Checking the error makes the code less couples to what exactly supports a
background color. Your example, for instance, doesn't support box controls,
and new versions of Access may add controls that may or may not support a
background color. The fewer details about something else your code needs to
consider, the better.


I wouldn't even bother with error codes. A simple On Error Resume Next
would suffice. But there are times that it's nice to know what the
ControlType is, so I provided an example to expand Allen's suggestions.
Nov 13 '05 #5

P: n/a
On Fri, 11 Nov 2005 18:55:36 GMT, Salad <oi*@vinegar.com> wrote:
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.com> wrote:

TD wrote:
This is the code under a command button -

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
Next ctl

The error says that the 'Object doesn't support this property or metho,
then it highlights the line above ctlBackColor.

How can I loop through the controls on a form to check the backcolor
and then set it?

TD
Taking from what Allen said, you could test the type

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox or ctl.ControlType = acLabel
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next ctl

Checking the error makes the code less couples to what exactly supports a
background color. Your example, for instance, doesn't support box controls,
and new versions of Access may add controls that may or may not support a
background color. The fewer details about something else your code needs to
consider, the better.


I wouldn't even bother with error codes. A simple On Error Resume Next
would suffice. But there are times that it's nice to know what the
ControlType is, so I provided an example to expand Allen's suggestions.


Just to be agumentative :) I actually do pay attention to the error codes
because when all errors are treated the same, other errors that have
diagnostic value are obsucured. I always check for just the error codes I
expect, and report or re-raise other errors.
Nov 13 '05 #6

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:v0********************************@4ax.com:
On Fri, 11 Nov 2005 18:55:36 GMT, Salad <oi*@vinegar.com> wrote:
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.com> wrote:

TD wrote:
>This is the code under a command button -
>
>Dim ctl As Control
>
>For Each ctl In Me.Controls
> If ctl.BackColor <> RGB(255, 255, 255) Then
> ctl.BackColor = RGB(255, 255, 255)
> End If
>Next ctl
>
>The error says that the 'Object doesn't support this property
>or metho, then it highlights the line above ctlBackColor.
>
>How can I loop through the controls on a form to check the
>backcolor and then set it?
>
>TD
>

Taking from what Allen said, you could test the type

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox or ctl.ControlType = acLabel
If ctl.BackColor <> RGB(255, 255, 255) Then
ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next ctl
Checking the error makes the code less couples to what exactly
supports a background color. Your example, for instance,
doesn't support box controls, and new versions of Access may add controls that may or may not support a background color. The
fewer details about something else your code needs to consider,
the better.


I wouldn't even bother with error codes. A simple On Error ResumeNext would suffice. But there are times that it's nice to know
what the ControlType is, so I provided an example to expand
Allen's suggestions.


Just to be agumentative :) I actually do pay attention to the
error codes because when all errors are treated the same, other
errors that have diagnostic value are obsucured. I always check
for just the error codes I expect, and report or re-raise other
errors.


I would *never* use an error handler to fix this. I'd simply make
my
code apply only to the controltypes that I wanted it to apply to.

INdeed, for this kind of thing, especially if it's running more
often than just when the form opens, I'd likely define a custom
collection.

I prefer avoiding an error that is foreseeable, rather than
producing the error and then recovering from it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #7

P: n/a
On Fri, 11 Nov 2005 21:16:10 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....

I would *never* use an error handler to fix this. I'd simply make
my
code apply only to the controltypes that I wanted it to apply to.

INdeed, for this kind of thing, especially if it's running more
often than just when the form opens, I'd likely define a custom
collection.

I prefer avoiding an error that is foreseeable, rather than
producing the error and then recovering from it.


I have to wonder why...

I see 2 reasons to choose error handling over pre-checking conditions to avoid
exceptions.

1. The logic to check the condition and raise the appropriate error number to
communicate that condition is already built-in to VBA and the code libraries
accessed via VBA - why reinvent that wheel? In fact, by reinventing the
wheel, you end up also reinventing the debugging it takes to get the condition
checks right, and you are now responsible for updating your checks to apply to
new cases of the same condition in new versions of the underlying libraries.

To put this more succinctly, why write code that has the complexity of
checking 4 or 5 control types that may not even be the right set of types in a
future version of the library when it could very simply check 1 error code
that should remain informative of the same condition across multiple versions
of the library.

2. There are cases where there is a finite risk that the condition being
checked can change between the time of the check and the time the code that
depends on the precondition runs. Specifically, this can happen when checking
for duplicate rows before inserting, checking for the non-existence of files
before creating them, etc.

Using error handling, you get 2 for 1. You catch the exception at the time of
the action that has the conflict, and identify what kind of conflict occurred.

---

Bottom line - what is so compelling about avoiding the use of exceptions for
information that is is worth writing more complex, more fragile code, more
tightly coupled to things you didn't write?

(yes - pots calling kettles black ... mea culpa)
Nov 13 '05 #8

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:jl********************************@4ax.com:
On Fri, 11 Nov 2005 21:16:10 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

...

I would *never* use an error handler to fix this. I'd simply make
my
code apply only to the controltypes that I wanted it to apply to.

INdeed, for this kind of thing, especially if it's running more
often than just when the form opens, I'd likely define a custom
collection.

I prefer avoiding an error that is foreseeable, rather than
producing the error and then recovering from it.
I have to wonder why...

I see 2 reasons to choose error handling over pre-checking
conditions to avoid exceptions.

1. The logic to check the condition and raise the appropriate
error number to communicate that condition is already built-in to
VBA and the code libraries accessed via VBA - why reinvent that
wheel? In fact, by reinventing the wheel, you end up also
reinventing the debugging it takes to get the condition checks
right, and you are now responsible for updating your checks to
apply to new cases of the same condition in new versions of the
underlying libraries.


???

You've lost me. Sounds like you're saying to depend on a VBA error
rather than my method of only running the code on the controls to
which it applies.
To put this more succinctly, why write code that has the
complexity of checking 4 or 5 control types that may not even be
the right set of types in a future version of the library when it
could very simply check 1 error code that should remain
informative of the same condition across multiple versions of the
library.
Well, I tend to believe that error handling is more expensive in
terms of CPU cycles than checking controltypes. Also, I don't trust
VBA when it's in error mode. Things don't always go as one
expects,so I'd rather not get into that mode if I can avoide it.
2. There are cases where there is a finite risk that the condition being checked can change between the time of the check and the
time the code that depends on the precondition runs.
Specifically, this can happen when checking for duplicate rows
before inserting, checking for the non-existence of files before
creating them, etc.
Well, if you change the scenario, then the balance between the
alternative methods of handling the problem changes.
Using error handling, you get 2 for 1. You catch the exception at the time of the action that has the conflict, and identify what
kind of conflict occurred.
Well, that programming style goes against my grain. I dpn't let my
code generate errors that I'm aware of. To me, it's like prentative
medicine.
Bottom line - what is so compelling about avoiding the use of
exceptions for information that is is worth writing more complex,
more fragile code, more tightly coupled to things you didn't
write?


I don't see that the code is more complex, especially given that
the
error handler requires either a GoTo, or Resume Next, the former of
which is unnecessarily complicated (in my opinion) and the lateter
of which is flat out dangerous to use, and in my opinion should
never be used if there is an alternative.

Also, code that checks for the conditions that could produce the
error is likely to be better thought out, becuase in order to write
that code you have to consider what you need to check for.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9

P: n/a
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 21:16:10 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

...

I would *never* use an error handler to fix this. I'd simply make
my
code apply only to the controltypes that I wanted it to apply to.

INdeed, for this kind of thing, especially if it's running more
often than just when the form opens, I'd likely define a custom
collection.

I prefer avoiding an error that is foreseeable, rather than
producing the error and then recovering from it.
I have to wonder why...

I see 2 reasons to choose error handling over pre-checking conditions to avoid
exceptions.

1. The logic to check the condition and raise the appropriate error number to
communicate that condition is already built-in to VBA and the code libraries
accessed via VBA - why reinvent that wheel? In fact, by reinventing the
wheel, you end up also reinventing the debugging it takes to get the condition
checks right, and you are now responsible for updating your checks to apply to
new cases of the same condition in new versions of the underlying libraries.

To put this more succinctly, why write code that has the complexity of
checking 4 or 5 control types that may not even be the right set of types in a
future version of the library when it could very simply check 1 error code
that should remain informative of the same condition across multiple versions
of the library.


You shouldn't have to worry about new types of controls until you
actually start using them. The safety net (exception) is there to
catch :-) what falls through the cracks after you've done everything
you can to prevent errors from happening in the first place. You made
a good point about considering that libraries can change in the future
but that consideration is not as strong as it would be without MS'
efforts to maintain some backward compatibility. I don't mind a little
bit of extra code that prevents an error from happening 99.999 percent
of the time. If you prevent the usual one or two obvious situations
the remaining cases are often rare and not worthy of reinvention.
Besides, checking for the existence of a file or of an indexed field
doesn't need much debugging.

2. There are cases where there is a finite risk that the condition being
checked can change between the time of the check and the time the code that
depends on the precondition runs. Specifically, this can happen when checking
for duplicate rows before inserting, checking for the non-existence of files
before creating them, etc.
Now those are the exceptional cases that the word exception implies. I
can live with the VBA error handler when a file whose existence I
checked a moment ago got deleted a score of milliseconds later. Not
even checking to see if the file is there and relying on the error
handler is a different style entirely. Trying to use the error handler
where you don't need it is like using the debugger to search and
replace a new variable name. So I guess I'm suggesting a combination
of both error prevention and error handling for extremely rare cases,
yet the situation I'm in usually allows me to forego VBA error handling
entirely (my bad).

Using error handling, you get 2 for 1. You catch the exception at the time of
the action that has the conflict, and identify what kind of conflict occurred.

---

Bottom line - what is so compelling about avoiding the use of exceptions for
information that is is worth writing more complex, more fragile code, more
tightly coupled to things you didn't write?

(yes - pots calling kettles black ... mea culpa)


Maybe I bought into that "ounce of prevention" line too much :-). My
mathematician background always causes me to seek airtight solutions
but I can't get airtight solutions when so many unexpected things are
possible; so my engineering background causes me to try to get as close
to the ideal as possible even if it means checking some things that
others don't bother with. Of course you have to consider how much time
the extra check takes. I've never had a situation where I could not
find a way to make the extra check fairly quickly. A good argument
might convince me to try error handling for duplicates. If someone
wants to skip the extra check because it saves some time and gives the
user a quicker response then the error handling for the non-rare case
is there for a reason. If you're concerned that MS is not going to
continue backward compatibility and you want more robust code that will
not have to be rewritten for new versions then that is a reason. Note
that checks that use common API functions seem to be pretty reliable
for future versions. I try not to use checking techniques that are
version fragile. So have a reason for what you do either way.

James A. Fortune

Nov 13 '05 #10

P: n/a
On Fri, 11 Nov 2005 19:44:51 -0800, Steve Jorgensen <no****@nospam.nospam>
wrote:

....
1. The logic to check the condition and raise the appropriate error number to
communicate that condition is already built-in to VBA and the code libraries
accessed via VBA - why reinvent that wheel? In fact, by reinventing the
wheel, you end up also reinventing the debugging it takes to get the condition
checks right, and you are now responsible for updating your checks to apply to
new cases of the same condition in new versions of the underlying libraries.

To put this more succinctly, why write code that has the complexity of
checking 4 or 5 control types that may not even be the right set of types in a
future version of the library when it could very simply check 1 error code
that should remain informative of the same condition across multiple versions
of the library.


Perhaps an example is in order...

Let's say, we want a collection of all the control values on a form keyed by
control name, and we try the approach of checking the type of each control to
see if it's one we know should have a Value property. Let's assume we're
fairly naive or fairly tired, so we get the obvious cases, but initially miss
some slightly non-obvious cases that we'll have to find during testing or when
they break later.

Public Function ControlValues(Form As Access.Form) As VBA.Collection
Dim colResult As New VBA.Collection

Dim ctlItem As Access.Control
Dim varValue As Variant
For Each ctlItem In Form.Controls
If TryGetControlValue(ctlItem, varValue) Then
colResult.Add Key:=ctlItem.Name, _
Item:=varValue
End If
Next

Set ControlValues = colResult
End Function

Private Function TryGetControlValue( _
Control As Access.Control, _
ByRef Value As Variant _
) As Boolean
Dim blnResult As Boolean

If Control.ControlType = acTextBox Then
blnResult = True
ElseIf Control.ControlType = acComboBox Then
blnResult = True
ElseIf Control.ControlType = acCheckBox Then
blnResult = True
End If

If blnResult Then
Value = Control.Value
End If

TryGetControlValue = blnResult
End Function

.... Ok, so let's say we do some testing and figure out some Access control
types we missed that have Value properties, and we fix that...

If Control.ControlType = acTextBox Then
blnResult = True
ElseIf Control.ControlType = acComboBox Then
blnResult = True
ElseIf Control.ControlType = acCheckBox Then
blnResult = True
ElseIf Control.ControlType = acOptionGroup Then
blnResult = True
ElseIf Control.ControlType = acListBox Then
blnResult = True
ElseIf Control.ControlType = acToggleButton Then
blnResult = True
End If

.... A while later we try to use this on a form and get an error because it
turns out that a toggle button was used in an option group instead of free
standing on the form. Hmm - guess we'd better check what happens with an
option button that's -not- in an option group too. Yup - an option button can
have a value property if it's not in an option group...

Select Case Control.ControlType
Case acTextBox, acComboBox, acCheckBox, acOptionGroup, acListBox
Case acToggleButton, acOptionButton
blnResult = Not (TypeOf Control.Parent Is Access.OptionGroup)
End Select

.... There, is our code bullet proof yet? What about ActiveX controls (e.g.
the FMS controls for Access)? As I recall, some of them have Value
properties, and some don't. How should we find out? I guess we can cycle
through the properties list of each ActiveX control. And is that all? I
don't feel certain there's nothing else I've missed. Can I ever know if I've
thought of everything?

On the other hand, if I just ask for a control's value, I'll either get a
value if it has one, or I'll get a trappable error if there isn't...

Private Function TryGetControlValue( _
Control As Access.Control, _
ByRef Value As Variant _
) As Boolean
On Error GoTo Err_Catch

Dim blnResult As Boolean

Value = Control.Value
blnResult = True

Proc_Final:
TryGetControlValue = blnResult
Exit Function

Err_Catch:
If Err.Number = VB_ERR_MEMBER_NOT_SUPPTD Then
blnResult = False
Resume Proc_Final
Else
Err.Raise Err.Number, Err.Source, Err.Description, _
Err.HelpFile, Err.HelpContext
End If

End Function

.... Yes, the error handling code is annoying, but we've restricted it to one
small procedure, and this might not have been the only reason to need an error
handler as the code evolves, so much of that code might have been unavoidable
in any case.
Nov 14 '05 #11

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:6a********************************@4ax.com:
... Yes, the error handling code is annoying, but we've restricted
it to one small procedure, and this might not have been the only
reason to need an error handler as the code evolves, so much of
that code might have been unavoidable in any case.


I could construct any example I wanted if I design my code around
demonstrating whatever it is my argument happens to be.

The code you've spent all that time writing is pretty much useless,
as there are much easier ways to accomplish the same thing.

Your whole premise is that you're walking the entire controls
collection of the form.

Remove that premise and your whole argument falls apart.

Use the .Tag property to group your controls and assign them to a
custom collection and your argument falls apart.

If you limit the discussion to only one possible method for
accomplishing the task (i.e., always walking the entire controls
collection), yes, your error-based method is more efficient.

But you can avoid that entirely by simply avoiding the problem in
the first place by not walking the control collection repeatedly.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 15 '05 #12

P: n/a
On Mon, 14 Nov 2005 17:58:07 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:6a********************************@4ax.com :
... Yes, the error handling code is annoying, but we've restricted
it to one small procedure, and this might not have been the only
reason to need an error handler as the code evolves, so much of
that code might have been unavoidable in any case.
I could construct any example I wanted if I design my code around
demonstrating whatever it is my argument happens to be.

The code you've spent all that time writing is pretty much useless,
as there are much easier ways to accomplish the same thing.


For various values of "the same thing", that's probably true.
Your whole premise is that you're walking the entire controls
collection of the form.

Remove that premise and your whole argument falls apart.
And the original post was about trying to set the background color properties
of all the controls on a form. I also have had many occasions to want to do
something to all applicable controls on a form, so it's not a fringe case.
Use the .Tag property to group your controls and assign them to a
custom collection and your argument falls apart.
Yes, but that assumes the task at hand is best done by manually flagging the
controls in question rather than intelligently determining what do do without
the need for producing and maintaining a pattern of flagging. For some tasks,
that's tru, and for others it isn't.
If you limit the discussion to only one possible method for
accomplishing the task (i.e., always walking the entire controls
collection), yes, your error-based method is more efficient.
Well, even with the Tag property, you have to walk the entire collection.
But you can avoid that entirely by simply avoiding the problem in
the first place by not walking the control collection repeatedly.


Here's a case where I've needed to do something in line with the example I've
presented.

I had a bound form, and I needed to present freindly error messages to the
user in cases of certain errors during saving of new and edited records. The
only way of doing this other than -not- editing or adding through the bound
form was to use a procedure that scans the values of the bound controls on the
form, cancels the save operation, then performs the add or update directly on
the form's recordset clone.

Significantly changing the design of each form was very undesirable because
there were about 40 forms (including subforms) affected, so I needed a generig
procedure that would examine the ControlSource and and Value properties of all
controls to figure out which ones supplied values to what fields in the
recordset.

This code took about a day and a half to write and implement (mostly because
of quirks with RecordsetClone in Access 2000 that went away in later
versions), and required adding only a few lines of code to each form in
question.
Nov 15 '05 #13

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:g2********************************@4ax.com:
On Mon, 14 Nov 2005 17:58:07 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:6a********************************@4ax.co m:
... Yes, the error handling code is annoying, but we've
restricted it to one small procedure, and this might not have
been the only reason to need an error handler as the code
evolves, so much of that code might have been unavoidable in any case.
I could construct any example I wanted if I design my code around
demonstrating whatever it is my argument happens to be.

The code you've spent all that time writing is pretty much
useless, as there are much easier ways to accomplish the same
thing.


For various values of "the same thing", that's probably true.


The task at hand is setting the background colors of selected
controls. See below for my reasoning why the way you're doing it is
wrong.
Your whole premise is that you're walking the entire controls
collection of the form.

Remove that premise and your whole argument falls apart.


And the original post was about trying to set the background

color properties of all the controls on a form. I also have had many
occasions to want to do something to all applicable controls on a
form, so it's not a fringe case.
But you don't have to walk the whole controls collection but once.

Look, I do this ALL THE TIME -- it's no accident that my stock
custom collections examples are based around that. The methods I
use
don't involve checking the ControlType at all, even while
populating
the custom collections.
Use the .Tag property to group your controls and assign them to a
custom collection and your argument falls apart.


Yes, but that assumes the task at hand is best done by manually
flagging the controls in question rather than intelligently
determining what do do without the need for producing and
maintaining a pattern of flagging. For some tasks, that's tru,
and for others it isn't.


I can't think of a case where it would not be a viable alternative.
If you limit the discussion to only one possible method for
accomplishing the task (i.e., always walking the entire controls
collection), yes, your error-based method is more efficient.


Well, even with the Tag property, you have to walk the entire
collection.


But you don't have to check the ControlType, so there's nothing
that
could go wrong.

If you need to do different things to different types of controls,
you can set up different collections for each purpose, and any one
control can appear in more than one collection.

Of course, I"m assuming certain things:

1. if you're setting background colors, you're doing it more than
once in each form session. Otherwise, you'd just set the colors in
design mode (well, I guess you might be doing this as some kind of
user configurable setting, but that's a complete waste of time as
far as I'm concerned).

2. if you're doing it more than once, then you shouldn't be walking
the controls collection every single time you set the background
color.

3. so, you will be using a custom collection in the first place,
and
walk the collection only once, to get controls into the appropriate
collections.

You may use ControlType or Tags (or some combination) to assign the
controls to the relevant collections, but you won't need to rely on
error handling, since there's nothing in that process that can
raise
an error.

Thus, by setting up your custom control, you've avoided the whole
problem and also vastly speeded up the real, human-perceivable
performance of your form.

My point is that by choosing your methods right, you can design
away
the whole problem we are arguing over, which is caused by walking
the entire controls collection and trying to do too many things in
one pass.
But you can avoid that entirely by simply avoiding the problem in
the first place by not walking the control collection repeatedly.


Here's a case where I've needed to do something in line with the
example I've presented.

I had a bound form, and I needed to present freindly error
messages to the user in cases of certain errors during saving of
new and edited records. The only way of doing this other than
-not- editing or adding through the bound form was to use a
procedure that scans the values of the bound controls on the

form, cancels the save operation, then performs the add or update
directly on the form's recordset clone.

Significantly changing the design of each form was very
undesirable because there were about 40 forms (including subforms) affected, so I needed a generig procedure that would examine the
ControlSource and and Value properties of all controls to figure
out which ones supplied values to what fields in the recordset.

This code took about a day and a half to write and implement
(mostly because of quirks with RecordsetClone in Access 2000 that
went away in later versions), and required adding only a few lines of code to each form in question.


I'd create a custom collection, populate it in one pass through the
controls collection (checking the ControlType) and then run all my
tests around the custom collection.

In other words, using the custom collection eliminates the need for
error recovery from attempting to check non-existent properties of
controls.

I have a basic principle: never walk the controls collection of a
form more than once in any form session. The performance
improvement
from using a custom collection is surprisingly noticeable, even
with
forms that don't have hundreds of controls.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 15 '05 #14

P: n/a
On Tue, 15 Nov 2005 14:44:09 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:g2********************************@4ax.com :
....
Your whole premise is that you're walking the entire controls
collection of the form.

Remove that premise and your whole argument falls apart.


And the original post was about trying to set the background

color
properties of all the controls on a form. I also have had many
occasions to want to do something to all applicable controls on a
form, so it's not a fringe case.


But you don't have to walk the whole controls collection but once.


Right, so the code I posted might better have returned a collecion of controls
rather than a collection of values. That has nothing to do with what I was
trying to demonstrate - that checking for errors can be simpler and more
reliable that pre-checking to avoid all possible error conditions.
Look, I do this ALL THE TIME -- it's no accident that my stock
custom collections examples are based around that. The methods I
use
don't involve checking the ControlType at all, even while
populating
the custom collections.
So they use the Tag property, as you describe below?
Use the .Tag property to group your controls and assign them to a
custom collection and your argument falls apart.


Yes, but that assumes the task at hand is best done by manually
flagging the controls in question rather than intelligently
determining what do do without the need for producing and
maintaining a pattern of flagging. For some tasks, that's tru,
and for others it isn't.

....But you don't have to check the ControlType, so there's nothing
that
could go wrong.
Except forgetting to maintain the Tagging convention later when you add
controls, and there's the extra effort of manually setting the tag properties
for controls in each new form the code applies to.
If you need to do different things to different types of controls,
you can set up different collections for each purpose, and any one
control can appear in more than one collection.

Of course, I"m assuming certain things:

1. if you're setting background colors, you're doing it more than
once in each form session. Otherwise, you'd just set the colors in
design mode (well, I guess you might be doing this as some kind of
user configurable setting, but that's a complete waste of time as
far as I'm concerned).

2. if you're doing it more than once, then you shouldn't be walking
the controls collection every single time you set the background
color.
I've never seen that be a performance problem, but I don't do that either. If
I'm going to use the control set more than once, I return a collection.
3. so, you will be using a custom collection in the first place,
and
walk the collection only once, to get controls into the appropriate
collections.

You may use ControlType or Tags (or some combination) to assign the
controls to the relevant collections, but you won't need to rely on
error handling, since there's nothing in that process that can
raise
an error.
Again, I say that the way you describe is right for some cases, and not for
others. In many cases the "type" information you care about for a control is
whether it has a certain type of behavior that might apply to different
control types, and perhaps be different for different instances of the same
control type (e.g. the toggle button).
Thus, by setting up your custom control, you've avoided the whole
problem and also vastly speeded up the real, human-perceivable
performance of your form.

My point is that by choosing your methods right, you can design
away
the whole problem we are arguing over, which is caused by walking
the entire controls collection and trying to do too many things in
one pass.
I didn't think that was the issue at hand, and I don't disagree with what you
just said. Returning and reusing a collection of controls is a good thing.
But you can avoid that entirely by simply avoiding the problem in
the first place by not walking the control collection repeatedly.


Here's a case where I've needed to do something in line with the
example I've presented.

I had a bound form, and I needed to present freindly error
messages to the user in cases of certain errors during saving of
new and edited records. The only way of doing this other than
-not- editing or adding through the bound form was to use a
procedure that scans the values of the bound controls on the

form,
cancels the save operation, then performs the add or update
directly on the form's recordset clone.

Significantly changing the design of each form was very
undesirable because there were about 40 forms (including

subforms)
affected, so I needed a generig procedure that would examine the
ControlSource and and Value properties of all controls to figure
out which ones supplied values to what fields in the recordset.

This code took about a day and a half to write and implement
(mostly because of quirks with RecordsetClone in Access 2000 that
went away in later versions), and required adding only a few

lines
of code to each form in question.


I'd create a custom collection, populate it in one pass through the
controls collection (checking the ControlType) and then run all my
tests around the custom collection.


Again, not aguing with the controls collection - my code would have been much
more complex and trouble-prone if I had checked control types instead of just
checking whether I could read ControlSource values and Value values from the
controls - why should I have to care what "kind" of controls they are?
In other words, using the custom collection eliminates the need for
error recovery from attempting to check non-existent properties of
controls.
No - checking for every possible expected control type or checking for a
custom tag eliminates the need for error recovery. Using the custom
collection eliminates the need to do whatever kind of check will be done more
than once.
I have a basic principle: never walk the controls collection of a
form more than once in any form session. The performance
improvement
from using a custom collection is surprisingly noticeable, even
with
forms that don't have hundreds of controls.


I don't disagree at all with -that- point. In fact, isolating the process of
gathering the controls from the process of manipulating them is good
separation of concerns and makes code easier to debug and maintain.
Nov 16 '05 #15

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:4g********************************@4ax.com:
[me:]
In other words, using the custom collection eliminates the need
for error recovery from attempting to check non-existent
properties of controls.


No - checking for every possible expected control type or checking
for a custom tag eliminates the need for error recovery. Using
the custom collection eliminates the need to do whatever kind of
check will be done more than once.


Well, we'll have to agree to disagree.

Checking controltype doesn't seem to me to be any kind of big deal
at all. Your toggle button example makes no sense in the context of
a discussion of setting background color, so I'm not sure why you
even mention it. In any event, an error handler can't determine
which are the toggle buttons you want to operate on, so I faile to
understand why the issue is even relevant.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 17 '05 #16

P: n/a
On Wed, 16 Nov 2005 21:33:47 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:4g********************************@4ax.com :
[me:]
In other words, using the custom collection eliminates the need
for error recovery from attempting to check non-existent
properties of controls.


No - checking for every possible expected control type or checking
for a custom tag eliminates the need for error recovery. Using
the custom collection eliminates the need to do whatever kind of
check will be done more than once.


Well, we'll have to agree to disagree.

Checking controltype doesn't seem to me to be any kind of big deal
at all. Your toggle button example makes no sense in the context of
a discussion of setting background color, so I'm not sure why you
even mention it. In any event, an error handler can't determine
which are the toggle buttons you want to operate on, so I faile to
understand why the issue is even relevant.


At the risk of sounding like I'm just trying to have the last word, the toggle
button example refers to the Value property, not the color. A toggle button
that's by itself on a form has a value, and a toggle button that's in an
option group does not.

As for agreeing to disagree - yes, I'd say we've exhausted our respective
arguments.

Until next debate...

Nov 17 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.