473,544 Members | 1,944 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Error looping through controls on form in Access 2003

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
16 7194
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.goo glegroups.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
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
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.co m> 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
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.co m> 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
On Fri, 11 Nov 2005 18:55:36 GMT, Salad <oi*@vinegar.co m> wrote:
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.co m> 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
Steve Jorgensen <no****@nospam. nospam> wrote in
news:v0******** *************** *********@4ax.c om:
On Fri, 11 Nov 2005 18:55:36 GMT, Salad <oi*@vinegar.co m> wrote:
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 06:36:18 GMT, Salad <oi*@vinegar.co m> 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
>backcolo r 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
On Fri, 11 Nov 2005 21:16:10 -0600, "David W. Fenton"
<dX********@bwa y.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
Steve Jorgensen <no****@nospam. nospam> wrote in
news:jl******** *************** *********@4ax.c om:
On Fri, 11 Nov 2005 21:16:10 -0600, "David W. Fenton"
<dX********@bw ay.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
Steve Jorgensen wrote:
On Fri, 11 Nov 2005 21:16:10 -0600, "David W. Fenton"
<dX********@bwa y.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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3264
by: Michael | last post by:
Hello again everyone, Hope you can help. I have a form with a lot of code going on in the background but I can't find the route cause of the following error message. Update or Cancel Update Without Add New or Edit. I understand that when trying to update records you have to work
6
4717
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much appreciated. Thanks in advance
14
10110
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought I should give back to the community by posting our findings. Thanks you all for all your help till now by posting problems and their solutions. ...
6
8441
by: Squirrel | last post by:
I have a command button on a subform to delete a record. The only statement in the subroutine is: DoCmd.RunCommand acCmdDeleteRecord The subform's recordsource is "select * from tblVisit order by VisitDt" I'm getting this error message: Errno is 2465. Err.description is "Can't find field '|' referred to in your expression"
1
6417
by: Jozef | last post by:
Hello, I'm trying to loop through forms by doing a "For Each" in the Currentdb. I'm doing this like the following; Dim db as dao.database Dim docLoop as document Dim ctrl as control Set db = CurrentDb
1
8810
by: Mikey G | last post by:
Hi, I created a simple VB.NET 2003 application through Visual Studio that connects to a MySQL database and loads a table into a Dataset, and then displays that table information in a DataGrid on a form for editing. The table fill works fine, the delete function works fine, but when I try to update a row, the application fails with the...
5
3983
by: johnb41 | last post by:
I need to loop through a bunch of textbox controls on my form. The order of the loop is very important. For example, the top one must be read first, then the one below it, etc. My first attempt was to put the controls in a Panel, and then loop through the controls collection of the panel. What happened is it read the controls from the...
0
23449
by: HKSHK | last post by:
This list compares the error codes used in VB.NET 2003 with those used in VB6. Error Codes: ============ 3: This Error number is obsolete and no longer used. (Formerly: Return without GoSub) 5: Procedure call or argument is not valid. 6: Overflow. 7: Out of memory.
1
1401
by: Jimi200478 | last post by:
Hello All, Okay....here goes.We are currently in the process of updating our servers to windows 2003 server from Windows 2k. Before making any changes to our production environment, we ran the new servers on our test environments to make sure that we had everything configured correctly. We had no problems in the test environments, and moved...
0
7362
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7602
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7368
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5904
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5291
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4911
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3407
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1837
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
657
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.