Compile errors when using form control's "Control Source" property to refer to the control's value

[Yarik Mezheritskiy]

Hello!

Please, help me to solve this puzzle:

(1) We have a heap of legacy Access 2000 code that frequently uses the
following trick: when a value of some bound control is necessary, the
code refers to that control using its "Control Source" property value
(instead of, say, using the control's "Name" property value). For
example, if there is a bound text field named "txt88" and having its
"Control Source" set to "CustomerName" (which is a name of the
corresponding field in the underlying recordset), the code looks like

strName = Me.CustomerName (a)

instead of "classic"

strName = Me.txt88 (b)


(2) We have two computers with installations of MS Access 2000 + MS
Office Developer 2000 where this code is being maintained/developed.


The puzzle is that on one of those computers the code like (a)
compiles without a hitch, but on another computer each occurence of
code like (a) causes VBA compiler to issue an error ("Method or data
member not found"). Could somebody shed some light of why could
compiler on the second computer complain about code like (a)?

(The only difference between two computers and their software
configurations that I can easily describe is that the second computer
-- where compiler complains -- was set up recently, whereas the first
computer has been set up a long time ago and nobody remembers what
could have been installed there after basic MS Office + MS Office
Developer stuff.)


Thank you,
Yaroslav.

 

[Allen Browne]

Yes, there are quirks that show up intermittently here.
You can work around the problem with:
strName = Me!CustomerName

Access tries to be too clever. If you have both a field and a text box named
CustomerName, then
Me.CustomerName
is resolved as an object of type TextBox. If there is no text box by that
name, the reference is resolved as an object of type AccessField, and the
problem is associated with that type. For example, if you enumerate the
Controls of the form, naturally enough those that are just an AccessField
are not listed, and are not counted in Me.Controls.Count. However, the
reference:
Me.Controls("CustomerName")
*does* work, even though we just showed that the AccessField is *not* part
of the Controls collection.

The bug you refer to seems to be related to this inconsistent attempt to
recognise things that are but are not part of the collection. It works
sometimes. Then for no apparent reason, you make an unrelated design change
and it suddenly stops being able to recognise the AccessField. The best
workaround is to *always* use the bang and not the dot to reference the
AccessFields. The annoying part is that misspellings are not caught at
design time (which is why I generally prefer the dot), but it seems the only
foolproof way around this issue.

There's a much worse bug related to this in Access 2002 and 2003. Most
people don't display the foreign key in a subform because it's shown on the
main form. If the field named in the subform control's LinkChildFields is an
AccessField (i.e. there is no control for it), Access will not merely reject
the reference one day, but will start crashing, i.e. "shut down by Windows;
sorry for the inconvenience." In these versions, you *must* include a text
box for your foreign key fields in the subform (Visible = No), to avoid this
serious problem with the AccessField type.

HTH.

 

[Yarik Mezheritskiy]

Thank you Allen! Your response is very helpful, indeed. (Especially
information about potential problems with using AccessField objects.)

There is at least one thing though that still is not clear to me: Why
could two installations of MS Access 2000 SP3 react differently on the
very same code in the very same project?

The code is in a subform and (as I know now) tries to reference an
AccessField object in the top-level form, like this:

Me.PartID

Why one installation of MS Access 2000 SP3 accepts this expression
(and interpretes it as intended) at run-time, while the other
installation issues compile error? Maybe I need to install some
patch(es), service pack(s), whatsoever on the poor second computer?

FWIW: This is what I believe is currently installed on the "failing"
computer:

- MS Access 2000 as a part of MS Office 2000 Professional SR-1
- MS Office 2000 Service Pack 3
- MS Office 2000 Developer SR-1

Maybe there is something else I need to install? Maybe I should have
been installing these things in some particular order or do some other
weird things (like, for example, installing MS Office 2000 Service
Pack 2 before installing MS Office 2000 Service Pack 3)?

Or is it simply a nature of quirks of MS Access compiler and
interpreter: to _rnadomly_ complain or not complain about certain
things?

Thank you,
Yaroslav.

 

[Allen Browne]

Hi Yaroslav

Have you ever had an intermittent bug, i.e. one that's triggered by some
combination of circumstance, but not another? I don't have the resources or
inclination to try to track this bug down for Microsoft, but it is triggered
only by the interaction of certain conditions, and I don't know what they
are. It could be triggered by anything, even as unobvious as adding the
128th control to a form.

Your Office SP is good. Check that you have SP8 for JET 4 as well. Locate
msjet40.dll (typically in windows\system32), right-click and check on the
Version tab. SP8 is indicated the minor version starting with 8, i.e.:
4.0.8xxx.0
This is at least as important as the Office SP. In fact, we display this on
the Help | About screen for our clients, using the API calls from:
http://www.mvps.org/access/api/api0065.htm
http://www.mvps.org/access/api/api0010.htm