ctdak said:
You were right. That was the problem! The text box was named the same as
the field name ZipCode. Strange that this should matter though. When you
add a control for a field, Access automatically makes the control name and
the control source equal the field name, so why does the control name have to
be different when you change the control source to a formula using the field
name?
If the control is just bound to a field, then you do not
use the = sign and Access "knows" that it should get the
field from that table/query and there is no ambiguity.
In expressions, the confusion arises because it can refer to
a field in the form's record source table/query or a control
on the form by just using its name anywhere. There is no
syntax to indicate which one you want to use. So, if you
have a control named ZipCode and it has an expression like
yours, then, Access thinks the expression's ZipCode is
referring to the control named ZipCode, which is an infinite
loop, thus the #Error.
As far as Access defaulting the control name to the field
name when you drag a field from the field list to the
form/report, you've just joined a very long list of people
with the same complaint. A lot of folks avoid this isuue by
developing the habit of prefixing their control names with
an abbreviation of the type of the control (e.g. txt, cbo,
etc) whenever they refer to a control in a control
expression or in VBA code.
