A.D.TEJPAL
adtejpal at gmail.com
Thu Jul 5 00:11:03 CDT 2007
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