Jim Lawrence
accessd at shaw.ca
Thu Jul 5 01:25:39 CDT 2007
Hi A D Tejpal:
No problem. That example is definitely one of the worse I have seen for a
while. Added to those coding issues is a poor design.
Consider that the lady that made the database designed it never having
worked in Access or any database before, she is a hair-dresser who was doing
a friend a favour, I think she did rather well.
Regards
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Wednesday, July 04, 2007 10:11 PM
To: Access Developers discussion and problem solving
Cc: A. D. TEJPAL
Subject: Re: [AccessD] Naming of controls - [Was: Open a combobox on
entering it]
Jim,
Thanks for making the file available for study. My findings are
placed in the note appended below. Certain factors as brought out are
suspected to have contributed to the inconsistent behavior mentioned in your
post. I understand this is a db that was handed over to you. Of course, if
it had been your own creation, the aberrations would not have crept in.
A gist of the findings:
(a) A particular bound control is named as per a field other than its
actual control source. Moreover, the name of its source field is a reserved
word (Name). Combination of these factors makes it a case of multiplied
pitfall.
(b) Embedded quote in field name. Better avoid.
(c) Lookup fields used in tables. Not considered desirable.
(d) Subdatasheet property of tables is Auto. It should be set to None.
(e) In many cases, table column headings are at variance with field
names. Better keep the tables simple & straightforward. Instead, the
objective can be met through suitable captions on form labels.
As mentioned by you, the problem experienced at your end has been
surfacing intermittently. In limited tests at my end, no abnormality could
be detected.
However, it can be expected that if the points brought out above
(details in the note below) are attended to and even with names of pure
bound controls kept same as their source fields, the db should perform
consistently. Simultaneously, it has to be ensured that the names of unbound
and calculated controls never coincide with any field name in the record
source.
Best wishes,
A.D.Tejpal
---------------
Findings
=======================================
1 - Bound text box named LstnmVol on form frmHoursWorked:
(a) Control source for this text box is a field named Name (a reserved
word).
(b) LstnmVol is itself a field in tblVolunteers, one of the source
tables for the query serving as record source for this form.
(c) Combination of (a) & (b) above, is bound to invite trouble as it
becomes a case of contaminated bound control, named after an existing field
in one of the source tables, even though that particular field is not its
control source. Moreover, the control source actually used (Name) happens
to be a reserved word.
(d) It appears that to start with, this control was bound to field
LstnmVol and was correctly named accordingly. Later when calculated field
Name was used as the control source, re-naming of the control accordingly
(to match the name of new control source) seems to have been overlooked.
(e) Suggested step - Modify the name of calculated field in record
source from Name to VName. Control source as well as name of this text box
would then become VName.
2 - Control named DtEnt'dHW on form frmHoursWorked:
(a) There is an embedded quote in the name of control as well as source
field.
(b) Suggested step - Eliminate embedded quote in field as well as
control name.
3 - Data Tables:
(a) In many cases, column headings are different from field names. It is
considered desirable to keep the tables as simple & straightforward as
possible. The process of depicting the fields under more convenient names
should better be handled at form level, through suitable captions for
labels.
(b) Subdatasheet property of all tables should be set to None. (At
present it is Auto).
(c) Two fields in table tblHourWorked are found to be lookup type. This
is considered a violation of ten commandments.
Note:
(a) SQL serving as record source for form frmHoursWorked involves inner
join on EventNum between tables tblHourWorked and tblEvents. At present,
this field is blank in tblHourWorked, leading to no record getting
displayed. For testing, tblEvents was removed from the query, temporarily.
Retaining this table & displaying the records by converting to Left join
would have rendered the recordset non-updateable.
(b) Compile error in form lstEditHrsWkd on account of missing End If in
two subroutines. This was rectified.
=======================================
----- Original Message -----
From: Jim Lawrence
To: 'Access Developers discussion and problem solving'
Sent: Tuesday, July 03, 2007 17:38
Subject: Re: [AccessD] Open a combobox on entering it
Hi A.D. Tejpal:
If you would like I could give you a copy of the application before I
played around with it.
The application was giving the strangest errors. In one example: a combo
box would not take the assignment of an initial value but there were no
error messages generated... and the issue was not consistent... sometimes it
would work fine... but when the combo box name was changed this problem was
gone with no other modifications.
I have read some warning about this effect, a number of years ago, the
article discussed 'Context and Naming-Conventions' but I can not find any
reference to the white-paper.
Maybe someone else can remember?
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Monday, July 02, 2007 9:46 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Open a combobox on entering it
Jim,
Would you be in a position to isolate a typical form or report that
has
caused trouble on account of control names as mentioned in your post, and
make it available as a self contained small db, for further investigation ?
Best wishes,
A.D.Tejpal
---------------
----- Original Message -----
From: Jim Lawrence
To: 'Access Developers discussion and problem solving'
Sent: Tuesday, July 03, 2007 00:28
Subject: Re: [AccessD] Open a combobox on entering it
Hi Charlotte:
I have just been running into problems with the controls named the name
of the data field... A client has this package and all sorts of weird things
were happening. At 3:00AM, I started naming control appropriately and things
started magically working :-)
The strange thing is that the app never complained it just didn't work
right and the results were never consistent...
So, don't do it Arthur... if only for your own health and sanity. :-)
Jim
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com